03 May 2007 11:41 tonyrogerson

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:

Comments

# Know your memory usage or get it locked up

03 May 2007 20:52 by SimonS SQL Server Stuff - Microsoft SQL Server MVP

Tony's come across an interesting situation where SQL Server lost all its memory . Well not quite, it

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

04 May 2007 10:07 by Colin Leversuch-Roberts

Yeah it's a bit of strange one, I assumed as I was late, so to speak, to 64 bit everyone knew about this otherwise I would have blogged it myself. There's also a setting for system cache you might want to check in w2k3 too.

# Know your memory usage or get it locked up

08 May 2007 11:47 by SimonS' SQL Server Stuff

Tony's come across an interesting situation where SQL Server lost all its memory . Well not quite, it...

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

09 May 2007 10:03 by thomas

Note that 'lock pages in memory' only applies to SQL Server 2005 Enterprise Edition (see the 'applies to' bit at the bottom of the link you posted). This option is ignored in Std Ed, so it seems if you get this error on Std Ed, you are basically stuck with it!

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

09 May 2007 18:53 by tonyrogerson

Hi Thomas,

Good spot; however, I think the KB article is wrong (I'm pursuing that).

Book online clearly states you can use Lock Pages in Memory on x64 Standard and is actually a recommended configuration.

Tony.

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

09 May 2007 19:39 by tonyrogerson

You appear to be right Thomas, checking a couple installs I have DBCC MEMORYSTATUS does not report AWE use which is what you would get if you lock pages in memory. Nor any entry in the log.

Looks like Books online is wrong.

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

10 May 2007 09:37 by thomas

Yeah I read this newsgroup thread

http://groups.google.co.uk/group/microsoft.public.sqlserver.server/browse_frm/thread/4b03cc132e0550e3/a1171fc3bcbd4e43?lnk=raot&hl=en#a1171fc3bcbd4e43

where it is stated that an MS engineer has confirmed that Lock Pages in Memory is ignored in Std Edition. Seems somewhat crap that those of us running Std Ed on 64 bit servers are stuck with these problems. Maybe SP3 will help us.

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

11 May 2007 20:40 by TheSQLGuru

I just had a client get hit by TWO separate bugs that would cause memory flushing and improper RAM allocations.  G-d awful performance implications as you would expect.  The first was an OS bug (win2k3sp1) copying large files (>500MB) to a slower drive than the source drive.  The second was an HP Integrated Lights Out driver bug.  Fortunately they both had patches and things are MUCH better now!  It is really interesting watching sql server ram usage in task man go from 9+ GB to ZERO instantaneously, then beat the snot out of the drives as it paged back in from VM.  :-))

# re: Using 64 bit SQL Server 2005? Lock Pages in memory!

31 May 2007 07:42 by GNagel

@TheSQLGuru:

can you tell me more about the 'HP Integrated Lights Out driver' bug? I think i have the same problem but don't know where to find the fix for this bug.

thx, gerhard

# SQL Server error: messages are appearing when the working set of SQL Server 2005 process reaches 50 percent of the memory that is committed to the process.

07 March 2008 11:19 by SQL Server Security, Performance & Tuning (SSQA.net)

Have you seen the above message within the SQL Server error logs? If not then no need to worry and make