SQL SERVER: Top Memory Clerks

Top Memory Clerks.sql

-- https://msdn.microsoft.com/en-us/library/cc293624.aspx?f=255&MSPPError=-2147217396

SELECT TOP(10) [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

/*
Cache Stores
SQL Server’s plan cache is made up of four separate memory areas, called cache stores. There are actually other stores in SQL Server’s memory, which can be seen in the Dynamic Management View (DMV) called sys.dm_os_memory_cache_counters, but there are only four that contain query plans. The names in parentheses below are the values that can be seen in the type column of sys.dm_os_memory_cache_counters:

- Object Plans (CACHESTORE_OBJCP)
Object Plans include plans for stored procedures, functions, and triggers

- SQL Plans (CACHESTORE_SQLCP)
SQL Plans include the plans for adhoc cached plans, autoparameterized plans, and prepared plans.

- Bound Trees (CACHESTORE_PHDR)
Bound Trees are the structures produced by SQL Server’s algebrizer for views, constraints, and defaults.

- Extended Stored Procedures (CACHESTORE_XPROC)
Extended Procs (Xprocs) are predefined system procedures, like sp_executeSql and sp_tracecreate, that are defined using a DLL, not using Transact-SQL statements. The cached structure contains only the function name and the DLL name in which the procedure is implemented.
*/