Setting SQL Server memory options

Note the following recommendations for setting SQLServer memory options on the server where you install SQL Server:

  • Do not set the SQL Server maximum memory to less than 1GB.
  • Where only SQL Server is running on the host server, set the SQL Server maximum memory at the system physical memory less at least 2GB for the Windows operating system. For example, if your server has 8GB of memory, set the SQL Server maximum memory to no more than 6GB. This leaves at least 2GB for the operating system.
  • Where the SQL Server is sharing the host server with other server processes, including Internet Information Services (IIS) and ION services, set the SQL Server maximum memory to no more than half the physical memory on the server. For example, if your server has 8GB of memory, set the SQL Server memory to no more than 4GB. This leaves at least 4GB for the operating system and all other server processes.

In addition to setting the maximum memory option, consult with your site administrator to determine whether or not to enable the Lock pages in memory permission setting in Windows for the SQL service account on all SQL Server instances.

notice

LOSS of data

Back up or archive any SQL Server database data before adjusting any database memory options.

Failure to follow these instructions can result in irreversible database changes.

Only personnel with advanced knowledge of SQL Server databases should make database parameter changes.

 

To set the maximum memory setting for your SQL Server:

  1. Start SQL Server Management Studio and log in to your SQL Server instance.
  2. Right-click the SQL Server name and click Properties in the menu to open the Server Properties dialog.
  3. Select Memory in the left pane and adjust the value in the Maximum server memory field.
  4. Click OK to apply the changes and close the dialog.
  5. Close SQL Server Management Studio.

    A server reboot or a restart of the SQL Server service is not required.

To enable the lock pages in memory option:

  1. Click Start > Run and type gpedit.msc in the Run dialog to open the Local Group Policy Editor.
  2. In the left pane, navigate to Computer Configuration > Windows Settings > Security Settings > Local Policies.
  3. Expand Local Policies and select User Rights Assignment.

    The policies are listed in the right pane.

  4. Locate Lock pages in memory in the list and then double click the policy name to open the Lock pages in memory Properties dialog.
  5. Click Add User or Group on the Local Security Setting tab.
  6. Add an account with the privileges to run sqlserver.exe and then click OK to close the dialog.
  7. Click OK to close the Local Group Policy Editor.

    A server reboot or a restart of the SQL Server service is not required.