Note: While I do have business relationships with many vendors, I do not have one with SQL Sentry aside from attending their great annual party at the PASS Summit.
SQL Server allows us to have access to two types of execution plans, estimated and actual. Actual execution plans give us the real row counts and statistics the optimizer gets when executing the query, while the estimated plan displays the estimates the SQL Server optimizer used based on statistics when it generated the plan. The ideal scenario for performance tuning is to have the actual plan, but when you are dealing with a long running query that runs hours, and a busy procedure cache, that can be a real challenge to get.
So yesterday, I was helping a friend tune a couple of queries, one of which was running hours, and one of them was running in minutes. So I got my hands on the estimated execution plans, and I loaded them quickly in SQL Sentry Plan Explorer (which has a free version). I can’t recommend this tool enough—for simple query plans Management Studio is ok, but the second you have any level of complexity, Plan Explorer allows you to quickly break it down and identify where the problems are. As in this case:
Figure 1 The Good Query
Figure 2 The Bad Query
I quickly honed into the fact that the bad query was doing a lazy spool with 6 BILLION rows versus the 229 million in the good query. However, my friend who was looking at Management Studio was asking where I was getting those numbers. This is what the XML from the original plan showed:
<RelOp AvgRowSize=”51″ EstimateCPU=”0.00889634″ EstimateIO=”0.01″ EstimateRebinds=”0″ EstimateRewinds=”139581″ EstimatedExecutionMode=”Row” EstimateRows=”48868″
LogicalOp=”Lazy Spool” NodeId=”55″ Parallel=”true” PhysicalOp=”Table Spool” EstimatedTotalSubtreeCost=”1242.86″>
I made the assumption that Plan Explorer was doing some math on the execution plan, so I contacted my good friend Aaron Betrand (b|t) who works at SQL Sentry and asked him about it. His response was that “Plan Explorer will sometimes adjust the displayed row counts for certain operators where we know execution count is greater than one”. This is a really great use case for getting better data out of estimated execution plans when that is all you have to work with.
