Hello Geeks and welcome to the Day 20 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.
Yesterday we have covered sys.dm_os_process_memory. Today I will discuss about sys.dm_os_memory_clerks. There have been few changes in memory architecture from 2008 R2 to 2012. Like all the page allocations including multi page allocations happen in buffer pool. So few of the columns in this DMV are not relevant to 2012 and few added from 2012.
SQL Server Memory Manager has three layers of hierarchy. The bottom layer which interacts with lower levels are memory nodes. In the middle layer we have Memory Clerks, Memory Caches and Memory Pools. On the top are the Memory objects. To check how many memory nodes you have read this blog.
Memory Objects are used to allocate memory in the SQL Server Instance. Memory Clerks are only ones to interact with Memory Nodes to allocate memory. Any object requiring significant memory must create its own memory clerk to allocate memory. Most of these clerks are created when SQL Server is started. All these clerks are shown in sys.dm_os_memory_clerks.
Sys.dm_os_memory_clerks exposes most of the information which is shown in DBCC MEMORYSTATS. There are few attributes of clerks not shown yet. Like Away Committed and Taken Away Committed in Memory Node, small gateway which is used to control memory grants. Every other clerk details are covered in this DMV.
The important columns to be noted in sys.dm_os_memory_clerks are pages_kb (SQL 2012+), virtual memory reserved and committed (committed < reserved), shared memory reserved and committed, page size and page allocator address. Page allocator address maps to the memory object which are bound to this clerk. They can found in sys.dm_memory_objects.
SELECT type, name, pages_kb, virtual_memory_reserved_kb, virtual_memory_committed_kb, awe_allocated_kb, shared_memory_reserved_kb, shared_memory_committed_kb, page_size_in_bytes FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC
For dealing with any issue of Memory sys.dm_os_memory_clerks will be your step 2. Step 1 was explained yesterday to determine if the memory pressure is internal or external to SQL Server. This output will give you information like which clerk is taking up more memory. If any clerk is having abnormal allocation we can proceed with step 3. That I will explain tomorrow. So, stay tuned. Till then
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
