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

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.

One Response to “My SQL Server isn’t releasing memory, but it’s barely doing anything. What gives?”

  1. Billie K. Says:

    Was trying this goto a random blog feature on randomizer and ended up here, a great way to read something new like this. Thanks for taking your time and efforts to write this blogpost.

Leave a Reply