Memory configuration in SQL server and break up of memory utilized by SQL server
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.
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.
Memory Utilized by various object in SQL server.sql
/*
Author : Nelson John A
Description :List the memory utilized by various objects in SQL server
*/
IF OBJECT_ID('tempdb..#MemoryTable') IS not NULL
drop table #MemoryTable
GO
create table #MemoryTable (SeqNo int,MemoryUsagedesc varchar(1000),MemoryUsage decimal(20,2))
DECLARE @pg_size INT, @Instancename varchar(50)
SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'
SELECT @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name]))) FROM sys.dm_os_performance_counters WHERE counter_name = 'Buffer cache hit ratio'
insert into #MemoryTable
SELECT 1,'Total Server physical memory' ,physical_memory_in_bytes/1048576.0 as [Physical Memory_GB] FROM sys.dm_os_sys_info
insert into #MemoryTable
SELECT 2 ,'BPool Committed' ,(bpool_committed*8)/1024.0 FROM sys.dm_os_sys_info
insert into #MemoryTable
SELECT 3 ,'BPool Commit Target' ,(bpool_commit_target*8)/1024.0 FROM sys.dm_os_sys_info
insert into #MemoryTable
SELECT 4 ,'BPool Visible' ,(bpool_visible*8)/1024.0 FROM sys.dm_os_sys_info
insert into #MemoryTable
SELECT 5,'Connection Memory' ,cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE counter_name = 'Connection Memory (KB)'
insert into #MemoryTable
SELECT 6, 'Lock Memory',cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE counter_name = 'Lock Memory (KB)'
insert into #MemoryTable
SELECT 7,'Memory for dynamic SQL cache' ,cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE counter_name = 'SQL Cache Memory (KB)'
insert into #MemoryTable
SELECT 8,'Memory for Query Optimizer',cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE counter_name = 'Optimizer Memory (KB)'
insert into #MemoryTable
SELECT 9,'memory used for hash, sort and create index operations',cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE counter_name = 'Granted Workspace Memory (KB) '
insert into #MemoryTable
SELECT 10,'memory consumed by cursors',cntr_value/1024.0 FROM sys.dm_os_performance_counters WHERE counter_name = 'Cursor memory usage' and instance_name = '_Total'
insert into #MemoryTable
SELECT 11,'Plan Cache ',(cntr_value*@pg_size)/1048576.0 FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Plan Cache' and counter_name = 'Cache Pages' and instance_name = '_Total'
insert into #MemoryTable
SELECT 12,'buffer pool (includes data, free, and stolen)', (cntr_value*@pg_size)/1048576.0 as Pages_in_MB FROM sys.dm_os_performance_counters WHERE object_name= @Instancename+'Buffer Manager' and counter_name = 'Total pages'
insert into #MemoryTable
SELECT 13,'Buffer Pool Data pages' ,(cntr_value*@pg_size)/1048576.0 FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Database pages'
insert into #MemoryTable
SELECT 14,'Buffer Pool Free pages',(cntr_value*@pg_size)/1048576.0 FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Free pages'
insert into #MemoryTable
SELECT 15,'Buffer Pool Reserved pages ',(cntr_value*@pg_size)/1048576.0 FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Reserved pages'
insert into #MemoryTable
SELECT 16,'Buffer Pool Stolen pages ', (cntr_value*@pg_size)/1048576.0 FROM sys.dm_os_performance_counters WHERE object_name=@Instancename+'Buffer Manager' and counter_name = 'Stolen pages'
select * from #MemoryTable order by SeqNo ;
|
Bpool utilization by databases.sql
/*
Author : Nelson John A
Description :Bpool utilization by databases
*/
With CTE_BP(DatabaseName, BufferSizeInMB )
as
(
SELECT
case when DB_NAME(b.database_id) is null then 'Resource DB' else DB_NAME(b.database_id) end AS database_name
,(cast(COUNT(*) as decimal(20,2)) * 8192.00) / (1024.00 * 1024) AS buffer_count_MB
FROM sys.dm_os_buffer_descriptors AS b
GROUP BY b.database_id
)
select * from CTE_BP order by BufferSizeInMB desc
|
Memory Utilized by objects in db.sql
Use DATABASE_NAME
GO
SELECT
name AS TableName,
IndexName,
IndexTypeDesc,
(cast(COUNT(*) as decimal(20,2)) *8.0)/1024 AS 'cached_page_Size(mb)',
COUNT(*) AS 'cached_pages_count'
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT
Allocation.name,
Allocation.index_id,
Allocation.allocation_unit_id,
Allocation.OBJECT_ID,
ind.name IndexName,
ind.type_desc IndexTypeDesc
FROM
(
SELECT
OBJECT_NAME(p.OBJECT_ID) AS name,
p.index_id ,
au.allocation_unit_id,
p.OBJECT_ID
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT
OBJECT_NAME(p.OBJECT_ID) AS name,
p.index_id,
allocation_unit_id,
p.OBJECT_ID
FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
AND au.type = 2
) AS Allocation
LEFT JOIN sys.indexes ind ON ind.index_id = Allocation.index_id AND ind.OBJECT_ID = Allocation.OBJECT_ID
) AS sysobj ON bd.allocation_unit_id = sysobj.allocation_unit_id
WHERE database_id = DB_ID()
GROUP BY name, index_id, IndexName, IndexTypeDesc
ORDER BY TableName
|