Here's an issue we've been seeing more and more of on the Performance team lately concerning SQL 2005 and Memory Management.  The issue gets reported as: "Windows Memory Management is trimming the SQL Working Set."  When you review the event logs, you'll see lots of Event ID 17890 messages: A significant part of SQL server process memory has been paged out. This may result in a performance degradation.  This message itself was introduced in SQL 2005 SP2 - basically what is happening is that the working set for SQL is getting paged out by Windows.  If you do a KB Search, you'll find KB Article 918483.

First - let's define what exactly "Working Set" is.  The working set of a program is a collection of those pages in its virtual address space that have been recently referenced.  This includes both shared and private data.  The shared data includes pages that contain all instructions an application executes, including those in its own DLL's and the system DLL's.  As the working set size increases, memory demand increases.  A process has an associated minimum working set size and maximum working set size.  Each time a process is created, it reserves the minimum working set size for the process.  The virtual memory manager attempts to keep enough memory for the minimum working set resident when the process is active, but keeps no more than the maximum size.

If you search around for guidance for SQL Memory Tuning, you'll no doubt find several articles that provide specific guidelines about how much memory to allocate to SQL.  In Slava Oks's Weblog post on the topic of SQL 2005 Memory Pressure, he provides some guidelines around how much RAM to leave for the OS based on the amount of RAM. 

The problem with solid numbers and rules of thumb is that they are subject to change based on the server load.  Every environment is different and has its quirks - so you have to start with a baseline and then tune accordingly.  SQL Server max server memory is ONLY the buffer pool and doesn’t include a few other significant sqlservr.exe memory allocations such as the Multipage Allocations
(MPA), Thread Stacks (1.17GB), Linked Servers, to name a few.  So when you look at a Perfmon for the server in question, you will see the SQL Server Private Bytes counter will be greater than the max server memory configuration.

Let's look at a problem scenario based on the guidelines in Slava's blog:

• Server Configuration: Windows Server 2003 SP1 x64, running SQL 2005 Standard Edition with 8 Processors and 32GB RAM
• According to the guidelines, we should leave somewhere between 2GB and 4GB for the OS and everything else and allocate the rest to SQL.  We'll use 3GB as the midpoint, which means that the SQL max server memory setting is 32-3 = 29GB.
• In this scenario, we had a customer experiencing the event message indicating that SQL's working set was getting trimmed and they wanted to know why - their understanding was that 3GB should have been plenty for the OS based on the rule of thumb ...

Now let's try to generate a baseline with some other factors taken into consideration:

• Physical RAM: 32GB
• Let's leave 2GB for the OS and other applications: 2GB
• And let's allocate 2GB for the MPA / Thread Stacks / Linked Servers etc: 2GB (on x64 SQL Server, 2MB is allocated per worker thread.  The default number of Max Worker threads is 576 for an 8-processor server.  More information about the Max Worker threads option is available on the MSDN site - the link is in the Additional Resources section below.)
• And finally, let's reserve 3GB for all the other applications on the server (AV, backup etc): 3GB
• So now our max server memory setting is: 32-2-2-3 = 25GB
• Remember - this is a baseline calculation - so now we're ready to monitor the server to find out whether or not we have performance issues!

So, getting back to Working Set trimming.  We know that SQL has a large specified amount of memory reserved, but what happens when another process asks for memory?  If there is not enough memory available, the OS memory manager will still attempt to accommodate the new allocation request.  This is accomplished by trimming the working set (reducing in-RAM memory usage by paging out) of other processes, including SQLSERVR.EXE.  If you are trying to find out what's causing the trimming, you'll at least need Performance Monitor data with Process performance objects.  There's one other thing to point out here.  If available server memory drops below 100MB, then the Memory Manager will trim the Working Set of all processes.

Finally, there is one other factor to consider: according to KB Article 905865 if you're running Windows Server 2003, there is an issue (fixed in Service Pack 2) regarding Working Set trimming that takes place whenever you log on via RDP (even with the /console switch).  If you log on at the physical console, this trimming does not occur.  The problem occurs on W2K3SP1 or W2K3 servers with the patch from KB834293 applied.

Until next time ...

Additional Resources:

• KB918483: You can enable the lock pages in memory permissions to prevent SQL Server 2005 64-bit buffer pool memory from being paged out of physical memory
• Max Worker Threads Option on MSDN
• KB905865: Working Sets of all the processes get trimmed when you log on via RDP

 - CC Hameed