SQL SERVER – Get Query Plan Along with Query Text and Execution Count « Journey to SQL Authority with Pinal Dave

Pinal Dave comes through with another useful query.

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.

SQL SERVER – Get Query Plan Along with Query Text and Execution Count « Journey to SQL Authority with Pinal Dave