SQL Server 2005 Server Configuration Performance Audit Checklist : Part 1

Awe Enabled

This section only applies to the 32-bit version of SQL Server 2005.

If you are using SQL Server 2005 and if your server has 4GB or less of RAM, the “awe enabled” option should always be left to the default value of 0, which means that AWE memory is not being used.

If your server has SQL Server 2005 Standard or Enterprise Edition, the amount of RAM it can access depends on the OS Edition your server is running, along with how much physical RAM is available in the server.

For example, below is a list of how much maximum RAM is supported by the various editions of Windows Server 2003.

  • Windows Server 2003, Standard Edition supports physical memory up to 4 gigabytes (GB).
  • Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
  • Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.

So if you are running SQL Server 2005 Standard or Enterprise Edition under Windows 2003 Server Standard Edition, the maximum amount of RAM available to SQL Server is 4 GB. But if you are running SQL Server 2005 Standard or Enterprise Edition, then it can access up to 32 GB if your are running Windows 2003 Enterprise Edition, or access up to 64 GB of RAM if you are running Windows Server 2003 Datacenter Edition.

In order for SQL Server 2005 Standard and Enterprise Editions to be able to access more than 4 GB of RAM (besides having the correct editions of the OS), you must make two setting changes: one in the OS and the other in SQL Server. More on this soon.

The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2003 Enterprise Server or Windows 2003 Datacenter Server, to access more than 4GB of RAM. Both SQL Server 2005 Standard and Enterprise Editions are AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2003 Enterprise Server, SQL Server 2005 Standard and Enterprise Editions can use up to 32 GB of RAM. If the operating system is Windows 2003 Datacenter Server, SQL Server 2005 Standard and Enterprise can use up to 64GB of RAM.

Exactly how you configure AWE memory support depends on how much physical RAM your server has. Essentially, to configure Windows 2003 to use AWE memory, you must enter one of the following switches in the boot line of the boot.ini file, and reboot the server:

4GB RAM:  /3GB
16GB RAM:  /3GB /PAE
16GB + RAM:  /PAE

The /3GB switch is used to tell the OS to allow SQL Server 2005 (Standard or Enterprise Editions) to take advantage of 3GB out of the base 4GB of RAM that Windows 2003 supports natively. If you don’t specify this option, then SQL Server will only take advantage of 2GB of the first 4GB of RAM in the server, essentially wasting 1GB of RAM.

AWE memory technology is used only for the RAM that exceeds the base 4GB of RAM, that’s why the /3GB switch is needed to use as much of the RAM in your server as possible. If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to fully take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB of RAM itself to mange the AWE memory if your server has 16GB or more of RAM. If less than 16GB of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.

Once this step is done, the next step is to set the “awe enabled” option to 1, and then restart the SQL Server service. Only at this point will SQL Server be able to use the additional RAM in the server.

As part of your audit process, you will want to check what these settings are and then determine if the setting matches your server’s hardware and software configuration. If not, then change the settings appropriately.

Pages: 1 2 3


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |