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:
Script results detailed description:
| Column name | Data type | Description |
|---|---|---|
| Memory Clerk Type | nvarchar(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) | float | Size of memory used by given memory clerk type, in megabytes (MB). |