Estimated plans are like giving someone driving directions. You might know an awesome way to get somewhere, but you don’t know that a truck full of beer crashed into a truck full of monkeys. And though there were no fatalities, the street scene is not one that encourages forward progress.
That’s a lot like what happens to the optimizer. Sometimes it comes up with a good route for Tuesday at noon, but now you gotta make the trip Friday at 5:30. Actual plans can help you identify where things backfired in a way that cached and estimated plans usually can’t.
Here’s an example I love using in my training classes. If you saw this in a code review, you might just skip right past it.
SELECT TOP (10) u.DisplayName, p.* FROM dbo.Posts AS p JOIN dbo.Users AS u ON p.OwnerUserId = u.Id WHERE p.ParentId = @ParentId ORDER BY u.Reputation DESC;
But queries like this terrify experienced query tuners:
- Do we really need everything from Posts?
- Do we have a good index on Posts for the where clause and join?
- Is the ParentId column a highly skewed data set?
- Is there an index on Users to avoid needing to Sort by Reputation?
Under the right circumstances, even the simplest queries can backfire. This is a great example of that.
Query plans can spend a lot of time lying to you. It takes a while to develop an eye for those lies, and figuring out where things went wrong in estimated or cached plans. Actual plans will tell you the truth about most things.
Of course, getting cached or estimated plans is often better than not getting anything at all. But imagine someone sending you this and saying it’s really slow. What would you say? What would you ask for next?
All you have are estimates. Seeks and estimates.
You could even get the parameter from the properties, and see if the estimate lines up.
Different types of plans provide different levels of information. Identifying bad plan choices in Estimated plans is certainly more difficult, but not impossible with enough practice.
Understanding those poor choices and fixing them is what we’re here to do, of course.
The more estimated plans you look at, the more suspicious you become of every operator and choice, and realize that operator costs are often not well-aligned with reality.
Many query tuning efforts start with Estimated plans, and end with Actual plans.
Why? Because we get lied to enough.
Thanks for reading!
A Word From Our Sponsors
Following on the GREAT SUCCESS of previous online events, I’m kicking things back off with another offering of my Premium Performance Tuning class, before I retire and remix the material.
To buy tickets, head over here. It’s going to be taking place Friday, October 23rd from 9am-5pm EST.
The normal ticket price is $300, but all September I’m running a 50% off sale with the coupon code “stillonline”.
Every purchase comes with access to my recorded video training for life, too.