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
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
We went through many support articles like:
How to reduce paging of buffer pool memory in the 64-bit version of
SQL Server 2005
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
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
I have found what causes SQL Server to page out. File System
cache somehow gets really high and forces SQL Server to trim
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
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.
After I used SysInternal’s
CacheSet to reset file system cache and set around 500 MB as
max limit, memory started to free up.
SQL Server started to see more RAM free:
Then I hit the “Clear” button to clear file system
cache and it came down dramatically.
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
UPDATE – THE FINAL SOLUTION!
The final solution is to run this program on Windows
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
Download the program from this page:
Go to the end and you will get the link to the