Two Myths About ‘Lock Pages in Memory’

This Month’s T-SQL Tuesday topic comes from Sankar Reddy (twitter) and he’s asking for SQL Server myths and misconceptions to be de-bunked. I’ve got 2 myths I’d like to set the record straight about. They’re rather closely related, and concern the "Lock Pages in Memory" setting.

What’s it do?

Without getting into an entire lesson on how operating systems manage memory, I’ll try to shrink this down into about a 15 second blurb for benefit of those who might not already know. Every computer has a finite amount of memory (RAM). Regardless of how much memory you have, all of the processes running on your computer combined will probably allocate more memory than is physically available. Since not every process needs all it’s memory at any given second, operating systems employ a memory management scheme knowing as "paging", which means that inactive data stored in RAM will be written to disk in a file called the "page file". This typically happens unbeknownst to the application whose data has just been paged. When the application needs this data again, it is read back from the page file into memory and the process continues. The downside of all this, of course, is that reading and writing to/from disk takes time, and is slower than if the data had just stayed in memory to begin with.

"Lock Pages in Memory" (LPM) allows some processes to skirt around this by declaring their allocated portions of memory as "locked", therefore preventing the operating system from paging it to disk. This can result in significant performance gains for high-demand applications such as SQL Server. To grant a process the rights to utilize LPM, check out Microsoft’s instructions.

The Myths

Now that I’m done with the setup, let’s get to the myths. Myth the first is that LPM is only available in SQL Server Enterprise Edition. This once was true, but is no longer the case. Around April of 2009 it was announced that users of SQL 2005 and 2008 Standard Edition are also able to join the party and utilize LPM. To take advantage of this, systems must be upgraded to at least:

SQL Server 2005 Cumulative Update 4 for Service Pack 3
or
SQL Server 2008 Cumulative Update 2 for Service Pack 1

So now let’s say you’re running Standard Edition of a version listed above (or later) and you have the Lock Pages In Memory security setting enabled for the SQL Server Service account, just like you’d do for Enterprise Edition. You’re now good to go, right? Not exactly, but it’s commonly thought that it should work at this point, hence the second myth. Just because you’re using Standard Edition, you’ll also need to set Trace Flag 845 to take advantage of LPM.

Please be forewarned that "Lock Pages In Memory" can actually decrease performance in certain cases. It is not a silver bullet, and should be tested thoroughly in a development environment before deploying to production. Side effects may include nausea, vomiting, and constipation. Do not use Lock Pages In Memory if you are pregnant or thinking about becoming pregnant. If pages in memory remain locked for more than four hours, you should seek immediate medical attention.

To set a Trace Flag on startup, open SQL Server Configuration Manager and select "SQL Server Services" from the list on the left. Then select the desired SQL Server service from the list on the right. Right-click on it, select "properties", and choose the "Advanced" tab. In this case, you will want to make a change to the "Startup Parameters" value, by adding ";-T845" to the end of it. It should look something like the screenshot below. Once it does, click OK. The trace flag won’t take effect until the SQL Server service is restarted.

Once the service is restarted, look in the SQL Error Log. Shortly after startup you should see an entry that reads Using locked pages for buffer pool. The presence of this entry means that Lock Pages In Memory is active.

What about AWE?

Typically when Lock Pages in Memory is mentioned in conversation, Address Windowing Extensions (AWE) isn’t too far behind. AWE isn’t unique to SQL Server, it’s actually part of Windows – an API that allows 32-bit processes to map memory outside of their virtual address space. Did AWE in Standard Edition get the same treatment as LPM? It depends on your version.

For 32-bit, yes – AWE and Lock Pages in Memory (LPM) go hand-in-hand. Processes can’t utilize AWE without the ability to lock pages – it would be a lot more complicated otherwise. If you try enabling AWE without LPM, you’ll see error 5845, which tells you that AWE requires the ability to lock pages, which the current process doesn’t have. Conversely, pages will only be locked if they’re above the 32-bit process 4GB threshold, so granting the ability to use LPM without having AWE enabled will do nothing at all. In order to take advantage of your newly-minted right to lock pages in Standard Edition, Microsoft *had* to give you AWE, or it would be completely pointless.

If you’re using 64-bit SQL Server, you don’t need to care. AWE only applies to 32-bit processes, so the AWE setting is totally ignored in 64-bit versions. It’s not necessary anyway, as 64-bit process have enough address bits to go around. In fact, the feature is deprecated as of SQL Server 2008 R2. This makes sense, as it was announced over three years ago that Windows Server 2008 R2 would be the last Windows Server OS available for 32-bit architectures.

Other Thoughts

• My "quick and dirty" lesson on paging barely scratches the surface – there’s really a lot more to it. If you’re curious and want to learn more, the Wikipedia article on paging is a great place to start
• Lock Pages In Memory only applies to the SQL Server Buffer Pool, which is only one area of memory used by the database. Because LPM (and therefore AWE) can only benefit the buffer pool, it’s perfectly possible to have a server with a large quantity of memory returning "out of memory" errors, not because there’s not enough available memory, but because there’s not enough available memory in the right place.