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
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:
- It is a parameter to configure and guarantee memory, especially if you host multiple instances on the same server
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB, virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, virtual_address_space_available_kb/1024 AS sql_VAS_available_MB, page_fault_count AS sql_page_fault_count, memory_utilization_percentage AS sql_memory_utilization_percentage, process_physical_memory_low AS sql_process_physical_memory_low, process_virtual_memory_low AS sql_process_virtual_memory_low FROM sys.dm_os_process_memory; |
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
$counters = @("\Memory\Available MBytes", "\Memory\Pages/sec", "\SQLServer:Buffer Manager\Buffer cache hit ratio", "\SQLServer:Buffer Manager\Lazy writes/sec", "\SQLServer:Buffer Manager\Page life expectancy" '\SQLServer:Memory Manager\Total Server Memory (KB)' '\SQLServer:Memory Manager\Target Server Memory (KB)' ) export_counters "$counters $collections = Get-Counter -Counter $counters -SampleInterval 10 -MaxSamples 1 Write-Output $collections foreach ($collection in $collections) {$sampling = $collection.CounterSamples | Select-Object -Property TimeStamp, Path, Cookedvalue $sampling | Format-Table -AutoSize } |
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
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'min server memory', 2048; GO RECONFIGURE; GO sp_configure 'max server memory', 4096; GO RECONFIGURE; GO |
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023