But Really
Remember way back when maps were on paper? Some maps were so fancy they called themselves an atlas, even though you bought them at a gas station.
They were neat, and they were mostly right. If there was construction, or a road was closed for some reason, there was no way to update them fast enough to be useful.
Same deal with traffic. A route might look like the best way to go no matter what time of day you’re driving, but you wouldn’t know if it was a good idea until you started on your way.
You could hit all sorts of problems along the way, and in the age before cell phones, you wouldn’t be able to tell anyone what was happening.
Sure, you could stop at a pay phone, but everyone peed on those things and they had a bad habit of stealing your coins even if you could even remember the phone number.
I’ve said all that to say this: that’s a lot like SQL Server’s query plans today. Sure, Microsoft is making some improvements here with all the Intelligent Query Processing stuff.
Not much beyond adaptive joins are a runtime change in plan execution, though. A lot of it is stuff the optimizer adjusts between executions, like memory grants.
All Plans Are Estimates
By that I mean, win lose or draw, every execution plan you see is what the optimizer thought was the cheapest way to answer your question.
A whole lot of the time it’s right, even if it’s not perfect. There are some things I wish it were better at, for sure, like OR predicates.
For a long time I thought the costing algorithms should be less biased against random I/O, because it’s no longer a physical disk platter spinning about.
But then I saw how bad Azure storage performs, and I’m okay with leaving it alone.
Every choice in every execution plan is based on some early assumptions:
- You’re on crappy storage
- The data you want isn’t in the buffer pool
- What you’re looking for exists in the data
There are some additional principles documented over here, like independence, uniformity, simple containment containment, and inclusion for the best cardinality estimator.
In the new cardinality estimator, you’ll find things like “correlation” and “base containment”. Humbug.
Plan, Actually
Estimated plans can be found in:
- The plan cache
- Query Store
- Hitting CTRL + L or the “display estimated execution plan” button
- Collecting post compilation events from Profiler or Extended Events
- Somewhere in the middle is lightweight query profiling, which is also a total waste of time
These plans do not give you any details about where SQL Server’s plan choice was right or wrong, good or bad. Sometimes you can figure things out with them. Most of the time you’ll have to ask for an actual execution plan.
When you collect an actual execution plan, SQL Server adds in details about what happened when it executed the plan it estimated to be the cheapest. For a long time, it could still be hard to figure out where exactly you hit an issue.
That changed when Microsoft got an A+ gold star smiley face from their summer intern, who added operator times to query plans.
Actual execution plans can be found in:
- Hitting CTRL + M or the “include actual execution plan” button
- Collecting post execution events from Profiler or Extended Events
These are most useful when query tuning.
Reality Bites
They’re most useful because they tell the truth about what happened. Every roadblock, red light, overturned tractor trailer full of pig iron, and traffic jam that got hit along the way.
Things like:
- Spills
- Incorrect estimates
- CPU time
- Scalar UDF time
- I/O performed
- Wait stats (well, some of them)
- Operator times
You get the idea. It’s a clear picture of what you might need to fix. You might not know how to fix it, but that’s what many of the other posts around here will help you with.
One thing that there’s no “actual” counterpart for are operator costs. They remain estimates. We’ll talk about those tomorrow.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.