Archive for the ‘Memory Configuration’ Category

Don’t forget to add the Trace Flag 845 when enabling “Lock Pages in Memory” for the SQL Server 2005+ Standard Edition 64-bit

You can enable “Lock Pages in Memory” for the SQL Server service account to prevent paging. For the Enterprise editions, you can just use the following steps and the call it a day:

  1. On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.
  2. On the Group Policy 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 Policy Setting dialog box, click Add.
  7. In the Select Users or Groups dialog box, add the account that runs the SQL Server Service.
  8. Restart the machine for these changes to take effect.

But, if you are running SQL Server 2005+ Standard Edition 64-bit you will need to make sure you have the latest service packs and cumulative update provided that you have verified the compatibility of the applications, and then you have a critical extra step to do to enable Trace Flag 845 as a startup trace flag:

    1. Browse to the SQL Server Configuration Manager.
    2. Double-click to open the SQL Server instance properties.
    3. Click the Advanced tab.
    4. Scroll down to the “Startup Parameters”  row, then click on the drop-down box and then add “;-T845” to the end of the startup parameters.

T845

  1. Restart the SQL Server Service.
  2. Verify the “Using locked pages for buffer pool” message is written in the SQL Server Error log file at startup by running the following command:
    xp_readerrorlog 0, 1, 'lock'

REFERENCES:

How to: Enable the Lock Pages in Memory Option (Windows)
http://msdn.microsoft.com/en-us/library/ms190730(v=sql.105).aspx

Lock pages in memory now available for 64 bit Standard Edition of SQL Server
http://blogs.msdn.com/b/suhde/archive/2009/05/20/lock-pages-in-memory-now-available-for-standard-edition-of-sql-server.aspx

How to configure memory settings for the 64-bit SQL Servers?

First of all, great news! You don’t need to be concerned with the /3GB, /PAE, and AWE switches and configurations for the 64-bit SQL Servers. We must, however, still need to understand how to enable the SQL Server service account in the “Lock Pages in Memory” Windows group policy and also setting the values for the min and max server memory configurations correctly.

A few years ago, when I was young and didn’t know better, I had recommended setting the min and max server memory values to the same. As with relationships, the only way to move on is to apologize to your spouse and admit your mistakes, and show your sincerity in making efforts to change. From now on, I would recommend setting the min server memory a few gigabytes lower than the max server memory depending on the amount of memory available on the server. As we all know, the min server memory value specifies the minimum amount of memory for SQL Server and it is the value SQL Server can shrink to when under memory pressure, the max server memory value specifies the maximum amount of memory for SQL Server. The “Lock Pages in Memory” Windows group policy for the SQL Server service account when enabled can keep data in physical memory rather than paging the data to virtual memory on disk. If setting the min and max server memory values to the same in addition to enabling the SQL Server service account in the “Lock Pages in Memory” Windows group policy, when the Operating System signals with low memory notification flag, SQL Server can only release memory down to the min server memory value and the value is the same at the high value as the max server memory. The response from the Operating System will be unpredictable. It will be similar to bring home only beer without milk at the end of the day. It wouldn’t be pretty.

Let’s go over the overview steps in configuring memory setting for the 64-bit SQL Servers:

  1. Schedule a maintenance window as a SQL Server service restart is required to enable the SQL Server service account for the “Lock Pages in Memory” Windows policy. Notify the business users of this scheduled maintenance window.
  2. Plan and write down how much memory you wish to assign to SQL Server and how much memory to leave for the Operating System and other processes. In case you plan to add more memory, you will also need to know the SQL Server version and edition memory limitations in addition to the Operating System version and edition memory limitations.
  3. Find and write down the SQL Server service account.
  4. Configure the “Lock Pages in Memory” Windows group policy for the SQL Server service account you had written down in step 3.
  5. Set the min and max server memory.
  6. Restart the Windows Server for the “Lock Pages in Memory” Windows group policy to take effect.

Implementation steps are as follows:
1. Schedule a maintenance window as a SQL Server service restart is required to enable the SQL Server service account for the “Lock Pages in Memory” Windows policy. Notify the business users of this scheduled maintenance window.

2. Plan and write down how much memory you wish to assign to SQL Server and how much memory to leave for the Operating System and other processes. In case you plan to add more memory, you will also need to know the SQL Server version and edition memory limitations in addition to the Operating System version and edition memory limitations. Use the following links for references:

Memory Supported by the Editions of SQL Server
http://msdn.microsoft.com/en-us/library/ms143685(v=sql.90).aspx

