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?
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_query_plan are the ones to look into.
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.
-- 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 !!
@Kin has a very good answer so I do not intend to supplant his answer, but to supplement. You can also get the plan from Profiler itself. This would be especially helpful if the plan had been evicted from the Plan Cache before you had a chance to analyze it.
In the "New Trace" panel click on the "Events Selection" tab. Then check the "Show all events" and "Show all columns" selection boxes. Expand the "Performance" section and check the "Showplan XML" selection box. Re-run your trace and the plan XML will appear in the "TextData" column of your trace file.
you can always use Server Sid trace for lower impact on user side performance but in your case its nightly job so you can start Profiler and in the event choose showplanXML. you can also choose to extract the XML plan for all queries in single file or in separate file. as you also know what procedure you need to trace, filter the trace events by that procedure name. you can start the profiler and set stop timer on it in case you do not want to seat and watch it running.