Starting SQL: Things Your SQL Server Execution Plan Might Warn You About

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.

SQL Server Showplan XML
what was the robot’s name?

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.