Memory Limits for Windows Releases
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx

3. Find and write down the SQL Server service account.
Browse to “SQL Server Configuration Manager,” double-click the service “SQL Server (MSSQLSERVER)” for the default instance or “SQL Server (INSTANCENAME)” for named instance, on the “Log On” tab, you will see the SQL Server service account. Write it down as you will need it for a later step.

4. Configure the “Lock Pages in Memory” Windows policy for the SQL Server service account you had written down in step 3.

  1. Click the Start menu, click Run. Type in gpedit.msc.
  2. On the “Group Policy” console, expand “Computer Configuration,” and then expand “Windows Settings.”
  3. Expand “Security Settings,” and then expand “Local Policies.”
  4. Expand the “User Rights Assignment” folder.
  5. Double-click the “Lock pages in memory” policy.
  6. Click the “Add User or Group” button to add the SQL Server service account.

5. Set the min and max server memory.

-- Configure the min and max server memory
-- http://msdn.microsoft.com/en-us/library/ms190673(v=sql.105).aspx
USE master
go
sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'min server memory', 2048 -- 2 GB min, replace with your desired amount here
EXEC sp_configure 'max server memory', 6144 -- 6 GB min, replace with your desired amount here
RECONFIGURE WITH OVERRIDE

6. Restart the Windows Server for the “Lock Pages in Memory” Windows group policy to take effect.

REFERENCES:

How to: Enable the Lock Pages in Memory Option
http://msdn.microsoft.com/en-us/library/ms190730(v=sql.105).aspx

Memory Supported by the Editions of SQL Server
http://msdn.microsoft.com/en-us/library/ms143685(v=sql.90).aspx

Memory Limits for Windows Releases
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx

How to configure memory settings for the 32-bit SQL Servers?

This may sound disappointing with the limitations, the amount of memory a 32-bit process can access is 4 GB by default. To understand memory management for SQL Server, we must first get acquainted with the concept of Virtual Address Space (VAS). The virtual address space for a process is the set of virtual memory addresses that it can use. The virtual address space for 32-bit Windows Operating Systems is 4 GB in size and divided into 2 modes: The kernel mode and the user mode. 2 GB is partitioned for the kernel mode for the system processes and 2 GB is partitioned for the user mode for the user and application processes. SQL Server runs inside the user mode VAS region. Yes. This means that by default SQL Server can use maximum only up to 2 GB of the memory on a 32-bit Operating System. If you wish to allocate more memory to SQL Server on a 32-bit SQL Server, what can you do?

What if you have only 4 GB RAM for the host? First of all, check if the SQL Server version and edition and also the Operating System version and edition can utilize more RAM. If yes, use diplomacy with good reasoning to request for more RAM from the management team. The price of 8 GB of RAM will likely cost less than $50. Yes. You read that right $50. As a last resort, if you can’t increase more RAM, and this may sound awkward, but you can steal 1 GB of memory from the kernel to increase the addressable memory to 3 GB for SQL Server. This is known as the /3GB switch. You need to add it to the Windows boot.ini file. Be very careful before doing this as this will leave only 1 GB for the kernel mode processes.

What if you have more than 4 GB of RAM and you wish to enable the memory for your 32-bit SQL Server? In this case, you can enable SQL Server to utilize the memory over 4 GB through Address Windowing Extensions (AWE). In order to enable AWE, You will need to have the Physical Address Extension (/PAE) switch added to the boot.ini file. Then, verify the amount of memory that’s available in the System properties. In addition, you must first enable the “Lock Pages in Memory” Windows group policy for the SQL Server service account to keep data in physical memory rather than paging the data to virtual memory on disk. Since you will need to “Lock Pages in Memory” you should always set the min and max server memory limits as SQL Server will not release memory to the Operating System unless the Operating System complains with memory low notification flag. Setting the min and max server memory limits will prevent SQL Server from using all the available memory once the max limit is met and it helps SQL Server in releasing only down to the min server memory amount when the Operating System signals the memory low notification. Yes. This sounds a bit overwhelming. Let’s simplify it with an overview and then go through the step by step details on implementation. As always, you should document the steps to make sure you get things right the first time. Try reading “The Checklist Manifesto: How to Get Things Right” by Atual Gawande, even the oldest dogs can be enlightened by how critical it is to have a checklist.

The overview steps required are as follows:
1. Schedule a maintenance window and ask your System Engineers to take a VM snapshot before starting the planned maintenance if it is a VM machine. Run your database backups and script out the logins, SQL Server Agent jobs, and linked servers. In addition, don’t forget to notify the business users of the approved planned maintenance downtime window.

