SQL Server Memory Allocation & Role of MIN/MAX Server Memory Configuration
SQL Server is memory intensive database system and most resource intensive operations in SQL Server are DISK IO operations. SQL Server uses memory to minimize disk IO operations by creating a buffer pool where it hold pages read from the database. You need to have enough memory to process any transaction in SQL Server. It is not mandatory to allocate specific amount of memory to SQL Server. SQL Server dynamically acquires and frees memory as required. But if you want to allocate some memory to SQL Server, you can do that by changing server level configuration. Today, i am going to discuss SQL Server Memory Architecture and what is the role of min and max memory server configuration in SQL Server.
- SQL Server Error 701 and Error 802: There is Insufficient System Memory to Run this Query
- Top 10 Perfmon Counters to Identify SQL Server Memory Pressure
- Understanding Resource_Semaphore wait type that Indicates Insufficient Memory
What is VAS and How SQL Server allocates Memory
Before going ahead let us first understand virtual address space. The virtual address space for a process is the set of virtual memory addresses that it can use. The maximum virtual address space for 32-bit Windows is 4GB which is 2^32 and for 64-bit Windows is 16 TB which is equal to 2^64. This Virtual Address Space or VAS is divided into two regions. One is used by application/processes known as user mode VAS and another is used by system or shared between all processes known as kernel mode VAS.
In 32 bit system, by default 2 GB is allocated to user mode VAS where SQL Server runs and the remaining 2 GB is allocated to kernel mode VAS that is used by system or other shared processes. We can allocate /3GB to SQL Server by making changes in boot.ini file. Similarly, VAS of 64 bit system is divided by half of the total 16 TB to each mode of VAS. Maximum 8 TB is allocated to user mode and remaining 8 TB to kernel mode.
SQL Server runs in to user mode VAS which further divide into two distinct regions. One is the space occupied by buffer pool that serves as a primary memory allocation source of SQL Server and rest is occupied by external components that reside inside SQL Server process, such as COM objects.
Now the question is, how SQL Server allocates memory? You will get this answer if you keep reading this post.
When SQL Server starts, it computes the size of virtual address space for the buffer pool based on a number of parameters such as amount of physical memory on the system, number of server threads and various startup parameters. SQL Server reserves the computed amount of its process virtual address space for the buffer pool, but it acquires only the required amount of physical memory for the current load.
The instance then continues to acquire memory as needed to support the workload. As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.
Role of MIN and MAX Server Memory Configuration Option
These server memory configuration options are used to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server. MIN and MAX server memory configuration options indicates the upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine. The buffer pool does not immediately acquire the amount of memory specified in min server memory. The buffer pool starts with only memory required to initialize.
As the Database Engine workload increases, it keeps acquiring the memory required to support the workload. The buffer pool does not free any of the acquired memory until it reaches the amount specified in min server memory. Once min server memory is reached, the buffer pool then uses the standard algorithm to acquire and free memory as needed. The only difference is that the buffer pool never drops its memory allocation below the level specified in min server memory, and never acquires more memory than the level specified in max server memory.
If the same value is specified for both min server memory and max server memory, then once the memory allocated to the Database Engine reaches that value, the Database Engine stops dynamically freeing and acquiring memory for the buffer pool.
The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. You don’t need to restart the machine or SQL Server Instance post making these changes. Below script can be used to change or set the value of min and max server memory configuration options.
sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'min server memory', 4000;--Change this value as per your need. GO sp_configure 'max server memory', 5000;--Change this value as per your need. GO RECONFIGURE; GO
See the below screenshot where i have made changes for min and max server memory configurations using sp_configure command. If you are getting Error 15123 while running sp_configure command then i would recommend you to read below article to fix this issue first.
You can also change the values of min & max server memory configuration options using GUI in SQL Server Management Studio (SSMS). Right click on SQL Server Instance and choose properties. Server properties window will appear on your screen, now click on “memory” tab from left side pane as shown in below screenshot. Here you can change the value to min and max server memory as per your need in the right side pane.
- Overview of SQL Server Performance Tuning
- Top Perfmon Counters to Identify SQL Server CPU Bottleneck
- Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
- Disk and Performance Impact of running Online Index Rebuild Operation
- How to Improve Bulk Data Load Performance?
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018