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.
- The amount of memory that your Operating System needs. Typically about 500 Megs.
- The amount of memory that SQL Server will need on a typical day to hold the buffer cache and procedure cache.
- 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.
Hi Mr Denny, can you adivse does this apply to sql 2000? we just migrated a sql 2000 instance to vmware and we are having performance issues. CPU pegged at 100% and sql memory using 9 gb – physical memory is 5gb
SQL 2000 Standard Edition does not have the lock pages in memory. How much memory did the old server have? How many CPUs?
SQL2005 X64 STD with SP4 in Windows Server 2003 X64 Enterprise O/S – Virtualised – 24GB Ram
I have set MAX and Min Mem to 18GB (leaving 2GB for OS,2GB for other apps/2GB for drivers etc) – if i were to use Lock pages,do i still need to enable traceflag option (with SQL2005 SP4)
Even with the newer builds like SQL 2005 SP4 you’ll need to include the trace flag in order to get lock pages in memory with the standard edition.