Determine if adhoc queries are taking up most of the Plan Cache

Below query helps in identifying what type of objects are taking up most of the plan cache.


SELECT objtype AS ‘Cached Object Type’, count(*) AS ‘Number of Plans’,
sum(cast(size_in_bytes AS BIGINT))/1024/1024 AS ‘Plan Cache Size (MB)’,
avg(usecounts) AS ‘Avg Use Count’ FROM sys.dm_exec_cached_plans
GROUP BY objtype

If adhoc queries take up most of the plan cache, it indicates that queries should be parameterized and enclosed within stored procedures for efficient use of plan cache.

Who is consuming most of the Buffer Pool?

Buffer Pool includes data cache and plan cache. Usually, data cache consumes most of the memory in buffer pool.

Data Cache

Every data page that is read from disk is written to data cache before being used.

How much space each database is using in the data cache?

SELECT count(*)*8/1024 AS ‘Cached Size (MB)’,CASE database_id
WHEN 32767 THEN ‘ResourceDb’ ELSE db_name(database_id) END AS ‘Database’ FROM sys.dm_os_buffer_descriptors GROUP BY db_name(database_id) ,database_id ORDER BY ‘Cached Size (MB)’ DESC

Dirty page
A dirty page is a page that has changed in memory since it was loaded from disk and is different from the on-disk page.

How many dirty pages exist in buffer cache for each database?

SELECT db_name(database_id) AS ‘Database’,count(page_id) AS ‘Dirty Pages’
FROM sys.dm_os_buffer_descriptors WHERE is_modified =1
GROUP BY db_name(database_id) ORDER BY count(page_id) DESC

Top 10 consumers of memory

SELECT TOP (10) type, SUM(single_pages_kb) AS [SPA Mem, KB]
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY SUM(single_pages_kb) DESC

CACHESTORE_SQLCP—SQL plans (dynamic or prepared SQL)
CACHESTORE_OBJCP—Object plans (stored procedures, functions, and triggers)

Finding indexes and tables that use the most buffer space

SELECT OBJECT_NAME(p.object_id) AS ‘ObjectName’, p.object_id,
p.index_id, COUNT(*)/128 AS ‘buffer size(MB)’, COUNT(*)
AS ‘buffer_count’
FROM sys.allocation_units AS a
INNER JOIN sys.dm_os_buffer_descriptors AS b
ON a.allocation_unit_id = b.allocation_unit_id
INNER JOIN sys.partitions AS p
ON a.container_id = p.hobt_id
WHERE b.database_id = db_id()
GROUP BY p.object_id, p.index_id
ORDER BY buffer_count DESC