Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execution Plan Incomplete Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 4:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:34 AM
Points: 5, Visits: 95
I'm trying to show a developer how to rewrite a stored procedure. It's a pretty scary SP (hundreds of lines of T-SQL in a cursor loop doing a bunch of selects - all to create a dynamic SQL statement). I know we're all cringing in horror at this point but what I wanted to do was show him just how bad the performance would be and what it would do to SQL Server by walking him through the execution plan. When I include the actual execution plan, the SP that normally runs about 6 - 10 seconds now runs for over a minute (which I presume is the time spent generating the plan). The problem comes from the plan not finishing. I scrolled through this and when I got to the end of the plan screen, it was cut off half way through a query.

The question is - Is there a way to display the entire plan? Any switches or tricks for getting the entire plan to display?

Thanks
Tom
Post #1474325
Posted Tuesday, July 16, 2013 4:19 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 2:21 AM
Points: 803, Visits: 718
Why on Earth would you want to see the execution plan for that horrible procedure? When you have a loop, there will be plan for execution of a statement, and SSMS is likely to choke. (Which is one reason why it taking such long time to run. There is an overhead for creating the XML as well.)

If you want to show the developer how bad the procedeure is, the current behaviour does serve as threating example, but I'm not sure that the developer will get the drift anyway.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1474330
Posted Tuesday, July 16, 2013 4:25 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 43,047, Visits: 36,206
TUellner (7/16/2013)
(which I presume is the time spent generating the plan).


That's the time spend by SSMS rendering the plan, before it runs out of memory (or hits max resultsets) and gives up.
There's no good reason to display a plan of a procedure like that, you can't really demonstrate much from a plan of a cursor, just lots and lots and lots of repetition of the same piece of the plan.

Maybe use performance characteristics to teach this lesson and show him exec plans of something simpler?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1474333
Posted Tuesday, July 16, 2013 4:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:34 AM
Points: 5, Visits: 95
Thanks all. I know it's not useful for anything other than a teaching tool. I was hoping to get to the execution of the dynamic SQL mess at the end just out of savage curiosity.

-Tom
Post #1474335
Posted Wednesday, July 17, 2013 2:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:54 AM
Points: 1,247, Visits: 9,879
TUellner (7/16/2013)
Thanks all. I know it's not useful for anything other than a teaching tool. I was hoping to get to the execution of the dynamic SQL mess at the end just out of savage curiosity.

-Tom


Well that's a lot easier. Just change the exec/sp_executesql at the bottom of the SQL to a print, run the loop without generating plans and copy the SQL at the bottom to run in isolation.
Post #1474429
Posted Wednesday, July 17, 2013 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 25, 2014 11:34 AM
Points: 5, Visits: 95
Thanks Howard. I should have thought of that. Appreciate the help.
Post #1474662
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse