In The Dark
All query plans are based on estimates. The best we can hope for is that the estimates were good enough to get a good plan.
When you get an “actual plan”, all of the estimated metrics remain, but you also get the runtime metrics. Often query tuning is checking that those things lined up reasonably well.
As of now, there’s nothing that updates cached plan estimates after execution; there’s no runtime information added to them. Also notable is are the estimated metrics that don’t have “actual” counterparts, like operator costs.
Live Nude Plans
Microsoft has been adding some cool stuff to query plans, but actual plans are the recipients of most of it.
- Wait stats
- I/O Stats
- Memory Grants
- Spills
- Thread usage
- Operator Times
- Statistics used
- Row goals
What a list! You can do a whole lot of figuring out with a list like that accompanying such pretty drawings of what your queries did. And yet, we often need to get metrics about queries from other sources.
Jingling
SQL Server will keep lots of metrics about cached plans around to help you figure out where queries fall on the pain-causing scale. But the plan cache is an unreliable place, at times.
The plan cache can get wiped out for many reasons, like memory pressure, settings changes, running naughty DBCC commands, and of course restarting SQL Server.
The plan cache can also be difficult to interpret, because of recompilations and compilations. Recompilations kind of cover up a query’s trail, and compilations mean you could have the same query plan over and over again, but it’s not getting reused.
Both of those situations can make getting a clear picture of what a query has been doing over time.
Enter the Query Store. Though not a replacement for the plan cache, Query Store can be a quite helpful tool for finding performance issues. Especially on SQL Server 2017+ where high-level wait stats are also logged. Being able to tie queries to waits is pretty awesome.
Rubber, Glue, Flaming Pants
Another problem with execution plans is how they can lie to you, both by obfuscation and omission.
With all the detail involved in an execution plan, there are still some key data points missing from the estimated versions. That’s why we need stuff like DMVs and Query Store to act as additional sources of truth for what a query has been up to.
And even with all that, understanding query performance issues can still be tough.
Let’s spend some time learning how to figure out where your queries, and query plans, are going wrong.
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.