Post reply

Very large execution plan

  • October 22, 2009 at 10:56 am

    #136630

    I have a fun little problem.

    Someone sent me a 3.8MB execution plan (generated from Profiler). When I try to open that in Management Studio, SSMS churns away for 10 min or so then throws a .net exception - Could not create window handle (or something similar). This is even after a reboot.

    Other than opening the plan in an xml editor and working with the raw XML form of the plan, any suggestions on viewing the thing?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • October 22, 2009 at 12:00 pm

    #1069586

    Is if for one statement ? or a batch ?

    I think you should be able to manually remove out some of the StmtSimple blocks and you should still be able to to view in in SSMS as a sqlpan


    Clear Sky SQL
    My Blog[/url]

  • October 22, 2009 at 12:04 pm

    #1069589

    GilaMonster (10/22/2009)

    I have a fun little problem.

    I thought you only posted answers !!:-)

    Is it a graphical plan ? Could you get the execution plan in text instead ?

  • October 23, 2009 at 3:06 am

    #1069859

    It's for a batch of statements. It's the graphical plan (a .sqlplan file) and I can't get the text plan.

    So open in xml editor and 'edit' into several plan files?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • October 23, 2009 at 3:26 am

    #1069869

    I'd use notepad to be honest

    Then within the <Statements> element comment out using '<!--' and '-->' half of the child nodes.

    Save that to a new file.

    Do the same with the remaining half...

    If it is due to size then hopefully then SSMS will be able to display the plans.

    Obviously there may be a bug in SSMS caused by a particular statement, so keep commenting the file till you find the offender.


    Clear Sky SQL
    My Blog[/url]

  • October 23, 2009 at 3:40 am

    #1069880

    Just given this a go...

    Commenting doesn't seem to work :crazy:

    But deleting multiple batch elements from the BatchSequence element and then saving the file as a .sqlplan and loading in SSMS works fine


    Clear Sky SQL
    My Blog[/url]

  • October 23, 2009 at 3:48 am

    #1069887

    Dave Ballantyne (10/23/2009)

    If it is due to size then hopefully then SSMS will be able to display the plans.

    Obviously there may be a bug in SSMS caused by a particular statement, so keep commenting the file till you find the offender.

    I think it's more size or the sheer number of plans than a problem with a particular one.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • October 23, 2009 at 6:47 am

    #1069969

    Turns out there are around 1500 batches in that plan. 🙁 No wonder SSMS isn't happy. I think I'm just going to end up reading the plan in raw XML until I can find the 5 or so worst batches then haul just those back over to SSMS for analysis.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • October 23, 2009 at 7:08 am

    #1069988

    Or get a statement trace of the same batch executing. Should give you a better idea of which statements to look for....


    Clear Sky SQL
    My Blog[/url]

  • October 23, 2009 at 7:13 am

    #1069992

    Dave Ballantyne (10/23/2009)

    Or get a statement trace of the same batch executing. Should give you a better idea of which statements to look for....

    I can't get anything else, statement trace or text exec plan (client's away) until after I get back from PASS and report is due the week of PASS.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    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

  • October 23, 2009 at 8:11 am

    #1070050

    GilaMonster (10/23/2009)

    Dave Ballantyne (10/23/2009)

    Or get a statement trace of the same batch executing. Should give you a better idea of which statements to look for....

    I can't get anything else, statement trace or text exec plan (client's away) until after I get back from PASS and report is due the week of PASS.

    Good luck with wading through that then...


    Clear Sky SQL
    My Blog[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply