Two weeks ago I presented my session entitled “Squeezing Top Performance from Your Virtualized SQL Server” at the SQL PASS Summit in Charlotte, North Carolina. One comment that I made during my presentation that seemed to spark some debates is how I normally enable the ‘Lock Pages in Memory’ feature of SQL Server as part of my default build template unless a good reason exists not to do so. This recommendation contradicts other expert opinions from some of the people that I greatly respect and admire. I have continued to receive emails asking me to debate and weigh the pros and cons of this, and I’ll present my reasons why I enable it by default, as well as why or why not you should enable or ignore this setting in your environment.

First, let’s dig into this setting a little deeper. Then we will work on the reasons specific to your environment and application that you might help you to want to enable or ignore the setting.

Background

Lock Pages in Memory (or LPIM) became a standard setting for SQL Server installations with the introduction of 64-bit SQL Server 2005. Occasionally SQL Server would be forced to page out large quantities of memory randomly because of memory pressure from within Windows Server. When this action would take place, SQL Server’s performance would be temporarily reduced, and usually caused very negative affects to the application performance. Windows would instigate this action because of memory pressure, usually from background application activity or memory leaks in applications or drivers.

Enabling LPIM simply instructs SQL Server to ignore the request from Windows to give up memory.

SQL Server is designed to attempt to keep fetched data pages in memory for as long as possible to reduce disk I/O. If that data that is normally read from memory is now back on disk, the performance impact is that it takes much longer to complete the task, all while driving up disk activity. The community has been torn as to when and why to enable this feature for quite some time, and there never will be a “right” answer to this riddle.

However, understanding your environment and your system performance characteristics can help you determine the right setting for your application.

Now Introducting Virtualization

Now, let’s say your key SQL Servers are all virtualized under VMware vSphere. I leave out Microsoft Hyper-V here because Hyper-V will not let an administrator overcommit memory on an individual host. Dynamic Memory is different, and I will cover that in an upcoming post.

Continue reading on DavidKlee.net.