Log et al – Peter Curd

An irreverent peek into the inner rumblings of Peter Curd



Find Query Plans on Microsoft SQL Server

By pcurd

Today I was asked by Luke Smith a question about Query Plans in Microsoft SQL Server and how to return the Cached Query Plans to check they are decent.

I began to think about the DMV (Dynamic Management View) sys.dm_exec_cached_plans and whilst that includes some important information, it doesn’t include the plan itself.  So then I turned to another DMV, sys.dm_exec_query_plan passing it the plan_handle from the first DMV.

Finally, to be useful as a diagnostic tool I wanted the query text so I added in a third DMV sys.dm_exec_sql_text again passing the plan_handle. I then sorted by usecounts which does what it says on the tin and limited to those that are not system procedures, are actually plans, as opposed to records from parse trees or extended stored procedures, and have been used more than 10 times.  This returned a lot of data for me so I limited to the top 100, but this could obviously be tuned to a different environment.

Running this in SQL Server Management Studio has the bonus of being able to click on the XML in the query_plan and view the plan graphically which is definitely better than the XML!

Be warned – this may take some time to run!

select top 100 [cached_plans].[usecounts], [cached_plans].[cacheobjtype],
[cached_plans].[objtype], [query_plan].query_plan,
[query_text]. from sys.dm_exec_cached_plans as cached_plans
outer apply sys.dm_exec_query_plan([cached_plans].[plan_handle]) as query_plan
outer apply sys.dm_exec_sql_text([cached_plans].[plan_handle]) as query_text
[query_text]. not like '%sys%'
and [cached_plans].cacheobjtype ='compiled plan'
and [usecounts] > 10
order by [usecounts] desc

This will help you find good query plans:

and bad query plans:

Leave a comment