Windows policy Lock Pages in Memory option and SQL Server instance in VM

Memory

As we know, this Windows policy Lock Pages in Memory option determines which accounts can use a process to keep data in physical memory, preventing the Windows operating system from paging out a significant amount of data from physical memory to virtual memory on disk. This Windows policy is disabled by default. This Windows policy (Lock Pages in Memory option) must be enabled for SQL Server service account. That’s because, setting this option can increase the performance of SQL Server instance running on the virtual machine (VM) where paging memory to disk is expected. When not enabled, there is a risk that SQL Server buffer pool pages may be paged out from physical memory to virtual memory on disk.
*Note: Only applies to SQL Server instances running on the virtual machine (VM).

You can use the following Transact-SQL script to check whether or not this Windows policy is enabled for SQL Server Service Startup account:

SET NOCOUNT ON;

DECLARE @CMDShellFlag [bit] ,
		@CheckCommand [nvarchar](256);
		

DECLARE @xp_cmdshell_output TABLE
    (
      [output] [varchar](8000)
    );

IF NOT EXISTS ( SELECT  *
                FROM    [sys].[configurations]
                WHERE   [name] = N'xp_cmdshell'
                        AND [value_in_use] = 1 )
    BEGIN
		
        SET @CMDShellFlag = 1;

        EXEC [sp_configure] 'show advanced options', 1;

        RECONFIGURE;

        EXEC [sp_configure] 'xp_cmdshell', 1;

        RECONFIGURE;

        EXEC [sp_configure] 'show advanced options', 0;

        RECONFIGURE;
    END

SELECT  @CheckCommand = 'EXEC [master]..[xp_cmdshell]' + SPACE(1) + QUOTENAME('whoami /priv', '''');

INSERT INTO @xp_cmdshell_output
        ( [output] )
EXEC [sys].[sp_executesql] @CheckCommand;

IF EXISTS ( SELECT  *
            FROM    @xp_cmdshell_output
            WHERE   [output] LIKE '%SeLockMemoryPrivilege%enabled%' )
    SELECT  'Windows policy Lock Pages in Memory option is enabled' AS [Finding];
ELSE
    SELECT  'Windows policy Lock Pages in Memory option is disabled' AS [Finding]; 

IF @CMDShellFlag = 1
    BEGIN

        EXEC [sp_configure] 'show advanced options', 1;

        RECONFIGURE;

        EXEC [sp_configure] 'xp_cmdshell', 0;

        RECONFIGURE;

        EXEC [sp_configure] 'show advanced options', 0;

        RECONFIGURE;
    END

SET NOCOUNT OFF;

Here are instructions to enable Lock Pages in Memory option Windows policy:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc.
  2. On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
  3. Expand Security Settings, and then expand Local Policies.
  4. Select the User Rights Assignment folder. The policies will be displayed in the details pane.
  5. In the pane, double-click Lock pages in memory.
  6. In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
  7. In the Select Users, Service Accounts, or Groups dialog box, add SQL Server Service Startup account.
  8. Restart SQL Server for this change to take effect.

For more information, refer to article “Enable the Lock Pages in Memory Option (Windows)” on the Microsoft Development Network website.

Very Important Note: You must also configure the following two server memory options, min server memory and max server memory, if you enable Windows policy Lock Pages in Memory option. That’s because these memory options help you to control the amount of server memory with SQL Server can consume. Ignoring this recommendation can severely reduce SQL Server performance and even prevent SQL Server from starting. For more information about how to configure these two memory options, refer to article “Server Memory Server Configuration Options” on the Microsoft Development Network website.

I hope you will find this post useful. 🙂

Have a nice week….

Basit

One thought on “Windows policy Lock Pages in Memory option and SQL Server instance in VM

  1. In our environment, I have found that you cannot add the SQL Server Service to the Lock pages in memory setting if using an AD account. I am still not sure why this is the case, but it does not allow the account to be added. However, to work around this issue, I create a local windows group called (SQL Locked Pages In Memory). I add this group to the Locked Pages In Memory User Rights Assignment. It actually works out better for me since I can add several SQL Server Service accounts to the Local Windows Group, thus enabling them all when dropping them into this bucket.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s