Unless you’re looking at an actual execution plan, one must never ever ever ever ever believe what one is seeing.
What one is seeing is a series of estimates that may have very little to do with reality. I don’t only mean within the execution plan; I also mean the runtime issues a query may encounter.
With all that in mind, here are some of the many ways a query plan can hide work:
Pants On Fire:
- Low CPU, Long Duration: Scalar Valued Functions, Table Variables, Row Goals
- Low Cost, High CPU: Blocking, Triggers
- Spools: Index, Table
- Parallelism: Exchange Spills, Repartition Streams
- Dynamic SQL and Sub Procs
- Many to Many Merge Joins
- Nested Loops/Lookups High Executions
- Memory Grants/Sort and Hash Spills
- Operator Costs
- Batch Costs
- Branch usage/Startup Expressions
- Cached Temp Tables
Over the next few weeks, I’m going to show you how all of these things can be dreadfully wrong. The links will come alive here over the next few weeks, but you can see the full playlist here.
Thanks for reading!