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:

Related posts