Quite often, we need to know how many any particular objects have been executed on our server and what their execution plan is. I use the following handy script, which I use when I need to know the details regarding how many times any query has ran on my server along with its execution plan. You can add an additional WHERE condition if you want to learn about any specific object.
SELECT cp.objtype AS ObjectType,
OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
cp.usecounts AS ExecutionCount,
st.TEXT AS QueryText,
qp.query_plan AS QueryPlan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
--WHERE OBJECT_NAME(st.objectid,st.dbid) = 'YourObjectName'
In result set, you will find the last column QueryPlan with XML text in it. Click on it, and it will open a new tab displaying the graphical execution plan.
You can also save the newly opened tab in XML format with the extension .sqlplan. When this new file is opened in SQL Server Management Studio, it will automatically display the graphical representation of the XML Plan.
It is a quite simple script, but it works all the time.
Reference : Pinal Dave (http://blog.sqlauthority.com)
pinaldave
Hello sir,
very useful and nice feature.
what is the command to clear all cache plans ?
so we can get last query plan.
I think this gonna work…
DBCC FREEPROCCACHE
Thanks Ashish.
Hi Pinal
When I executed the query you mentioned above on SQL Server 2005, it throws an error saying…
“Msg 174, Level 15, State 1, Line 2
The object_name function requires 1 argument(s).”
Does it work on SQL Server 2008?
Thanks
Sy
Hi Pinal,
When I executed this is the error msg i got for a particular object.
Msg 6335, Level 16, State 101, Line 1
XML datatype instance has too many levels of nested nodes. Maximum allowed depth is 128 levels.
Pls advise.
Thanks,
Gautham
@Gautham: In SQL Server XML data type accepts only up to 128 levels of nodes nesting. Probably there is a plan that is too complicated to be stores in XML data type :-)
@Sy: What build of SQL Server 2005 do you have? It works for me on 9.00.4207.00. Also, check if your database is in compatibility level 90.