SQL Server - Memory usage by memory clerks

Look under hood and check what clerks are using memory. 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.

Memory nodes provide the interface and the implementation for low-level allocators. Inside SQL Server, only memory clerks have access to memory nodes. Every component that allocates a significant amount of memory must create its own memory clerk and allocate all its memory by using the clerk interfaces.

SELECT TOP (21) 
	COALESCE([type],'Total') AS [Memory Clerk Type], 
	SUM(pages_kb/1024.0) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks
GROUP BY GROUPING SETS((type),())
ORDER BY 2 DESC

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
Memory Clerk Typenvarchar(60)Specifies the type of memory clerk. Every clerk has a specific type, such as CLR Clerks MEMORYCLERK_SQLCLR. Is not nullable.
Memory Usage (MB)floatSize of memory used by given memory clerk type, in megabytes (MB).

Leave a Reply

Your email address will not be published. Required fields are marked *