Extracting a Query Execution Plan from the Procedure Cache

From SQL Server 2005 forward, it has been possible to view the execution plan of a query that has been executed by someone other than yourself. This is extremely useful on procedures that you, as a DBA, would not want to execute in production because of data modification logic, but are used everyday as a part of the application. Finding out how the procedure is running and why it is running so poorly without having to execute it also saves a lot of setup time since many procedures require an extensive set of input parameters that we may or may not know.

First, a little setup. We need to create a test stored procedure that we will be looking for in the procedure cache.

CREATE PROCEDURE KevinsTestProc

AS
BEGIN
  SELECT TOP 1 * FROM sys.objects
END
GO

After we’ve created the stored procedure, execute it a couple times so that SQL Server will cache it. Once it is there, we can start querying various execution related DMV’s that will eventually show us the execution plan.

EXEC KevinsTestProc

GO

Since there can be an enormous amount of plans in the procedure cache, the first thing we need to help narrow down the search is some text that is unique to that procedure, like the procedure name.  By querying the sys.dm_exec_cached_plans DMV and using the sys.dm_exec_sql_text DMF we can get the plan handle will be used to extract the exection plan.

SELECT plan_handle,usecounts, cacheobjtype, objtype, size_in_bytes, text, query_plan

FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE usecounts > 1
AND text LIKE '%KevinsTestProc%'
AND objtype = 'Proc'
ORDER BY usecounts DESC;

Results:

We can see from here that we have the plan handle,  how many times this store procedure has been executed, how large the plan is, the text inside the cached plan, and an XML representation of the Execution Plan generated at compile time.

Now that we have this we can click on the XML hyperlink in the results window to open up the execution plan in SSMS.