November 3, 2009 at 11:39 am
We're still using SQL 2000. I've tried using SSMS 2005 and the graphical execution plans, which works ok up to about 200-300 steps (although it freezes my machine for several minutes to generate it). Beyond that, it's back to QA I go which takes only a couple seconds to generate a plan of several thousand steps.
However, I'm estimating that this particular SP is about 80 thousand steps. QA can't handle the graphical model anymore either.
SET STATISTICS PROFILE ON isn't working out too well either. Grid mode doesn't work because 80,000 grids does me no good and it runs out of resources before it finishes creating those anyways. Output to file leaves me with a huge mess that I need another computer with Excel 2007 to even open (430K rows) and seems like it'll be enormously difficult to do anything with.
Anyone have any tips on how to get such a huge plan generated into something I can work with? (The irony of attempting to generate a plan for an SP with this many steps in order to 'optimize' it does not escape me)
November 3, 2009 at 2:35 pm
So far, the best I've come up with is to run a profile on the SPID and include the SHOWPLANSTAT events with the binary data, save the trace to a file then use this SP to parse the binary data back into a data table. Thanks to Erland Sommarskog for pointing this out on another forum and Umachandar Jayachandran for writing it.
This actually isn't bad, it's farther than I expected to get with this. If anyone else has any further advice I'd love to hear it though.
November 3, 2009 at 2:48 pm
That may be what you're stuck with. I was going to suggest using Profiler to get you the text plan, but you'd still be stuck with pasting that plan into a text editor of some sort to work with.
I would suggest using QA and SET STATISTICS IO ON to get the logical reads per DB object. This, along with the execution plan, should tell you where you want to focus optimization efforts.
November 4, 2009 at 6:47 am
Excellent tip John, thank you. That has already led to me finding one of the many many things that need to be adjusted with this 'process'.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply