TSQL to list the most used query plans

Published 28 September 07 01:12 AM | SQL Master 
select  TOP 100
objtype,
p.size_in_bytes,
LEFT([sql].[text], 100) as [text]
from sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
ORDER BY usecounts DESC

Comments

# SSQA.net - SqlServer-QA.net said on September 28, 2007 1:41 AM:

select TOP 100 objtype, p.size_in_bytes, LEFT([sql].[text], 100) as [text] from sys.dm_exec_cached_plans

# Other SQL Server Blogs around the Web said on September 28, 2007 2:21 AM:

select TOP 100 objtype, p.size_in_bytes, LEFT([sql].[text], 100) as [text] from sys.dm_exec_cached_plans

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.