Oct 122010
 

T-SQL Tuesday LogoThis Month’s T-SQL Tuesday topic comes from Sankar Reddy (blog | 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.
Startup Parameters Screenshot

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.

  11 Responses to “Two Myths About ‘Lock Pages in Memory’”

  1. [...] Bob shares good information about Lock pages in memory and AWE in SQL Server. These are definitely one of the most talked about myths in SQL Server. [...]

  2. We have a W2K8-R2 x64 w/SQL2005-SP3 std x64 box, 48GB RAM. We had some periodically (every 15 min.) “slows” of 20-30 seconds until we enabled LPM. That went fine for 5-6 weeks until the next reboot. Then, the LPM seemed to stop working. We had a few applications installations during this lap (Arcserve Replication Software, etc.) What could cause LPM to stop working ? Should I re-apply SP3 for SQL2005 ? The setting is still there.

    Thanks.

    • Hi Sylvain,

      I obviously can’t diagnose your problem over my comment board, but I’m happy to offer a few thoughts. I highly doubt it stopped working, though I wouldn’t be surprised if its effects were diminished, especially if you installed some applications in the meantime like you say you did. Have you checked the SQL Error Log? If you see the “Using locked pages in buffer pool” message, then LPM is definitely active. Is it possible other applications are consuming enough resources (memory) that SQL Server can’t get the memory it needs? HTH.

      Bob

  3. Thanks for replying so quickly. It’s appreciated. I just read that Microsoft launched a SQL 2005 SP3 Cummulative update 4 a few months ago: (http://support.microsoft.com/kb/970279/en-us). According to some other blogs, LPM does not work with SQL 2005 x64 std SP3 until this patch is applied. So, if for us, it worked once and then stopped working, maybe that was caused by an update of W2K8 or an application we installed, OR with just got lucky ! We plan to try this patch very soon. I’ll let you know the results.

    Sylvain

    • Ahh, I didn’t see that in your previous post. Yes, you need CU4 for SP3 in order to use LPM (how’s that for abbreviations?!). Hopefully it all gets sorted out once you install CU4 or later. Best of luck!

  4. Hi, Bob! Just to let you know that, so far, we think we solved our problem with CU4 for SP3. We put the trace -T845, and it now uses LPM and stopped doing his “slow-downs”.
    Thanks, Sylvain.

  5. Hi Bob,

    Does lock pages in memory work for SQL 2005 64bit Standard SP4?

    thanks.

    • Yes it should work in SP4, but you will need to enable trace flag 845 as specified in this post.

      • Thanks for your input Bob. Trace flag 845 has been added as a startup parameter and lock pages in memory has been enabled on windows level. All this was done a few weeks back and sql error logs do not contain the startup info anymore. Can you please let me know how to confirm that lock pages in memory works without restarting the instance? Thank you.

        • If you haven’t restarted the instance, then lock pages in memory won’t be active. SQL Server only checks for that privilege at startup and if it’s not present at that time, LPM will not be active until the service has been restarted again and the privilege is present.

          The only way I’m aware of for telling if LPM is active or not is to check the SQL error log for the message as described in the post. Sorry I don’t have anything more helpful than that, but good luck!

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>