Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Just a thought but you could schedule sp_whoisactive to run around time the job is running with @get_plans=1. It should grab the plan of any active query running at the time, just log it to a table. sqlblog.com/blogs/adam_machanic/archive/2012/03/22/… –  Shawn Melton Sep 28 '13 at 1:53

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer
    
This won't run as our database is set to compatibility 80 (SQL Server 2000). Is there a way to work in SQL Server 2000? Our server is actually 2005. And another thing is how can I view the batch_query_plan in graphic? –  thotwielder Sep 27 '13 at 11:31
    
How would I know that your database is is in 80 compatiblity mode as you did not mentioned in your question ? –  Kin Sep 27 '13 at 11:35
    
Sorry because I didn't know it matters until I run your script. When I changed it to 90 (in test database), it run no problem. –  thotwielder Sep 27 '13 at 13:01
    
@thotwielder You can change the database to be master and rerun the query that I gave. It should work. The query fails as you are running the query on a database with 80 compatiblity mode. The server is SQL 2005, so it should be fine. –  Kin Sep 27 '13 at 17:21
    
Yes, the query runs on master. Thanks a lot. But I get nothing from the query for the proc I am interested. Probably because other things like the cache being flushed by later quires (I am just guessing here)? Strangely there are only 3 rows in sys.dm_exec_query_stats deqs table. I will probably need to turn on execution plan event in the profiler for the proc. –  thotwielder Sep 30 '13 at 8:37

@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.

share|improve this answer
    
Its a good idea, but the OP does not want to rerun the workload. We also don't know if OP had profiler running during the workoad. But agreed that if the plan is evicted then Profiler will be a huge help. –  Kin Sep 26 '13 at 23:14
    
No I can't rerun the procedure. If I could I could already got the execution plan by executing the proc with Include actual execution plan. –  thotwielder Sep 27 '13 at 11:32
    
So you can't rerun a new overnight trace? –  swasheck Sep 27 '13 at 13:06
    
I don't quite understand. If I start a new trace, there will be Showplan XML. But the original trace has no this event. And I don't quite understand this replay thing. Is this supposed to rerun all the statements captured in the trace? If so, then I can't do it on production server. –  thotwielder Sep 27 '13 at 14:19
    
@thotweiler well you'd have to run a whole new trace and capture it again –  swasheck Sep 27 '13 at 18:03

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.

share|improve this answer
    
Thanks. Yes, that's what I was thinking, but I was afraid the execution plan event will affect the production server. Now it seems I have no other choices. –  thotwielder Sep 30 '13 at 8:45
    
To reduce the impact of performance just filter your events by that proc name. If I have knowledge or access to business logic behind this procedure, I would also try to know what workflow will trigger this procedure and how likely that workflow is. So I will have best educated guess on how long I should put the trace on for. If frequency is high enough that profiler for only 15 minutes can give you what you need. Again you are filtering by exact procedure name so this combination of what you are tracing and how long is the main factors and we are trying to reduce as much as possible. –  Anup Shah Sep 30 '13 at 14:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.