THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

AWE and 3GB: An Empirical Picture

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:

  1. AWE enabled SQL Server option
  2. 3GB in boot.ini
  3. 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.

Published Thursday, January 11, 2007 11:18 PM by Linchi Shea
Filed under: ,

Attachment(s): AWE.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Dave Markle said:

This might be a bit OT, but IMO it's worth noting that a lot of us SQL Server people use SQL Server on the desktop.   Now that memory is incredibly cheap, more and more of us will be running into these issues on our workstations as well.  

A word of warning from the Bitter Voice of Experience about this:  Make sure your system and drivers fully support PAE (Physical Address Extensions) before adding more memory to a 32-bit *workstation*.  (nVidia's GeForce drivers don't work on a system with PAE enabled on Windows 2003)

The solution for all of this?  Get ready for 64-bit.

January 12, 2007 8:45 AM
 

Eisen said:

I wish BOL was this straightfoward  :)

February 9, 2007 2:13 PM
 

Henry Wu said:

Hi, just to clarify, the Y-Axis is

SQLServer:Memory Manager\Target Server Memory (KB) &

SQLServer:Memory Manager\Total Server Memory   (KB)

or as per blog,...

SQLServer:Buffer Manager\Target Pages &

SQLServer:Buffer Manager\Total Pages

Sorry, the Y-Axis' title has almost the exact name of the Memory Manger's counters,... converted to MB.

If the latter, conversion used was 8kb = 1 Page ?

And lastly, it didn't mention that the /PAE is in the boot.ini during the test, does this mean that the boot.ini just contains the /3GB?

I'm currently in need to know the advantages and disadvantages of a /PAE /3GB with AWE Enabled and /PAE /3GB with AWE Disabled in a 8GB RAM server with SQL Server and Analysis Services installed.

Best Regards,

Henry

November 24, 2007 1:57 PM
 

wizualizacje architektoniczne said:

Thats good info. Thanks

Regards

February 4, 2008 9:38 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement