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

The query below, 1 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
  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

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.