Plan, Actually
SQL Server has started collecting a ton of information about a query when it executes.
Live query stats actually captures operator runtimes. Additionally, the stuff that’s captured in actual query plan XML has seen a lot of development.
SSMS 18 goes a step further and shows you those without ticking the Live Query Plan button.
What am I getting at?
Outside Shot
As a consultant, people sometimes send me query plans. They’re usually estimated, or cached plans.
That’s not bad! You can get a sense of some important things based on them, but there’s a ton of detail in actual plans that makes life easier.
One example is with parameter sniffing: estimated and cached plans look like they did something completely reasonable.
Getting an actual plan is tough, though, especially if it’s a long running query, or the query runs modifications.
Containers Are All The Rage
- What if query plan XML had enough information in it for you to “execute” the query locally without returning any results?
- What if you could press play, fast forward, and rewind on a query plan?
- What if you could try things like using the new or old CE or other hints on the query?
- What if parameters could be masked (but differentiated internally) to test parameter sniffing?
This might be possible with the right information collected, even if some of it is imperfect. In newer versions of SQL Server, even information about statistics is gathered by the plan.
The one missing piece would be index definitions, and perhaps reasons why indexes weren’t used.
With the direction Microsoft is finally going in collecting runtime information about queries, I wouldn’t be surprised if something like this became possible.
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.