Recently started seeing this Information Item pop in the event logs:
A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 2091292, committed (KB): 4140648, memory utilization: 50%%.
This occurs when you are using SQL Sever Server standard edition 64 bit on a Windows 2003 64bit system. There are a couple of good support articles, but ultimately 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
Still had the same issue where this kept popping up and we had extremely slow performance. Now this was a small VM with 4 GB RAM where 2 GB is maximum limit allocated to SQL Server. This left 2 GB for the OS and Team Foundation Server. We have also turned off antivirus and any large backup job. 2 GB RAM should be plenty because there’s no other app running on the SQL Server box. But the page out still happens. When this happens, SQL Server becomes very slow. Queries timeout, the error logs fill up, transactions abort. Sometimes this problems goes on for 30 to 40 minutes and TFS becomes slow/unresponsive during that time.
Now, what we tracked the cause down to for SQL Server to page out ended up being the File System cache somehow gets really high and forces SQL Server to trim down. 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.
The solution was to use this program: http://technet.microsoft.com/en-US/sysinternals/bb897561.aspx to manually set the Cache of the system file cache. It’s not a great solution because it needs to be run each and every startup or else the system file cache resets back to unlimited, but useful when you’re working on a sever with limited resources to keep applications running smoothly.
