Archive

You are currently browsing the Log et al – Peter Curd blog archives for the day Thursday, April 1st, 2010.

Apr

1

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!
Read more »