Tell me more ×
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.
up vote 3 down vote favorite

I have a large nasty stored procedure in a 13gig database that takes 45 minutes to run when I include the actual execution plan. I save the query plan from SSMS and open in up in SQL Sentry Plan Explorer and I don't see a duration for any of the queries:

I've searched through the entire list of queries and resorted it, the entire column is empty. I know some queries in this particular stored procedure take a long time to run. While the stored procedure is large, it not a stored proc with millions of microsecond queries, and I know for a fact that certain queries take minutes to run.

share|improve this question
3  
Do the durations show in the underlying XML? – Randolph West Jan 2 at 21:03
1  
@RandolphWest - Nope, this isn't in the XML so no way of plan explorer knowing this. – Martin Smith Jan 2 at 21:38
2  
@Justin if you collect the execution plans through the "Get Actual Plan" in the toolbar I believe it collects durations. – Martin Smith Jan 2 at 21:41

1 Answer

up vote 6 down vote accepted

Martin is correct, in order to see duration and other metrics from an actual plan with Plan Explorer, "Get Actual Plan" must be run from within Plan Explorer. Actual plan XML does not contain this data, regardless of source.

Plan Explorer FREE uses STATISTICS TIME & IO behind the scenes and auto-correlates the data with the plan tree, and Plan Explorer PRO uses a dynamic rowset trace for this purpose. An advantage of the latter method is the ability to provide the full call stack including plans and metrics for embedded dynamic SQL calls. Neither method is affected by statement duration.

This is why we use our own .queryanalysis file format when saving plans from within Plan Explorer, as it contains this extra metadata along with the plan XML.

share|improve this answer

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.