SQL Server Working Set Trim Problems

En un artículo anterior se mención los problemas de rendimiento que se pueden presentar en SQL Server debido al manejo de memoria, o más bien cuando el sistema operativo no le asigna toda la memoria a SQL Server, para resolver estos problemas el CSS Team tiene una serie de reocmendaciones que incluyo a continuación:

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.

http://blogs.msdn.com/psssql/archive/2008/03/03/sql-server-working-set-trim-problems-consider.aspx

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server