A significant part of sql server process memory has been paged out. This may result in performance degradation

If you are using SQL Sever Server standard edition 64 bit on a
Windows 2003 64bit, you will frequently encounter this problem
where SQL Server says:

A significant part of sql server process memory has been paged
out. This may result in performance degradation. Duration 0
seconds. Working set (KB) 25432, committed (KB) 11296912, memory
utilization 0%

The number in working set and duration will vary. What happens
here is SQL Server is forced to release memory to operating system
because some other application or OS itself needs to allocate
RAM.

We went through many support articles like:

  • 918483:
    How to reduce paging of buffer pool memory in the 64-bit version of
    SQL Server 2005
  • 905865:
    The sizes of the working sets of all the processes in a console
    session may be trimmed when you use Terminal Services to log on to
    or log off from a computer that is running Windows Server 2003
  • 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 Service Pack 1

But nothing solved the problem. We still have the page out
problem happening every day.

The server has 16 GB RAM where 12 GB is maximum limit allocated
to SQL Server. 4 GB is left to OS and and other application. We
have also turned off antivirus and any large backup job. 12 GB RAM
should be plenty because there’s no other app running on the
dedicated SQL Server box. But the page out still happens. When this
happens, SQL Server becomes very slow. Queries timeout, website
throws error, transactions abort. Sometimes this problems goes on
for 30 to 40 minutes and website becomes slow/unresponsive during
that time.

I have found what causes SQL Server to page out. File System
cache somehow gets really high and forces SQL Server to trim
down.

clip_image002 "http://omar.mvps.org/images/SQLServerhaspagedoutsignificant_10B/clip_image002.jpg"
width="557">

You see the System cache resident bytes are very high. During
this time SQL Server gets much less RAM than it needs. Queries
timeout at very high rate like 15 per sec. Moreover, there’s high
SQL Lock Timeout/sec (around 15/sec not captured in screen
shot).

clip_image004 "http://omar.mvps.org/images/SQLServerhaspagedoutsignificant_10B/clip_image004.jpg"
width="549">

SQL Server max memory is configured 12 GB. But here it shows
it’s getting less than 8 GB.

While the file system cache is really high, there’s no
process that’s taking significant RAM.

clip_image006 "http://omar.mvps.org/images/SQLServerhaspagedoutsignificant_10B/clip_image006.jpg"
width="340">

After I used SysInternal’s "http://www.microsoft.com/technet/sysinternals/FileAndDisk/CacheSet.mspx">
CacheSet
to reset file system cache and set around 500 MB as
max limit, memory started to free up.

clip_image008 "http://omar.mvps.org/images/SQLServerhaspagedoutsignificant_10B/clip_image008.jpg"
width="557">

SQL Server started to see more RAM free:

clip_image010 "http://omar.mvps.org/images/SQLServerhaspagedoutsignificant_10B/clip_image010.jpg"
width="564">

Then I hit the “Clear” button to clear file system
cache and it came down dramatically.

clip_image012 "http://omar.mvps.org/images/SQLServerhaspagedoutsignificant_10B/clip_image012.jpg"
width="553">

Paging stopped. System cache was around 175 MB only. SQL Server
lock timeout came back to zero. Everything went back to normal.

So, I believe there’s either some faulty driver or the OS itself
is leaking file system cache in 64bit environment.

What we have done is, we have a dedicated person who goes to
production database servers every hour, runs the CacheSet program
and clicks “Clear” button. This clears the file system cache and
prevents it from growing too high.

There are lots of articles written about this problem. However,
the most informative one I have found is from the SQL Server PSS
team:

"http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx"
href=
"http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx">

http://blogs.msdn.com/psssql/archive/2007/05/31/the-sql-server-working-set-message.aspx

UPDATE – THE FINAL SOLUTION!

The final solution is to run this program on Windows
Startup:

SetSystemFileCacheSize 128 256

This sets the lower and higher limit for the System Cache. You
need to run this on every windows startup because a restart will
undo the cache setting to unlimited.

You can run the program without any parameter to see what is the
current setting.

Download the program from this page:

"http://www.uwe-sieber.de/ntcacheset_e.html">http://www.uwe-sieber.de/ntcacheset_e.html

Go to the end and you will get the link to the
SetSystemFileCacheSize.zip

