Sql-server – SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors

memorypagingperfmonsql serversql-server-2012

I am having an issue in my SQL Server like high memory paging rate and more compilation/sec which lead me to a different observation in memory, so first i checked what is total size of memory of system and what are the threshold levels set in SQL server i.e. min server memory 4096 MB and max server memory 51200 MB, i checked few performance counters total server memory is 64 GB, target server memory is 53 GB and other performance counters as shown below images

enter image description here

When i checked sys.dm_os_buffer_descripters in SQL Server there are very less dirty pages, but when i check total size of memory occupied by all databases is 39 GB as shown in below images

enter image description here

My questions is why and how 14 GB of memory is additionally being occupied by SQL Server and the same i can in resource monitor or performance counter like below image.

enter image description here

Can any one tell me did i miss some thing to check, Total databases are occupying only 39 GB but if i check perfmon or resource monitor it shows that SQL Server is occupying total 53 GB, how 14 GB can be used internally.

Best Answer

sys.dm_os_buffer_descriptors

From the official documentation on sys.dm_os_buffer_descriptors (Transact-SQL):

sys.dm_os_buffer_descriptors returns pages that are being used by the Resource database. sys.dm_os_buffer_descriptors does not return information about free or stolen pages, or about pages that had errors when they were read.

You are looking at the data cache usage. But there are more objects in memory than just the data.

sys.dm_exec_cached_plans

You have to consider the plan cache (sys.dm_exec_cached_plans (Transact-SQL)) and other objects.

Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Memory acquired by cached plans can be retrieved with the following script:

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,   
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes   
FROM sys.dm_exec_cached_plans AS ecp   
JOIN sys.dm_os_memory_objects AS omo   
    ON ecp.memory_object_address = omo.memory_object_address   
    OR ecp.memory_object_address = omo.parent_address  
WHERE cacheobjtype = 'Compiled Plan';  
GO

sys.dm_os_memory_clerks

But instead of looking at individual caches, you might want to consider querying the view: sys.dm_os_memory_clerks

The SQL Server memory manager consists of a three-layer hierarchy. At the bottom of the hierarchy are memory nodes. The middle level consists of memory clerks, memory caches, and memory pools. The top layer consists of memory objects. These objects are generally used to allocate memory in an instance of SQL Server.

sys.dm_os_memory_objects

... SQL Server components use memory objects instead of memory clerks. Memory objects use the page allocator interface of the memory clerk to allocate pages. Memory objects do not use virtual or shared memory interfaces. Depending on the allocation patterns, components can create different types of memory objects to allocate regions of arbitrary size.

Use the following query to see what sys.dm_os_memory_objects (Transact-SQL) are using in RAM.

SELECT SUM (pages_in_bytes) as 'Bytes Used', type   
FROM sys.dm_os_memory_objects  
GROUP BY type   
ORDER BY 'Bytes Used' DESC;  
GO

Summary

So while sys.dm_os_buffer_descriptors does provide an overview of the data cahce, you will have to query other sys views to determine the actual current memory usage.

Your SQL Server instance may at one time have occupied 53 GB of RAM, but it is now only currently consuming 39 GB of RAM (in data cache) and more GB in plan cache and memory objects, which may sum up to 53 GB RAM, but which may also sum up to less because of free pages in RAM, that may not yet have been released by SQL Server.

Related Question