2. Plan and write down how much memory you wish to assign to SQL Server and how much memory to leave for the Operating System and other processes. In case you plan to add more memory, you will also need to know the SQL Server version and edition memory limitations in addition to the Operating System version and edition memory limitations.

3. Find and write down the SQL Server service account.

4. Configure the Operating System to use Physical Address Extensions (PAE). Host reboot is required.

5. Configure the “Lock Pages in Memory” Windows policy for the SQL Server service account you had written down in step 2.

6. Configure to enable Address Windowing Extensions (AWE). SQL Server service restart is required for both the “Lock Pages in Memory” Windows policy and the AWE configuration to take effect.

7. Set the min and max server memory.

Implementation steps are as follows:
1. Schedule a maintenance window and ask your System Engineers to take a VM snapshot before starting the planned maintenance if it is a VM machine. Run your database backups and script out the logins, SQL Server Agent jobs, and linked servers. In addition, don’t forget to notify the business users of the approved planned maintenance downtime window.

2. Plan and write down how much memory you wish to assign to SQL Server and how much memory to leave for the Operating System and other processes. In case you plan to add more memory, you will also need to know the SQL Server version and edition memory limitations in addition to the Operating System version and edition memory limitations. Use the following links for references:

Memory Supported by the Editions of SQL Server
http://msdn.microsoft.com/en-us/library/ms143685(v=sql.90).aspx

Memory Limits for Windows Releases
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx

3. Find and write down the SQL Server service account.
Browse to “SQL Server Configuration Manager,” double-click the service “SQL Server (MSSQLSERVER)” for the default instance or “SQL Server (INSTANCENAME)” for named instance, on the “Log On” tab, you will see the SQL Server service account. Write it down as you will need it for a later step.

4. Configure the Operating System to use Physical Address Extensions (PAE).
In Windows Server 2008:

  1. Open the command prompt as an administrator.
  2. In the command prompt, execute:

    BCDEDIT /SET PAE ForceEnable

  3. Reboot the host is required for this to take effect.

In Windows Server 2003:

  1. Rgith-click “My Computer,” then select “Properties.”
  2. Click on the “Advanced” table and click the “Startup and Recovery” button.
  3. Under “System startup,” click the “Edit” button to open the boot.ini file.
  4. Append the /PAE switch to the last line. For example:

    multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=”Windows Server 2003, Standard” /noexecute=optout /fastdetect /PAE

  5. Save the file and click the “OK” button and then the “OK” button.
  6. Reboot the host is required for this to take effect.

5. Configure the “Lock Pages in Memory” Windows policy for the SQL Server service account you had written down in step 2.

  1. Click the Start menu, click Run. Type in gpedit.msc.
  2. On the “Group Policy” console, expand “Computer Configuration,” and then expand “Windows Settings.”
  3. Expand “Security Settings,” and then expand “Local Policies.”
  4. Expand the “User Rights Assignment” folder.
  5. Double-click the “Lock pages in memory” policy.
  6. Click the “Add User or Group” button to add the SQL Server service account.

6. Configure to enable Address Windowing Extensions (AWE). SQL Server service restart is required for both the “Lock Pages in Memory” Windows policy and the AWE configuration to take effect.

-- Activate AWE
-- http://msdn.microsoft.com/en-us/library/ms190673(v=sql.105).aspx
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO

7. Set the min and max server memory.

-- Configure the min and max server memory
-- http://msdn.microsoft.com/en-us/library/ms190673(v=sql.105).aspx
USE master
go
sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'min server memory', 2048 -- 2 GB min, replace with your desired amount here
EXEC sp_configure 'max server memory', 6144 -- 6 GB min, replace with your desired amount here
RECONFIGURE WITH OVERRIDE

REFERENCES:

Virtual Address Space
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366912(v=vs.85).aspx

Physical Address Extension
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366796(v=vs.85).aspx

Enabling AWE Memory for SQL Server
http://msdn.microsoft.com/en-us/library/ms190673(v=sql.105).aspx

How to: Enable the Lock Pages in Memory Option
http://msdn.microsoft.com/en-us/library/ms190730(v=sql.105).aspx

Memory Supported by the Editions of SQL Server
http://msdn.microsoft.com/en-us/library/ms143685(v=sql.90).aspx

Memory Limits for Windows Releases
http://msdn.microsoft.com/en-us/library/windows/desktop/aa366778(v=vs.85).aspx