Memory clerks

 To view the total server memory use the following query against the sys.dm_os_sys_memory DMV: 

 

SELECT total_physical_memory_kb / 1024 AS MemoryMb  

FROM sys.dm_os_sys_memory 

  

 

To view SQLs allocation we can query the sys.configurations table to see how SQL has been configured: 

 

SELECT name, value_in_use FROM sys.configurations  

WHERE name LIKE 'max server memory%' 

 

A common misconception is that the maximum server memory setting applies to all of SQL Server, it doesn’t and its quite common to see, at the server level, SQL Server using more memory than this setting allows. The reason for this is that the configuration item only applies to the SQL Buffer Pool and various other components within SQL can consume more memory but it must be said that the Buffer Pool is mainly the biggest item of SQL memory allocation. 

 

To see how SQL is using memory internally we can query the sys.dm_os_memory_clerks DMV to view currently active memory clerks within SQL Server. A memory clerk sits between memory nodes and the memory components within SQL Server. Each component has its own memory clerk that interfaces with the memory nodes to allocate memory; these clerks can then be used to track resource consumption. This architecture also means that threads cannot directly interface with the low level memory allocators but must go to the clerks for memory requests. 

 

SELECT TOP(5) [type] AS [ClerkType], 

SUM(pages_kb) / 1024 AS [SizeMb] 

FROM sys.dm_os_memory_clerks WITH (NOLOCK) 

GROUP BY [type] 

ORDER BY SUM(pages_kb) DESC 

 

 

 The CACHESTORE_OBJCP allocation refers to plans for stored procedures and functions. The CACHESTORE_SQLCP are plans not within those object types and refer to statements executed directly against SQL Server whilst the CACHESTORE_PHDR row shows algebrized trees for various objects. 

 

  

SELECT TOP 5 DB_NAME(database_id) AS [Database Name], 

COUNT(*) * 8/1024.0 AS [Cached Size (MB)] 

FROM sys.dm_os_buffer_descriptors WITH (NOLOCK) 

GROUP BY DB_NAME(database_id) 

ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);