Written by: Cindy Gross
Monday, April 28, 2008 6:53:33 AM

Microsoft has recently revised the documentation on the below message due to our recent history with troubleshooting the warning:
A significant part of sql server process memory has been paged out.
The below recommendations apply whether or not you have ever seen the above message. If you follow these recommendations you reduce the chance of encountering this warning which is generally accompanied by a severe performance degradation.
This KB article is now more detailed about the specific recommendations.
918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
PSS Blog: SQL Server Working Set Trim Problems? - Consider...
http://blogs.msdn.com/psssql/archive/2008/03/03/sql-server-working-set-trim-problems-consider.aspx
Basically these are the general, default recommendations. The defaults may not be appropriate for every situation, but they can be considered a general guideline for an average SQL Server system.
• Make sure you’re on Windows 2003 SP2 or higher or Windows 2008. Windows 2008 trims memory differently and is much less likely to forcibly trim SQL Server’s memory.
• On Windows 2003 SP2, set the HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\SessionManager\MemoryManagement\SystemCacheDirtyPageThreshold registry key value as described in: 920739 You may experience a decrease in overall system performance when you are copying files that are larger than approximately 500 MB in Windows Server 2003 SP1 or in Windows Server 2003 SP2 http://support.microsoft.com/default.aspx?scid=kb;EN-US;920739
• Verify you have the latest server BIOS and the latest drivers and firmware for your network adapter(s). If your vendor doesn’t have fixes to address conflicts with the TCP Chimney Offload settings, then you may need to disable TCP Chimney Offload. For more details see: 942861 Error message when an application connects to SQL Server on a server that is running Windows Server 2003: "General Network error," "Communication link failure," or "A transport-level error" http://support.microsoft.com/default.aspx?scid=kb;EN-US;942861
• Change your advanced system properties, set the Performance Options “Memory usage” to adjust for best performance of “Programs” as opposed to “System Cache” or file system.
• If you are writing your own drivers, avoid using using the MmAllocateContiguousMemory function and avoid setting the value of the HighestAcceptableAddress parameter to less than 4 gigabytes (GB). If you can isolate a memory trimming problem to the use of a particular driver, ask the driver’s vendor if/how they use the above function.
• Consider applying this Windows 2003 hotfix if you cannot track down and replace the driver that is causing trimming to occur: 938486 A Windows Server 2003-based computer becomes unresponsive because of a memory manager trimming operation that is caused by an indeterminate module that requests lots of memory http://support.microsoft.com/default.aspx?scid=kb;EN-US;938486
• Set your SQL Server configuration value for “max server memory” to an appropriate value. This value will leave plenty of memory for the OS and other applications (including SQL Agent, Virus Scanners, IIS, Reporting Services, Analysis Services, SSIS, backups, SQL Server’s non-buffer pool memory, XProcs, In-process OLE DB drivers, CLR, etc.). Allow for a peak load, even if that only occurs periodically, like when monthly reports are run.
• If your other applications on the system handle memory allocations in a reasonable manner, grant the Lock Pages in Memory privilege to the SQL Server startup account. Note that this privilege is honored only by the Enterprise Edition of SQL Server 2005 x64, though it is honored by both Standard and Enterprise Edition in 32bit due to AWE. Allowing this privilege may not be the best option in all cases, but if you have properly configured the rest of your system it is more likely than not to be the best setting. Remember that other applications on the system have the biggest impact on whether this privilege is appropriate, because those applications have to be written to “play well with others” with regards to memory.
• Monitor your memory on the box to verify your settings remain appropriate.
• Monitor all the event logs and the SQL Server error logs and address any errors immediately.

Trackback Print

Location: Blogs Troubleshooting and Tips - Cindy Gross