on March 17, 2016
I like SQL Server’s new Live Query Statistics feature a lot for testing and tuning large queries. One of my first questions was whether this could replace using actual execution plans, or if it’s useful to use both during testing.
Finding: Both are useful. And both can impact query performance.
Live Query Statistics gives insight into plan processing
I’m loading a bunch of rows into a partitioned heap.
Here’s what the insert looks like in Live Query Statistics when run with parallelism
Note that the progress on the Sort operator remains at 0% done until the Clustered Index Scan and Compute Scalar operators feeding it are 100% complete:
Here’s what that same insert looks like when run at maxdop 1
In this case the operators are ordered differently, and the Sort Operator is able to make progress while the Clustered Index Scan is still running.
This insert runs significantly faster at MAXDOP 1, and Live Query Statistics really helps see why. The single threaded plan doesn’t have to wait — it can stream more through at once.
Live Query Statistics didn’t tell us about the tempdb spill
When the query completes, Live Query Statistics tells us that everything is 100% done. It doesn’t say anything about a tempdb spill.
To see the that this tempdb spill occurred as part of the query, we need to have turned on “Actual Execution Plans”. Then we can see click over to the Execution Plans tab and see this:
]
Hovering over the warning on the sort operator, we can see more details about the tempdb spill:
Observation has a performance cost
Getting plan details isn’t free. The amount of impact depends on what the query is doing, but there’s a stiff overhead to collecting actual execution plans and to watching live query statistics.
These tools are great for reproing problems and testing outside of production, but don’t time query performance while you’re using them– you’ll get too much skew.
REGISTER