21 Comments

  1. Look at two things.

    1. Lock pages in memory local group policy set for the SQL Server service account.

    2. AWE (yes, even in 64 bit) enabled in SQL Server 2005 configuration.

  2. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    Has anyone got a definitive fix for this? I have just started getting it on Windows Server 2003 SP2 (64-bit) with SQL 2005 SP2 (64-Bit). Also does anyone know where I can get ClearCache?

    Thanks

    Russell

  3. We are running into this 17890 all of a sudden on SQL 2005 SP2 as well. We have found an interesting way to make this happen on command though. We were humming along just fine until we created a virtual server machine and turned it on. As soon as we turn it on, SQL barfs this message. It then repeats every so often until we turn the machine off, then the problem ceases to exist.

    The virtual server is microsoft virtual server 2005 R2. We have tried it with a couple of virtual servers, as soon as you start one, the SQL 2005 installed on the host starts puking these errors. Wondering if anyone else has observed this as the cause for these mysterious issues?

  4. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    How can I obtain the sql server report that you use in this article (I intend theese one with gray background)?

    Thank you, bye

  5. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    Omar,

    Can you tell me what utility that you used to display the performance counters in your examples above?

    Thanks.

    Bryan Hunt

  6. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    Omar,

    Please, do you may help me, with the Clearcache.exe, file. We have now the same problem and need urgently solve this.

    john_zuniga at h o t m a i l

    Thanks.

  7. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    As an employee with Microsoft SQL Server Support and very familiar with this issue, I want to first thank Omar for his detailed analysis. However, this is one of many possible causes for this problem.

    The bottom line: The issue is caused because the OS trims SQL Server in response to memory pressure it is dealing with. SQL Server has no control over being trimmed. The OS controls this.

    What customers should do in response to this error is to do as Omar did and look to see what is causing the OS pressure. In this case it was clearly the System Cache, but that in no way is applicable to every situation.

  8. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    Hi guys, the gray screenshots you see are collected from Performance Monitor. It comes with Windows.

    Control Panel => Administrative Tools => Performance

    Click on the + button. Now select SQL Server related categories from the dropdown and add the counters you want to monitor.

  9. One must select Report View (Ctrl+R) to see the data in Performance Monitor as shown.

  10. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    I am strugeling with Micorsoft since 3 weeks regarding this issue. I am thrown between the SQL & OS team. I have applied SP2, CU#9, KB 920739 & 953600. Any other hints?

  11. Can this issue be solved by upgrading the underlying operating system to Windows 2008?

  12. I think this has cropped back up with SQL 2008

  13. We're having the same issue with SQL 2008 Ent Ed 64 on Windows 2008.

  14. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    We are having the same issue (SQL 2005 SP2/Windows 2003 SP2 x64). I'd also be interested to hear if anyone has had any luck with the Windows Dynamic Cache Service tool.

    Also to re-address the original issue, it seems like the trick is to determine which process is originally causing Windows to trim its memory. In the above analysis it is assumed to be due to the Windows file cache, but possible issues with hardware/drivers are also alluded to. Has anyone found any commonality that may point towards a driver issue? For reference we are running on Dell servers with Broadcom NICS/drivers.

  15. re: A significant part of sql server process memory has been paged out. This may result in performance degradation

    We installed Microsoft Windows Dynamic Cache Service and it seemed to work for a few weeks, but now we're back to the same problem again.

  16. We think we have isolated our issue to the Qlogic HBA driver that our SQL servers use to connect to the back end storage, but unfortunately the latest available driver doesn't seem to resolve the issue. We still have some other workarounds that MS has recommended left to try – if any of these work I'll post an update.

  17. When you start a VM in Virtual Server 2005, and sometimes just at random on a host with at least one VM running, all processes on the host will have their working set trimmed.

    I finally found a solution to this, it was causing me a major headache for some time.

    The solution is to run Virtual Server and Virtual Machine Helper under a different user account rather than NETWORK SERVICE / LOCAL SYSTEM.

    I tried it with a limited user account and set permissions in various folders without success (VS would start and stop immediately). Eventually I just tried with a local admin account and it worked.

    Now when I start a virtual machine…NO WORKING SET TRIM!!!

    Thanks Microsoft. Another great reason to continue to be married to your wonderful products.

Leave a Reply