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

Mild Best Wishes

---

Microsoft has chosen some odd things to warn us about in query plans. For estimated and cached plans, I totally understand some of the limitations.

Though the DMVs for queries associated with query plans (both in the plan cache and query store) log some additional metrics about memory grants, spills, CPU, duration, reads, and more, none of that additional information ends up as feedback in the plan XML.

I’m not complaining, either. Modifying XML is stupid.

Bellyachin’

---

What could be in the XML is an extension of the current set of warnings, with maybe a bit more in Actual plans.

Query plans could warn if:

• A non-SARGable predicate caused a scan
• A non-SARGable/complex predicate caused a Filter
• A residual predicate did a lot of reads
• Eager Index Spools are over a certain number of rows
• Performance Spools have an unfavorable rebind/rewind ratio
• If modes switch between row and batch
• Why an index wasn’t used
• When estimated and actual rows or executions are way off
• When joins have an OR clause
• When parallel row distributions are uneven
• Louder warnings for when something forces a query to run serially
• Show multiple missing index requests when present
• When operators execute more than once
• When a statistic used has significant modifications
• A roll up of per-operator I/O at the root node

Many of these things could be in both estimated and actual plans, and would really help people understand both why the things they do when writing queries can mess them up, and also when there might be a better way to do things.

A Professional With Standards

---

Is that a lot of stuff? Yes. But think about the warnings we get now, and the last time they were really helpful to you.

If they ever were, I can guarantee that they’ve been misleading more often.

All in all, people need a more informative query plan, and building this sort of instrumentation for end users also gives the robots in Azure, and future Intelligent Query Processing features some nice feedback.

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.