SQL Server - Plan cache distribution per database

Check what database is using most of plan cache. Every query execution requires execution plan. Execution plans are in most cases then stored in plan cache for reuse. This behavior saves time on repeated work when preparing execution plans (you know the cases when fresh execution plan is needed). Query is useful when investigating memory performance issues such like memory bloat.

WARNING: Query can take some time to complete based on your memory usage.

Script source: https://www.sqlshack.com/searching-the-sql-server-query-plan-cache/

SELECT
	db.name AS [database_name],
	SUM(CAST(cached_plans.size_in_bytes/1024/1024 AS BIGINT)) AS [plan_cache_size_MB],
	COUNT(*) AS number_of_plans
FROM sys.dm_exec_query_stats query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS [query_plan]
	INNER JOIN sys.databases db ON db.database_id = query_plan.dbid
	INNER JOIN sys.dm_exec_cached_plans cached_plans ON cached_plans.plan_handle = query_stats.plan_handle
GROUP BY db.name
ORDER BY [plan_cache_size_MB] DESC

Results

Script results screen:

Results screen

Script results detailed description:

Column nameData typeDescription
database_namesysnameName of database, unique within an instance of SQL Server or within a Azure SQL Database server.
plan_cache_size_MBintNumber of megabytes consumed by the cache object.
number_of_plansbigintNumber of plans stored in plan cache for given database,

Related posts

Leave a Reply

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