SQL 2k5 Lock Pages in Memory Standard Edition

Question

SQL 2k5 Lock Pages in Memory Standard Edition

Erich Brinker

SSCrazy

Points: 2973
More actions
December 11, 2009 at 9:54 am

#137665

Hi all
I was wanting to see if anyone has been using the new update for SQL 2k5 Standard Edition sp3 which allows lock pages in memory? I have been considering this, but wanted to see if anyone had been using it?
Thanks
Erich

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I've been using since the day after it was released on my 64 bit servers.

Erich Brinker SSCrazy Points: 2973 More actions · Answer 2

Erich Brinker

SSCrazy

Points: 2973

December 11, 2009 at 10:23 am

#1091298

Thanks. I guess no issues?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Not for servers that are dedicated to SQL Server, have a lot of RAM (more than 8GB), and have a proper configuration setting for max server memory.

Locking pages was added to allow you to correct problems with working set trims by windows which causes performance problems on servers with large amounts of RAM. I've seen a server with 32GB of RAM with max server memory configured at 28GB page have its entire 28GB working set page out. If you have this kind of problem, Lock Pages in Memory helps by using AWE (yes AWE on 64 bit servers too) to allocate the memory preventing it from being paged/trimmed.

If you don't have working set trim messages in your logs, you won't benefit from having lock pages in memory. If your server is a shared application/SQL Server, using lock pages and not setting SQL Server correctly to leave memory for the OS and other applications running on the server will cause performance problems or OOM Exceptions for the applications or OS.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

To back up what Jonathan said, here is an article that talks about Lock Pages in Memory:

http://blogs.technet.com/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Erich Brinker SSCrazy Points: 2973 More actions · Answer 5

Thanks guys. I have a server that had over 10 of the 13 gib paged out from under it today. I am still in the process of troubleshooting, but was looking at this as an option.

Thanks for the help

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

One thing to keep in mind is that if you use Lock Pages in Memory, you won't see SQL Servers memory consumption in Task Manager. You have to track AWE mapped memory using perfmon and the SQL Server:Memory Manager\Total Server Memory (KB) counter.