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.
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 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.