Very large execution plan

Question

Very large execution plan

Gail Shaw

SSC Guru

Points: 1004504
More actions
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

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

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

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]

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

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 ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

I'd use notepad to be honest

Then within the <Statements> element comment out using '' 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]

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 5

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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

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]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 10

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]