SQL Server Working Set Trim Problems? - Consider...

Microsoft support continues to receive a steady flow of cases indicating poor, SQL Server performance symptoms.   When the issue is narrowed down we are finding that the working set of SQL Server and many of the processes on the computer have been significantly trimmed.

What we have found is a series of 3rd party and a few Microsoft drivers that are not playing well with the operating system.   Many of these are making memory allocations that are large or contiguous.  When the operating system attempt to forfill the request it can upgrade to a policy that results in significant working set trimming for many processes.  This trimming can trigger poor performance behavior due to the addition paging and other activity that occurs.

On a 64 bit installation the paging file is commonly backed all the time.  To better explain if SQL Server is using 64GB of memory it will commonly already be backed with 64GB of paging file.   So trimming of the working set can happen very quickly.   Just yesterday I looked at a performance monitor where SQL Server workings set went from 80GB to 650MB in a matter of a few seconds.

Some SQL Server Enterprise installations have enabled locked pages for SQL Server.   The locked pages only protects the SQL Server buffer pool allocations.  It does not protect the images, thread stacks and other memory outside of buffer pool allocations.  This can help avoid the paging because the locked pages are not considered part of the available memory for working set trimming.   The behavior of trimming more working sets than just the SQL Server process can trim thread stacks, network structures and other important objects which can still lead to unwanted performance dips. 

Windows 2003

We can use a combination of memory sizing and locked pages to avoid the issue in the vast majority of cases.   What you want to do is monitor your memory counter a peak load.    Doing this with locked pages disabled will let you see all the counters in one place.   Using the values captured you can establish the max and min server memory settings of SQL Server to accommodate the peak work load.   Once you have these targets you can enabled locked pages again.

Take for example a 64GB machine and when run peak load the OS and other applications require 10GB.   You might configures SQL Server max server memory around 53GB (bit of overhead for thread stacks and memory outside buffer pool) to achieve steady server performance.

Windows 2008

Windows 2008 updated the working set policy decisions and avoids many of the significant working set trim.  These changes are designed to avoid significant trims and steady the performance of the server.   This allows SQL Server to adjust to memory notifications and avoid being pages.   Testing has shown that prior to these changes the SQL Server working set could be significantly trimmed.  After the changes the system maintains better working set balance and no longer trims the SQL Server working set aggressively.   Instead the memory notifications that SQL Server listens to can be fired and SQL Server will back-off when required.

The example used if peak memory requires 10GB but common load only 4GB you can keep max memory around 59GB and only when peak load on the system is occurring will SQL Server back off to accommodate the load.

Min Server Memory

Use the min server memory setting with care.   This is a floor to SQL Server.   Once committed memory to reach the min server memory setting SQL Server won't release memory below the mark.   If you set max server memory to 59GB and min server memory to 56GB, but the server needs to back SQL Server down to 53GB SQL Server won't drop below 56GB.    When you combine this setting with locked pages in memory the memory can't be paged.  This can lead to unwanted performance behaviors and allocation failures.

 

Microsoft Knowledge Base Article (https://support.microsoft.com/kb/918483) is being rewritten to expand on this information.   Expect to see the new version around Mid-March.

Bob Dorr
SQL Server Senior Escalation Engineer

Comments

• Anonymous
  March 03, 2008
  Does this apply to MSSQL 2000 32 bit? [RDORR] You can get working set trims on SQL 2000 as well.  Watch your perfmon counters carefully to see it occur.

• Anonymous
  April 16, 2008
  I had a very nice conversation this week with one of the blog readers.   The question related

• Anonymous
  April 26, 2009
      Have you faced any SQL Server working set trimming problems (Don’t know this? Read Here

• Anonymous
  April 29, 2009
  Microsoft ha anunciado que la versión estándar de SQL Server va a soportar Locked Pages http://blogs

• Anonymous
  April 29, 2009
  The comment has been removed

• Anonymous
  May 21, 2009
  Great news!!! Due to over-whelming customer demand for the " Lock pages in memory " support

• Anonymous
  October 27, 2009
  how do I do "Lock Pages in memory" from commandline. In windows 2003, I use ntrights.exe.

• Anonymous
  October 27, 2009
  rephrasing  the question: how do I do "Lock Pages in memory" from commandline on Windows 2008?. In windows 2003, I use ntrights.exe.

• Anonymous
  November 11, 2009
  Am about to build a W2K8,SQL2K8 enterprise cluster and have been toying with the notion of not using lock pages in memory, since it affects release of memory in a cluster failover scenario... If W2K8 is better at managing it, why so much noise about allowing all to enable 'lock pages in memory'