SQL Server Max Memory Settings

When SQL Server is using memory dynamically it queries the system periodically to determine the amount of free physical memory available.
  • 1962

Introduction

SQL Server can change its memory requirements dynamically based on available system resources. The default setting for min server memory is 0 and the default setting for max server memory is 2147483647. The minimum amount of memory you can specify for max server memory is 4 megabytes (MB).

When SQL Server is using memory dynamically it queries the system periodically to determine the amount of free physical memory available. SQL Server grows or shrinks the buffer cache to keep free physical memory between 4 MB and 10 MB depending on server activity. This prevents Microsoft Windows NT® 4.0 or Windows® 2000 from paging. If there is less memory free SQL Server releases memory to Windows NT 4.0 or Windows 2000 that usually goes on the free list. If there is more memory free, SQL Server recommits memory to the buffer cache. SQL Server adds memory to the buffer cache only when its workload requires more memory a server at rest does not grow its buffer cache.

Allowing SQL Server to use memory dynamically is the recommended configuration; however, you can set the memory options manually and override SQL Server's ability to use memory dynamically. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting from the total physical memory the memory required for Windows NT 4.0 or Windows 2000 and any other instances of SQL Server.

Setting the Memory Options Manually

There are two principal methods for setting the SQL Server memory options manually:

  • In the first method, set min server memory and max server memory to the same value. This value corresponds to the fixed amount of memory to allocate to SQL Server.
  • In the second method, set min server memory and max server memory to span a range of memory values. This is useful in situations where system or database administrators want to configure an instance of SQL Server in conjunction with the memory requirements of other applications running on the same computer.

You can use T-SQL to set your Max Server Memory setting. The sample below sets it to 3500, which is the equivalent of 3.5GB. This setting is dynamic in SQL Server 2005/2008, which means that you can change it and it goes into effect immediately, without restarting SQL Server.

– Turn on advanced options
EXEC  sp_configure'Show Advanced Options',1;
GO
RECONFIGURE
;
GO

– Set max server memory = 3500MB for the server
EXEC  sp_configure'max server memory (MB)',3500;
GO
RECONFIGURE
;
GO 
 

-- See what the current values are EXEC sp_configure;

© 2020 DotNetHeaven. All rights reserved.