2

I understand that setting the lock pages in memory privilege to the service account housing SQL2005/2008 services on a 64 bit system uses the AWE API and effectively stops SQL's buffer pool from being paged to disk - increasing stability.

But if you've a dedicated box with enough free memory so you're confident SQL won't page - is native SQL 64 bit memory management faster?

3 Answers 3

5

The short answer is, if used correctly, Lock Pages In Memory can enhance SQL Server performance on 64-bit systems.

The long answer has us taking a few steps back.

First of all, you need to understand that there is a difference between paging and paging to disk. With modern operating systems, ALL memory is allocated in pages within the VAS (Virtual Address Space). This is not a bad thing, this is how it works.

There are two problems with the way SQL Server works with buffer pool memory when Lock Pages is not used:

First, it is more expensive to allocate memory. When a process starts or asks for more memory, the OS creates new pages for it on demand. So, normally, as SQL Server runs it will be requesting, getting, freeing pages by chatting with the OS all the time, which is expensive.

Second, NUMA doesn't work very well. Proper NUMA support means that the application can count on a page being resident in physical memory that is the closest to the processor the thread is on, but when the OS is taking over the paging, SQL Server can't guarantee that. It has to check for NUMA residency for each page request, which is expensive as well.

When you enable Lock Pages in Memory, SQL Server doesn't have to deal with either of these issues for the buffer pool because it uses the AWE API for memory management.

Firstly, when it asks for memory, it does so only once, and gets a list of pages from the OS all at once. Then, in the future, it can allocate these pages as it needs to without having to ask the OS for them.

Secondly, because it is locking the pages from the OS, it knows that the properties of these pages won't change. So if the page is a NUMA page, it will put it in the NUMA Buffer Pool and not have to worry about verifying that it is in NUMA the next time it checks.

You will notice these improvements both as the database ramps up (when memory allocation would happen), and also over time (when normally SQL Server would be dealing with the OS for page allocation/deallocation).

If you want further details, there is an excellent technical article on this by Slava Oks's, when he was on the SQL Server team. He also has a Q&A post on the topic another great article on how NUMA works in SQL Server.

2
  • +1 good to see Slava's excellent articles referenced Aug 1, 2009 at 19:53
  • Thanks for the answer and the links - it's made me realise that I was too aggressive when setting the size of the buffer pool. Aug 4, 2009 at 14:51
1

As I understand the Locked Pages in Memory setting from my conversations with the SQL Server Product Team the only thing this setting controls is that it allows the SQL Server to ignore the OSs instructions to page all data to disk and flush the memory.

Under normal circumstances having the locked pages in memory setting disabled won't do anything good or bad to you. It's the odd occurrence where having lock pages in memory will help you out. An example being there was a bug in Windows 2003 RTM that when running SQL Server and you initiated a remote desktop session onto the server, the OS would tell the SQL Server to flush all memory to disk for no reason. If you had the Lock Pages in Memory setting enabled your SQL Server would ignore this instruction, if you did not have this setting set then your SQL Server would flush it's buffer cache to disk as instructed.

There are other cases of RAID drivers, and HBA drives having bugs in them which cause this same issue.

I talk about this here and Bob Ward of the SQL Server team talks about it here.

1
  • No problem. Happy to.
    – mrdenny
    Aug 4, 2009 at 21:16
0

It my experience any performance gains are offset by the problem that one day this will bite you. I'd say that if you have relatively slow data growth and few database code changes, you could effectively implement this and get a little benefit. If however you database is heavily used and sometimes sql pages out, I'd say do not turn it on. As the Ask the Performance Team article states:

"Once you have configured your server based on the results of the Performance Monitor analysis, that decision really rests with you."

I have yet to find justification to turn it on, on any of my systems

0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .