Haven't we heard enough about AWE and 3GB? Yes, I bet you have, and I won't bore you with regurgitating any second-hand information or rehashing any Microsoft documentation or whitepapers. Instead, I'll give you my test results to highlight how much buffer pool memory a 32-bit SQL Server 2005 instance can consume for each combination of the following three configuration parameters:
- AWE enabled SQL Server option
- 3GB in boot.ini
- max/min server memory SQL Server options
More specifically, I'd like to answer questions such as the following with empirical test results:
- If AWE is enabled, 3GB switch is turned on, and max/min server memory is set to 12GB, how much buffer pool memory can SQL Server 2005 consume?
- If AWE is not enabled, 3GB switch is turned on, and max/min server memory is set to 6GB, how much buffer pool memory can SQL Server 2005 consume?
- If AWE is enabled, 3GB is not turned on, and max/min server memory is set to 4GB, how much buffer pool memory can SQL Server 2005 consume?
- If AWE is not enabled, 3GB is not turned on, and max/min server memory is set to 6GB, how much buffer pool memory can SQL Server 2005 consume?
If you are familiar with SQL Server memory management and the implications of these configuration parameters, you do not need to resort to any empirical data. Still, empirical data can help you validate your logical analysis or prediction.
The following chart shows how much buffer pool memory a 32-bit SQL Server 2005 instance can actually consume in various configurations.
The X-axis has the configured values of max/min server memory. In these tests, the values of max server memory and min server memory were always set to the same. The Y-axis shows the measures of the perfmon counters SQLServer:Buffer Manager\Target Pages and SQLServer:Buffer Manager\Total Pages.
The test server had 16GB of physical memory, running 32-bit SQL Serve 2005 SP1 Enterprise Edition on 32-bit Windows Server 2003 SP1 Enterprise Edition. An OLTP benchmark tool was used to generate a memory-hungry workload. In each test that generated a data point on the chart, the values of the Target Pages and Total Pages perfmon counters were taken after the test run reached its steady state where these two values had converged and leveled off, representing the maximum amount of buffer pool memory for that test configuration.
None of the data points on the chart is a surprise. But it is still interesting in that it paints a comprehensive picture. So how would you configure it if you want the buffer pool to consume 3GB?
Note that on a different system, some of the data points, especially the peak values when AWE is not enabled, may be slightly different, depending on what else is going on the computer and what else is being loaded into the SQL Server instance.