Sql 2005 Dynamic Memory Managment with AWE enabled

By: Chad Boyd | Read Comments | Print Chad is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server. Related Tips: More

Earlier this week I had a customer send me the following question:

   I have a server running the 32-bit edition of SQL 2005 SP1 with 16 GB of memory and
   8 CPUs. AWE is enabled and the max server memory setting is 12 GB. When the service
   is started, memory usage starts low and gradually increases as the workload builds
   until it reaches 12 GB where it stops (as I expect it to). However, once the workload
   slows down and usage drops, I notice CPU and Batch Requests drop to practically
   nothing, while memory usage remains constantly at 12 GB; there is no paging or memory
   thrashing, and there is nothing else running on the server. This is leading me to
   believe that Sql 2005 really doesn't do a good job of dynamic memory management as it
   claims it should with AWE enabled now. Why isn't Sql 2005 releasing un-used memory
   during low activity periods?

First off, Sql 2005 does handle memory allocation much differently than Sql 2000 did, especially with AWE enabled. In Sql 2000, enabling AWE on your system basically caused the engine to grab a fixed memory space on startup and not release it back at any time (the actual determination on how much and what amount of memory it would grab depends on multiple considerations, which I won't go into here). Sql 2005 however introduces dynamic memory mangement even with AWE enabled (meaning you can use AWE and still have Sql grow/shrink it's memory footprint as it determines appropriate) - similarly, Sql 2005 also allows for dynamic memory management with locked pages, which is different from AWE. I won't go into these specifics here, but maybe I'll post a follow-up covering these topics.

Back to the question at hand - what is going on in the above scenario. Well, just because we allow dynamic memory management with AWE now, this still doesn't change a basic principal of Sql's management of memory - this is actually the same no matter if you are using AWE or not - Sql won’t release memory just because usage drops; it will only release when informed to do so by a user change to the min/max configuration options, or if ‘asked’ to do so by the OS (i.e. the OS is showing that other applications need some memory space, or the OS is coming under memory pressure) - this is by design and as works as expected (also works as it always has).

On startup, Sql will grow it’s memory space as appropriate to the configured max value if it can get it from the OS - if while the server is growing it’s memory space up to the max configuration value the OS is under pressure from other consumers, Sql won’t get to it’s max configured value for the same reasons that it will release it as mentioned above (this assumes there is enough pressure to preclude Sql from getting to it’s max configured value).

The basic thought here is that, given that the job of Sql Server is to act as a data server, and in any enterpise-level deployment/edition if there isn’t memory pressure on the box, why would you want (or care for that matter) Sql to release reservations back?  That would slow the build-up of memory space again during workload ramp-up times, and as a result, slow user response times.

Chad Boyd ~~~ This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.

Share:

Share

Tweet

Free SQL Server Learning The SQL Server Security THREAT - It’s Closer Than You Think Tutorial - SQL Server Performance Tuning and Monitoring

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code