0

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.

2
  • 1
    Why do you think target server memory should be === total database size? Surely SQL Server uses memory for things other than storing data and index pages? How about execution plans? Jul 25, 2017 at 11:30
  • Thanks @AaronBertrand, will study more on cached plans and other memory objects as discussed by hot2use. Jul 25, 2017 at 13:06

2 Answers 2

4

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.

1
  • Thank you very much for your time in answering my question in detail, I will do more research on this. Jul 25, 2017 at 13:04
0

sys.dm_os_buffer_descripters gives you the Buffer pool usage and not the total memory usage.DO NOT assume the results from this DMV to be the total memory usage by the database engine. There are lots of other things that SQL Server does and uses memory for.

Total will be a sum of Buffer pool and Non-buffer pool usage.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.