My SQL Server isn’t releasing memory, but it’s barely doing anything. What gives?

Filed under: Administration, Internals and Architecture, SQL Server 2005, Tuning and Optimization — Ari Weil at 9:48 am on Monday, January 14, 2008

SQL Server uses the Lazy Writer process to release memory. The Lazy Writer is a process that checks the status of bufferpool buffers (committed or not committed) in a cycle and evaluates whether to increase or decrease the number of committed buffers. It decides whether to change the committed buffers according to the memory required by SQL Server and memory available to the OS. By default, the process wakes up every 1 second but can also be called by other processes to run more frequently.

Now, on to your question… When the Lazy Writer runs, it will only shrink the bufferpool when the OS does not have sufficient memory to service memory requests from other applications. If the OS does not need additional memory, SQL Server does not release any; more committed buffers means faster access and better performance for SQL Server so if it is not required to return memory, it will not. But here’s the fun part; if the OS requires memory, thereby creating the condition where the buffers should be returned (de-committed) the Lazy Writer must first determine if this is possible. If, for instance, a buffer is pinned or is currently involved in an I/O operation, the Lazy Writer cannot return the buffer and will wait until the next cycle to re-evaluate the situation.

So, in some cases DBAs believe that SQL Server does not release memory because there is a delay, or because the Lazy Writer recognizes it simply does not have to de-commit any committed buffers.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>