SQL: Top most used query plans that are cached by SQL Server for faster query execution

The query below, 1Please ensure you test any script taken from my website on a test/development machine, before running on a production server. returns the top 20 most used query plans that are cached by SQL Server for faster query execution.

For more information regarding the dynamic management view, please see “sys.dm_exec_cached_plans (Transact-SQL)” in Books Online.

SELECT TOP 20 
	CASE cp.objtype 
		WHEN 'Proc' THEN 'Stored procedure'
		WHEN 'Prepared' THEN 'Prepared statement'
		WHEN 'Adhoc' THEN 'Ad hoc query'
		WHEN 'ReplProc' THEN 'Replication-filter-procedure'
		WHEN 'UsrTab' THEN 'User table'
		WHEN 'SysTab' THEN 'System table'
		WHEN 'Check' THEN 'Check constraint'
		ELSE cp.objtype END AS [Type of Object], 
	cp.cacheobjtype [Type of Object in the Cache], 
	cp.size_in_bytes AS [Size of Cache in Bytes], 
	cp.refcounts AS [Referenced By Other Cache Objects Count], 
	cp.usecounts AS [How Many Times Cache Used Count], 
	st.text
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
ORDER BY cp.usecounts DESC

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.