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.

Related Articles:

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.

min and max server memory configuration

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.

min and max server memory in GUI

I hope you like this article. Please follow us on our facebook page and on Twitter handle to get latest updates.

Read More:

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:

You may also like...

2 Responses

  1. Marcellus says:

    Is it good or bad practice to set the Min/Max to the same value? Does this cause stress on the server itself? I currently have my servers set to use 12GB min/max of a total of 16GB on the server.

    • Ideally if your database server is dedicated to only SQL Server then we should keep MIN & MAX memory same because 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. Major applications like SAP suggests to keep MIN and MAX value same for SQL Server database server.

      But if SQL Server is one of several server applications running on a single computer then you may need to control the amount of memory allocated to SQL Server by using the min server memory and max server memory options. Hope I answer your query. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *