Oh look, XML
One of my favorite pieces of SQL Server documentation is the XML schema for query plans.
Whenever I’ve needed to figure something out to query the plan cache, or when I’ve been looking for stuff that might be interesting to pull out of it, this is where I’ve gone.
Especially when plan XML, it helps me rest a bit easier to know I’ve covered all the documented possibilities.
Of course, there’s no distinction in the documentation between what can be in the Estimated plan, and what’s reserved for Actual plans.
Such Document
While that’s inconvenient, you can figure most things out by separating plan elements into two groups:
- Optimization time metrics
- Runtime metrics
Here are the documented warnings available in query plans.
Things like spills and memory usage can only be known at runtime, when the optimizer’s estimates are put into play.
For many other things, the optimizer likely knows about and has to account for the warnings while it’s coming up with a query plan.
Warnings About Warnings
One thing to keep in mind about many of these warnings, whether they’re optimization-time or run-time, is that they’re trigger for things that might not matter, or even necessarily be true.
I’ve written in the past about silly execution plan warnings. I’m not going to re-write all those here.
But I do want you to reinforce a couple points I’ve made over the course of the series:
- Estimated and cached plans miss details that make troubleshooting easier
- Not every metric and warning is a critical datapoint
For example, I’ve seen people focus on small spills in execution plans many times only to have them not be the cause of a performance problem.
Operator times make distinguishing this easier, of course. Prior to that, even actual plans could be misleading and unforgiving.
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.