Break Down Complex Execution Plans

I’ve seen this question posted in a lot places and I’ve seen the search come in to the blog: “How do you break down a complex execution plan?”

The short answer; just like eating an elephant, one bite at a time. The longer answer…

First you have to define what you mean by a complex execution plan. For example, I’ve seen 16 page stored procedures that consist of about 50 or more different statements executed in series (and I ‘ve heard of worse). No single statement was very big, but trying to dig through all the statements to identify which may be causing slow execution was a problem. This is the easiest type of complex execution plan to solve. In SQL Server 2005 and 2008, when you turn on a graphical plan for a query with multiple statements, each statement has a seperate execution plan. At the top of the plan is the query definition and a very important statement: Query Cost (relative to batch): x%.

This is telling you just how much the statement in question costs compared to the rest of the statements in the query. This is how you figure out which of the 50 or 500 statements you need to spend your time with. Easy.

 

More difficult is the second kind of query. This one statement contains hundreds of operations (the icons in a graphical execution plan). Now what? First, these queries, just like the small ones, have a cost for each operation. You just have to know how to navigate around the enormous execution plan. You can browse around using the scroll bars. Better still, there’s a little plus sign (+) in the lower right hand corner of the execution plan window.

Clicking on this opens an image of your entire execution plan, all umpty-gazillion operators, that allows you to scroll around the plan in the regular viewer window. You can simply browse until you find the operation that cost the most.

 

The image above is from a query written by one of our development teams. I’m not sure how many operators there are in the query. The query itself has six seperate CTE’s that are joining against a series of tables and recursing back to themselves. We’re working on tuning it. It’s a good example of the kind of query that you’ll need to browse through a lot of operators to find the costly one. In this case, one of the CTE’s results in 2.4 billion (yes, that’s a ‘b’) rows getting cross-joined and then filtered down to 50 rows that ultimately return. We found this by scrolling through the UI where we saw an incredibly fat pipe connecting one of the operaters to a join.

That should be enough to get you started. There’s more, including some tricks you can do with XML, but I think I’ll put it together in an article.