SQL Server Nuts and Bolts: Memory Clerks DMV

Memory clerks provide an interface between components and the memory nodes that can be used to allocate memory and are also used by the memory nodes to track allocation and we can use the Dynamic Management View sys.dm_os_memory_clerks ourselves to see how memory is being used within our SQL instance by running the following query:

SELECT [type],SUM(pages_kb) / 1024 AS MemoryMb

FROM sys.dm_os_memory_clerks

GROUP BY [type]

ORDER BY MemoryMb

The query will return the top clerks based on Mb of memory used in SQL Server. Ideally we'd expect to see MEMORYCLERK_SQLBUFFERPOOL as the largest allocation which is essentially our pages in memory. 

We can also test buffer pool allocation by (on a very suitable test system well away from production) by clearing down our buffer pool using the DBCC DROPCLEANBUFFERS command, this is what the top 5 rows are on my machine from the above query:

MEMORYCLERK_XTP         78

MEMORYCLERK_SOSNODE 43

MEMORYCLERK_SQLSTORENG 27

MEMORYCLERK_SQLCLR 21

MEMORYCLERK_SQLGENERAL 16

Now if I run a couple of queries on my test database such as the following to bring some pages into memory:

SELECT * FROM [WideWorldImporters].[Sales].[Invoices];

SELECT * FROM [WideWorldImporters].[Sales].[Orders];

When I rerun my DMV query I can see that there is now around 100Mb in my buffer pool as a result of my queries being executed.

MEMORYCLERK_SQLBUFFERPOOL 100

MEMORYCLERK_XTP                 78

MEMORYCLERK_SOSNODE         43

MEMORYCLERK_SQLSTORENG         27

MEMORYCLERK_SQLCLR         21

We also need to aware that our maximum server memory configuration setting will restrict the amount of memory available to the SQL Server memory components. 

This is a quick example of how easy it is to use Dynamic Management Views to expose detailed information on what is happening in our SQL Server instance and over the next few posts I'll look at some of memory related features of SQL Server.

Nuts and Bolts picture from iStockPhoto: 29,882 Nuts And Bolts Stock Photos, Pictures & Royalty-Free Images - iStock (istockphoto.com)