I have written about this topic before (in my old SQLCommunity blog) but I still see lots of questions about it. So here is a simplified explanation of this memory trimming issue that is common with SQL Server on Windows 2003.
On Windows 2003 you may occasionally see memory trimming messages in your SQL Server error logs: “A significant part of sql server process memory has been paged out. This may result in a performance degradation.” Generally you will see this at the same time that the performance of your SQL Server becomes very bad. What happens is Windows has to free up memory rapidly and it cannot wait for the normal, polite “hey, I need some memory, are there any apps willing to give me some”. This can happen due to severe memory pressure, when certain APIs are called, or due to certain Windows 2003 bugs (most of which are fixed in Windows 2003 SP2 or post SP2 hotfixes). Windows 2003 will respond to this pressure by trimming GBs of memory at once. Windows 2008 responds much better in that it trims MBs at a time instead and typically does not cripple running applications such as SQL Server. Therefore it is expected that you will NOT see this scenario on Windows 2008 or later.
Ideally you would find and resolve the reason for the external memory pressure, but in many cases that is impractical when a workaround may alleviate the problem. On Windows 2003 you can try combining the “lock pages in memory” (LPIM) setting with a “max server memory” setting such that all instances of SQL added together will still leave “enough” memory for everything else running on the box (SQL Agent, SSIS/DTS, RS, AS, anti-virus, backups, batch processes, services, etc.). LPIM keeps Windows from trimming SQL’s buffer pool. Do NOT ever enable LPIM without also setting “max server memory” for all instances to an appropriate value. There are rare cases where LPIM can cause more problems than it solves. This happens if Windows still faces this type of memory pressure and has to trim SQL Server memory outside the buffer pool which could mean trimming the mapped SQL exes and dlls and causing even bigger performance problems. So monitor the system carefully after you add LPIM and make sure you’ve properly set the “max server memory” setting. The currently recommended fix is to upgrade to Windows 2008 or Windows 2008 R2 where the memory trimming is handled much differently.
2001745 Working Set Trimming can negatively impact SQL, Exchange, and Operating System performance under Windows 2003
http://support.microsoft.com/kb/2001745
918483 How to reduce paging of buffer pool memory in the 64-bit version of SQL Server
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918483
How to enable the Lock Page in Memory option (Windows)
http://msdn.microsoft.com/en-us/library/ms190730(SQL.90).aspx
Thanks for the post, Cindy. I thought I would share my story about SQL Server paging. It has been going on for about six-weeks, and I finally discovered what was causing it yesterday. We have two virtual SQL Server 2005 64-bit Ent. Ed. servers with 32 GB of RAM, and every two to four-hours one of them would start paging. So yesterday I was watching TaskMan at a time when I thought it would start paging. SQL Server was using 8GB at the time, then I saw a new avp.exe process start up (Kaspersky AV). It didn't consume much in the way of CPU or Memory Usage, but sqlservr.exe dropped to about 150MB in a split second, and CPU for the System process jumped up to about 25% for some time (working on paging I assume). After about 10-minutes, SQL Server was able to start using more memory.
We have Kaspersky AV installed on both servers, and I thought they were configured exactly the same. It turns out that the server that was paging was set to update every two-hours, and to go to Kaspersky's web site instead of our internal update server. Once I disabled the auto updates, the paging stopped. The server that was not paging has Kaspersky configured properly and hits our internal update server. IT was notified, and the case was solved.
It was a double rainbow moment for me.
@Scott – that's a great example of where Windows 2008+ could help. Windows 2003 trims huge amounts of memory. Windows 2008 and later will trim a few MB at a time. Of course, generally you can avoid the trimming completely by setting your max server memory low enough to let that other process get "enough" memory "fast enough" that it doesn't send a "panic" to Windows. Thanks for sharing your real world story!
I am running Windows 2008 R2 and I am having the same issuse
@Sophie – do you actually see a performance problem when the trim happens? On Windows 2008 R2 the amount trimmed should be very small so the performance impact should be neglible. Make sure you've properly set your max server memory.
Thanks Cindy. I upgraded sql to sp1 – 2008 R2. The performance issue is not obvious but strangely I have another instance of sql running and no problems there. The only difference was the service packs for sql. I am watching to logs to see if there is a difference.
I'm having the same problem with Windows server 2008r2 and sql server 2008r2 sp1. As well as the error message above (, all the app domains are marked for unload. We have a number of servers and instances, and the problem seems to be spreading steadily across them: another new one this morning. Taskmgr on the main culprit server shows only 2.5Gb of memory is in use out of 8Gb total physical memory, yet the error is showing up as often as every ten minutes. All our instances have memory capping in place.
Dave? Are you seeing actual performance problems as a result of the memory trimming? On Windows 2008+ the trimming will be in MB rather than GB so the chance of performance issues is greatly reduced. Please don't rely on Task Manager on a server, use PerfMon and pay careful attention to the nuances of the various memory counters. What is using memory outside of the sqlservr.exe process? Do you have max server memory set low enough to avoid memory pressure outside SQL, such as from CLR, SQL Agent, SSIS, and whatever services/apps are running on the box? Is the problem really around CLR rather than the trimming itself?