Sql-server – SQL Server 2005 get execution plan of a overnight stored procedure

execution-plansql server

There is a overnight job runs long time. I have used profiler to get the trace and identified a statement (in a stored procedure) that takes most of execution time. Now I need to see the execution plan of the statement so I can see what I can do. But I can't rerun the query since it uses a temp table created in the proc. And the logic of the proc is rather complex and very hard to reproduce (involving getting data from file system and data exchanging with Oracle database via linked server). I am not saying reproducing is not possible but would be rather time consuming. So is there a way I can see the execution plan of the proc or the statement in particular?

Best Answer

So is there a way I can see the execution plan of the proc or the statement in particular?

You have to refer to DMV's for extracting such information. sys.dm_exec_cached_plans, sys.dm_exec_sql_text and sys.dm_exec_query_plan are the ones to look into.

Especially sys.dm_exec_query_plan DMF will return the plan for a given batch or procedure (along with the "subplans" for each query comprising the batch).

Note: Restarting sql server will flush out all dmv data.

Example :

-- Do not run it in PROD !!!!
-- free up the procedure cache for TESTING ONLY !!
dbcc freeproccache

-- create an SP with different queries 
create procedure usp_Kin_Test
as
select name, create_date from sys.objects
where type = 'P'
order by create_date desc

select name, OBJECT_ID from sys.procedures
order by name

-- execute the query
exec usp_Kin_Test

--- Check the DMV's for cached query plan

SELECT deqp.dbid
    ,deqp.objectid
    ,CAST(detqp.query_plan AS XML) AS singleStatementPlan
    ,deqp.query_plan AS batch_query_plan
    ,ROW_NUMBER() OVER (
        ORDER BY Statement_Start_offset
        ) AS query_position
    ,CASE 
        WHEN deqs.statement_start_offset = 0
            AND deqs.statement_end_offset = - 1
            THEN '-- see objectText column--'
        ELSE '-- query --' + CHAR(13) + CHAR(10) + SUBSTRING(execText.TEXT, deqs.statement_start_offset / 2, (
                    (
                        CASE 
                            WHEN deqs.statement_end_offset = - 1
                                THEN DATALENGTH(execText.TEXT)
                            ELSE deqs.statement_end_offset
                            END
                        ) - deqs.statement_start_offset
                    ) / 2)
        END AS queryText
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS detqp
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
WHERE execText.TEXT LIKE '%usp_Kin_Test%' -- CHANGE here for your stored procedure !!

enter image description here

Note : You can do many cool things by digging into Plan cache as described by Jonathan here. Also refer to DMVs for Query Plan Metadata

Related Question