By: Chad Boyd | Comments | Related: > SQL Server Configurations
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.
About the author
Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
