A significant part of sql server process memory has been paged out

On a SQL Server 2005 x64 Standard Edition cluster I get the error listed below and then the SQL server service restarts. The SQL server is unavailable for 5-10 minutes during that time. Any ideas?

Error:

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 647 seconds. Working set (KB): 11907776, committed (KB): 28731732, memory utilization: 41%%.

This means that Windows has decided to move SQL Server memory out of RAM and stored it in disk until it's needed again. This article has some more information on how to avoid this:

http://support.microsoft.com/kb/918483/en-us

You also mentioned that SQL Server has restarted after this. Could you please post the last 10 lines or so of errorlog when this occurred?

Thanks,

Fabricio.

|||

I did look at that article but I don't think that will work for SQL Standard Edition.

Note SQL Server 2005 Enterprise Edition is the only edition that is designed to use lock pages in memory.

|||

In that case, try to identify what's causing Windows to page SQL Server out to disk. SQL Server tries to fill up the available memory on the machine. When you have other apps running on the same machine, they may use a lot of memory and push SQL Server out to the swap file. SQL Server will detect this and trim its memory, but in some rare cases it can't release enough memory or can't release fast enough and may end up being swapped out. This message is to notify you that such event happened and that SQL Server's memory is being fetched from disk, causing some temporary slowness.

Please, let me know if you need help figuring out what app is using memory on the machine.

Thanks,

Fabricio.

|||

I believe the KB article is incorrect stating it’s only valid for x64 Enterprise Edition. I encountered the same problem you reported and the Lock Pages In Memory policy work just fine with x64 standard edition.

|||

This article explains the problem I was having:

http://blogs.technet.com/askperf/archive/2007/05/18/sql-and-the-working-set.aspx

The system was running Windows 2003 SP1. Have upgraded to Windows 2003 SP2 and have not had the problem since.

|||

Kmoskal,

The KB article is correct. Only SQL Server Enterprise will lock pages in memory. You may set the priviledge with any other edition, but it will have no effect in SQL Server. I guess you just have been lucky (and SQL Server has been doing a great job keeping itself in RAM) :-)

Thanks,

Fabricio.