Rajendra Gupta
Type of memory allocations

Min and Max memory configurations in SQL Server Database instances

October 6, 2021 by

This article gives an insight into the configuration of Min and Max memory for SQL Server Database instances.

Introduction

The server performance depends on various factors such as CPU, Memory, and Storage configurations. SQL Server works on top of the operating system and works closely with these resources. Usually, if you RDP to SQL Server, you would probably notice that SQL Server is a top memory consumer even on an idle instance. It is a pretty common question for those unfamiliar with how SQL Server memory management works.

SQL Server memory management is dynamic, and DBA does not require a specific memory configuration for each memory component. SQL Server uses buffer cache to load pages from the disk depending on the workload requirements. It is necessary to minimize the disk I/O requirements. As per Microsoft document, SQL Server works on balancing the following goals:

  • Keep the buffer pool from becoming so big that the entire system is low on memory
  • Minimize physical I/O to the database files by maximizing the size of the buffer pool

By default, SQL Server dynamically acquires memory depending on the server RAM; however, it ensures that it does not create memory shortage for remaining processes.

SQL Server database engine has a background process called Resource monitor. Its task is to monitor the internal and external memory indicators.

These notifications are stored in the ring buffers. The resource monitor resource group uses the signals RESOURCE_MEMPHYSICAL_HIGH, RESOURCE_MEMPHYSICAL_LOW, RESOURCE_MEMPHYSICAL_STEADY, or RESOURCE_MEMVIRTUAL_LOW.

  • RESOURCE_MEM_STEADY: No memory issues were observed
  • RESOURCE_MEMPHYSICAL_HIGH: SQL Server can acquire memory because OS has sufficient free available memory
  • RESOURCE_MEMPHYSICAL_LOW: SQL Server must return the memory as OS is having memory shortage
  • RESOURCE_MEMVIRTUAL_LOW: The virtual address range for the SQL Server process is getting exhausted

Once we start SQL Server, it computes the virtual address space size for the buffer pool. It takes into consideration of physical memory, server threads, startup parameters. SQL Server acquires the required memory for the current workload. Once users start performing their transactions, it gains additional physical memory and continues until OS has a memory shortage. If an application stops and additional memory is available, the SQL Server instance adjusts its memory allocation.

If you open the SQL Server Database instance property and go to the memory section, you find the following configurations.

  • Min Memory: Default value 0 MB
  • Max Memory: Default value 2147483647 MB

SQL Server Database instance

Minimum server memory for SQL Server Database instance

It guarantees a minimum amount of RAM to the SQL Server for the specific instance. During SQL Service startup, it does not allocate whatever memory is specified in the min memory configuration. However, once SQL Server reached that level due to client workload, it cannot free up memory to OS.

  • Note:
    1. It is a parameter to configure and guarantee memory, especially if you host multiple instances on the same server
    2. It is normal to see SQL Server memory usage less than the configured minimum server memory

Maximum Server Memory for SQL Server Database instances

It sets the upper boundary for the memory allowed for use by the buffer pool. It is helpful to reserve sufficient memory for the SQL Server processes, queries. The default value allows SQL Server to use as much as the memory required. It might consume almost all OS memory that might raise a server performance issue.

The buffer cache comprises the following components:

  • Database page cache
  • Lock manager memory
  • Log cache
  • Query workload space
  • Optimizing queries
  • System data structures

You can query DMV sys.dm_os_process_memory for a breakdown of currently allocated memory.

Virtual Memory for SQL Server Database

Let’s say you set the maximum memory for SQL Server, and when you looked at task manager, you see SQL process is taking more memory than the configured maximum memory. Several components such as thread stacks, CLR, extended procedures, distributed queries, automation objects, and non SQL Server DLL take memory apart from the configured max server memory.

  • Note: We should not specify the same memory in both min and max server memory. If you do so, SQL Server stops managing memory dynamically once it reaches the configured value. The buffer pool uses the entire memory

Starting from SQL Server 2012, Microsoft modified memory allocation for more fair and stable memory usage. The following table ( reference MS docs) gives a comparison between before and after SQL 2012 versions.

Type of memory allocations

SQL Server provides multiple parameters such as Page-Life expectancy, Buffer cache hit ratio, Total server memory, Target server memory. You can use the DMV sys.dm_os_performance_counters or PowerShell script to fetch the required counters.

PowerShell script output for SQL Server database

Suppose the Total Server Memory(KB) has reached the Target Server Memory(KB). In that case, SQL Server might allocate more memory than the maximum configured value to satisfy the requirement of multi-page memory requests(more than 8 KB). Once the allocation is performed and work is done, the resource monitor signals the memory consumers to release the additionally allocated memory. It tries to bring the Total Server Memory below the Target Server memory configuration. You can also monitor performance counters Total Server Memory(KB) and Target Server Memory(KB) for analysis.

Max Server Memory general guidelines

You should not assign all your server memory for max SQL memory configuration. The configuration should leave sufficient free space for OS processes and other applications, if any, running on the server. As general guidelines, for a single instance, the memory configuration should be as specified below.

  • Leave a minimum of 1-4 GB for the operating system
  • Max Server memory = (Total Server memory – Memory for OS) – (Stack Size * max worker threads)
    • The stack size for 64 bit x64 bit architecture is 2048 KB and 4096 KB for IA64
    • The max worker thread depends on the CPUs, computer architecture, and versions of SQL Server. The following table (reference Microsoft docs) shows worker threads data

Max Server Memory general guideline

Memory configuration for multiple instances of SQL Server

Sometimes we install multiple SQL Server instances on a server. As we specified earlier, by default, SQL Server does not control maximum memory configuration. Therefore, if you do not configure SQL Server memory configurations, one instance might allocate all of the memory while another instance might be facing a low memory issue. SQL Server does not balance memory automatically between multiple instances. Both instances coordinate with Windows memory notification signals for adjusting their memory. Therefore, you should not leave default configuration for various instances on a server as well.

  • Configure max server memory: You can configure maximum memory for each SQL instance. However, the total memory allocation on both instances should not exceed the total physical memory
    • In this approach, the active instance might consume max server memory immediately. However, if one instance is not running for multiple instances, the other instance might not utilize the remaining free memory

  • Configure min server memory: It is advisable to configure the minimum server memory for multiple instances. The sum of multiple instances minimum memory should be 1-2 GB (for OS) less than total physical memory
    • In this approach, SQL Server can expand the memory for an instance if another instance is idle or down; another instance can benefit from the free memory. However, if an instance has taken up the memory, it may take some time for the instance to release memory

Configure the Min and Max Server memory for SQL Server Database instance

You can utilize SSMS GUI or T-SQL for configuring the min and max server memory. The following SQL script configures 2 GB minimum and 4 GB maximum memory for a SQL Server database instance.

Conclusion

In this article, we explored the min and max memory configurations for the SQL Server database instance. It is a crucial configuration, and now SQL Server 2019 included the recommendation and configuration while installing SQL Server. You should evaluate your SQL Server, OS and other processes memory requirements and configure these values.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views