Query Cached Plan Statistics

We can use the below script to find the Query Cached Plan statistics for all DB's

SELECT UseCount = p.usecounts
	,PlanSize_KB = p.size_in_bytes / 1024
	,CPU_ms = qs.total_worker_time / 1000
	,Duration_ms = qs.total_elapsed_time / 1000
	,ObjectType = p.cacheobjtype + ' (' + p.objtype + ')'
	,DatabaseName = db_name(convert(INT, pa.value))
	,txt.ObjectID
	,qs.total_physical_reads
	,qs.total_logical_writes
	,qs.total_logical_reads
	,qs.last_execution_time
	,StatementText = SUBSTRING(txt.[text], qs.statement_start_offset / 2 + 1, CASE 
			WHEN qs.statement_end_offset = - 1
				THEN LEN(CONVERT(NVARCHAR(max), txt.[text]))
			ELSE qs.statement_end_offset / 2 - qs.statement_start_offset / 2 + 1
			END)
	,QueryPlan = qp.query_plan
FROM sys.dm_exec_query_stats AS qs
INNER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = qs.plan_handle
OUTER APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
OUTER APPLY sys.dm_exec_sql_text(p.plan_handle) AS txt
OUTER APPLY sys.dm_exec_query_plan(p.plan_handle) AS qp
WHERE pa.attribute = 'dbid' --retrieve only the database id from sys.dm_exec_plan_attributes
ORDER BY qs.total_worker_time + qs.total_elapsed_time DESC;

Done!! I hope this helps 😊