In this post I would like to explain memory configuration option and memory utilization pattern in SQL server.I have seen many people worrying about the memory utilization (in task bar or through other monitoring tool) on a box where SQL server is installed. I have also seen people becoming panic after seeing the alert
The threshold for the Memory\% Committed Bytes In Use performance counter has been exceeded. The value that exceeded the threshold is: 90.5850397745768
from SCOM (System Center Operations Manager) .I hope this post will help them to find an answer.
In SQL server the Physical memory utilization is controlled by following two parameters available through sp_configure
- min server memory (MB)
- max server memory (MB)
These two parameters control only the memory utilized by the buffer pool(bpool). In SQL server bpool is the biggest consumer of the memory.There are other component which consume memory apart from bpool.
Below is the list major component which use memory apart from bpool.
- SQL Mail
- COM/OLE components loaded in SQL Server
- Prepared document using sp_xml_preparedocument
- Linked Server
- Backup/Restore
- SQL CLR
It is very important to define these two parameter especially in the case like SQL server is running on a box where other applications are also running,multiple instances are installed on the same machine,installation over cluster environment,etc
min server memory (MB): The min server memory setting define the lower limit of the memory available for buffer pool. On start up of SQL server, the buffer pool does not immediately acquire the amount of memory specified in min server memory. It starts with memory required to initialize. As the workload increase, it keeps acquiring memory.Once it acquired the amount of memory mentioned the min server memory configuration, bpool acquire more memory depends on the memory availability on the server and max server memory settings.bpool never drops the memory below the level specified in the min server memory once it acquired. The total amount of memory consumed by the bpool is completely depends on the workload. On a SQL instance that is not processing many request may never reach min server memory limit.By default this value set to 0.
max server memory(MB): The max server memory define the upper limit for the bpool. It will never acquire the memory more than value specified in the max sever memory setting even if there is lot memory available on the server.Once it is reached the limit specified and if there is memory request from OS, bpool will keep releasing memory till it reaches the min server memory. The default this value for this is 2147483647 (2TB).
To understand it in much better way, look at the Fig 1 where the green(40 GB) and orange(20 GB) portion are occupied by bpool of INST1 as per the configuration settings of max server memory (60 GB) by leaving 4GB for OS ,other processes and non bpool components. Assume that we have installed one more instance on the same server with configuration setting as mentioned in the Fig 2.Now to satisfy the min server memory setting of INST2 (20 GB) ,INST1 is forced to release the memory which was above min memory setting by keeping only 2GB(orange portion). Now INST2 satisfied it min server memory configuration by leaving only 42 GB for INST1 and 2GB for OS,other processes and non bpool component.In later point of time if OS required more memory to perform some action , it can grab maximum of 2 GB from the INST2.If that is not enough for OS other task , you can feel overall degradation of the performance of the physical server.
Memory Configuration Consideration : I have seen many servers which running on the default value for these two parameters. It may not make harm on the stand alone server which dedicated to single instance of SQL server. In the case of multiple instances on the same server, we have to configure these two parameter in all the instances to guarantee that all instances and OS will have minimum memory to process its workload. A typical setting in our environment where we run three instance on the same box is given below.
The sum of min server is restricted to 27 GB by leaving 5 GB for OS and other process.Max server memory is configured in such a way that the , SQL instances can make use of the 5GB if the OS does not requires that.Also note that SQL server is very efficient in releasing the memory if there is a memory pressure from OS but only till the min server memory configuration.
It is more important to configure memory settings appropriately in the cluster environment. Instances might work very smoothly when it running on its own preferred owner node.In case of some issues , if one instance failed over to another node (assuming it is active-active cluster or multiple instances are failed over to the passive node in case of active-passive cluster environment), the performance of the instances might affect depends on the setting. So it is important to configure these value to make sure the multiple instances can run on the same node with out much memory crunch. Think about a scenario of two node active-active cluster and each node has 64GB memory. SQL instances on these nodes are configured with 50 GB as min server memory and 60GB as max server memory. What will happen if one of the instance failed over to another node ? I am sure you will be able to figure out what will happen and how to resolve the issue.
Break up of memory consumed by SQL server: Below are the various objects that consume memory in SQL server
This Memory Utilized by various object in SQL server.sql will list the memory consumed by the various object in SQL server. From the listing you can easily identify that the Bpool is the biggest consumer of the memory. It is interesting to know the amount of bpool memory utilized by each database.The Bpool utilization by databases.sql will give you the details of Bpool memory utilized by each databases.It will be more interesting to know the details of objects in each database that consume bpool space.The Memory Utilized by objects in db.sql will give that statistics.
Hope now you have better idea about the memory configuration and utilization in SQL server. Please feel free to pass your comments.
www.PracticalSqlDba.com
