VMware and SQL and Lock Pages in Memory

With the recent release of the ability for the Lock Pages in Memory setting to be used on SQL Server 2005 and 2008 Standard Edition I see more and more people shooting them selves in the foot with this setting when running under VMware.  I see this as becoming more of an issue now that this switch is available for Standard edition as I would assume that most virtualized SQL Server installations are done using SQL Server Standard Editions.

Why this setting may being causing performance problems is because the lock pages in memory setting will do exactly what it is suppose to do; it will tell the SQL Server to ignore calls from the OS to flush some data pages from memory.  In a physical environment this usually isn’t going to be much of an issue, provided that your SQL Server’s maximum memory setting is high enough.

However in a virtual environment, specifically under VMware you have another piece to the puzzle to deal with.  This is VMware’s balloon driver.  This driver is installed as part of the VMware Tools pack which you install on your guest machine.  This balloon driver allows the VMware Hypervisor to tells the guest OS that some memory needs to be reclaimed for another operation.  If you do not have locked pages in memory enabled, this isn’t an issue as the Windows OS will receive the command to lower memory usage, and will pass that along to all the applications including SQL Server.  SQL Server will then being freeing up memory to the OS, which VMware will then see as empty data pages in RAM, which it can then reallocate out for other uses.

With the lock pages in memory setting enabled, SQL will not release the memory.  Windows will see this, and find other places to remove memory from.  As this is a SQL Server and there should be nothing else installed besides Windows, your anti-virus, and your backup software, the only other place that Windows can get memory from is by reducing its own memory footprint.  This then causes the Windows OS to begin paging to disk, which will cause a major performance slowdown on the server.

How can I fix this?

The easiest way to fix this is to simply not enable the lock pages in memory setting.  If you do decide that you need to enable the lock pages in memory setting, there are a few things that should configure to prevent these performance problems.

1. Correctly configure your maximum server memory setting using SQL Server Management Studio or the sp_configure system stored procedure.

2. Configure the memory reservation setting within VMware.  This tells VMware that the VM in question needs to have a minimum amount of memory.  Now before you go setting this to the maximum amount of memory that your database could ever use, don’t.  If you do you could end up over committing the RAM to your VM, not leaving enough for your other VMs.  To correctly set this setting, you need to figure out a few pieces on information.

1. The amount of memory that your Operating System needs.  Typically about 500 Megs.
2. The amount of memory that SQL Server will need on a typical day to hold the buffer cache and procedure cache.
3. Room for other applications that will be running on the SQL Server.

Take the sum of these values together and set the VMware memory reservation setting to this amount.  This will prevent the VMware application from attempting to lower the amount of memory that the guest OS is using beneath this value.

The added benefit of doing this may be that it may prevent the need for the lock pages in memory setting all together.

Denny