03 May 2007 11:41
Using 64 bit SQL Server 2005? Lock Pages in memory!
Watch yourself on this one, my client has an 8GB machine, two SQL instances (standard edition), one uses 6GB (production) and one uses 512MB of memory (development), the server is dedicated so there is a reasonable amount of available memory.
Suddenly all hell broke loose – people could not connect, I could not connect – not even via the dedicated admin connection; I could not even stop the SQL Server service.
In the eventvwr there was a message “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): 13128, committed (KB): 6239692, memory utilization: 0%%.”
What does that mean? Well, basically the entire working set SQL Server was using got paged out of memory to disk, leaving SQL Server with just 13Mbytes of RAM in use – no wonder SQL Server became unresponsive! “Performance degradation” – now there is an understatement or what!
This support article documents the problem: http://support.microsoft.com/kb/918483.
Our particular circumstance came about when we copied a number of 1.1GByte files off the server.
So, I guess the general advice here is to “Lock Pages in memory”.
Filed under: SQL Server