In this blog post, we will discuss the memory configuration options and memory utilization patterns in SQL Server. Many people often worry about the memory utilization on a server where SQL Server is installed, and may even panic when they see alerts about memory thresholds being exceeded. This post aims to provide clarity on these topics and help readers find answers to their questions.

In SQL Server, the physical memory utilization is controlled by two parameters: min server memory (MB) and max server memory (MB). These parameters only control the memory utilized by the buffer pool (bpool), which is the largest consumer of memory in SQL Server. However, there are other components that also consume memory apart from the bpool.

It is important to define these two parameters, especially in scenarios where SQL Server is running on a server with other applications, multiple instances are installed, or in a cluster environment.

Min Server Memory (MB)

The min server memory setting defines the lower limit of memory available for the buffer pool. When SQL Server starts up, the buffer pool does not immediately acquire the amount of memory specified in min server memory. It starts with the memory required for initialization. As the workload increases, it acquires more memory. Once it has acquired the amount of memory specified in min server memory, the bpool acquires more memory based on availability and the max server memory setting. The bpool never drops the memory below the level specified in min server memory once it has acquired it. The total amount of memory consumed by the bpool depends on the workload. On a SQL instance that is not processing many requests, it may never reach the min server memory limit. By default, this value is set to 0.

Max Server Memory (MB)

The max server memory setting defines the upper limit for the bpool. It will never acquire memory beyond the value specified in max server memory, even if there is more memory available on the server. Once the limit specified in max server memory is reached and there is a memory request from the operating system, the bpool will release memory until it reaches the min server memory setting. The default value for max server memory is 2TB (2147483647).

To better understand these concepts, let’s consider an example. In Fig 1, the green portion (40 GB) and orange portion (20 GB) represent the bpool occupied by INST1, based on the configuration settings of max server memory (60 GB), leaving 4GB for the operating system, other processes, and non-bpool components. Now, let’s say we install another instance on the same server with a configuration setting as shown in Fig 2. In order to satisfy the min server memory setting of INST2 (20 GB), INST1 is forced to release memory above the min server memory setting, leaving only 2GB (orange portion). Now, INST2 satisfies its min server memory configuration, leaving only 42 GB for INST1 and 2GB for the operating system, other processes, and non-bpool components. If the operating system requires more memory for other tasks, it can grab a maximum of 2 GB from INST2. If that is not enough, overall performance degradation of the physical server may be experienced.

Memory Configuration Considerations

Many servers run on the default values for these two parameters, which may not cause harm on a standalone server dedicated to a single instance of SQL Server. However, in the case of multiple instances on the same server, it is important to configure these parameters in all instances to ensure that each instance and the operating system have the minimum memory required to process their workloads.

In our environment, where we run three instances on the same box, a typical setting would be to restrict the sum of min server memory to 27 GB, leaving 5 GB for the operating system and other processes. Max server memory is configured in such a way that the SQL instances can make use of the 5 GB if the operating system does not require it. It is worth noting that SQL Server is efficient in releasing memory if there is memory pressure from the operating system, but only until the min server memory configuration is reached. Configuring memory settings appropriately is even more important in a cluster environment, where instances may need to run on the same node without experiencing memory crunch.

For example, consider a scenario with a two-node active-active cluster, where each node has 64 GB of memory. The SQL instances on these nodes are configured with 50 GB as min server memory and 60 GB as max server memory. If one of the instances fails over to another node, the performance of the instances may be affected depending on the settings. It is crucial to configure these values to ensure smooth operation of multiple instances on the same node without memory issues.

Breakup of Memory Consumed by SQL Server

Various objects in SQL Server consume memory. To get a better understanding of memory utilization, you can use the script This Memory Utilized by various object in SQL server.sql to list the memory consumed by different objects in SQL Server. From the listing, you can easily identify that the bpool is the biggest consumer of memory. If you want to know the amount of bpool memory utilized by each database, you can use the script Bpool utilization by databases.sql. To get more detailed information about the objects in each database that consume bpool space, you can use the script Memory Utilized by objects in db.sql.

We hope this blog post has provided you with a better understanding of memory configuration and utilization in SQL Server. If you have any comments or questions, please feel free to share them.

For more SQL Server tips and tricks, visit our website www.PracticalSqlDba.com.