This is new in the preview of SQL Server Management Studio 2016, available for download now. It even works when you’re connected to SQL Server 2014 SP1 – but not 2012, because it relies on DMVs that were only shipped with 2014 SP1.
It’s so cute to watch it working on multiple indexes at once:
And the bigger your plans get, the more fun it is to watch. I’m only showing 600-pixel wide images here though to make your blog reading easier, but here it is working with multiple statements in a batch:
It’s not perfect – if SQL Server believes only a few rows will come back, but its estimates are wrong, then the operator will keep showing 100%. Here’s an example:
The top right operator starts first, and SQL Server only expects 1 row for this clustered index scan. As it turns out, there’s more data – note that the line leaving the scan keeps moving, indicating we’re still getting data.
Interestingly, the clustered index seek immediately underneath it keeps changing its time numbers. It’s as if SQL Server Management Studio is saying, “Okay, we started this now and – wait, no, hang on, not yet, now. Okay now we’re – no, maybe not yet.”
The catch (because you knew there was a catch)
Great news – it even works when connected to a server running SQL Server 2014 SP1, as long as you’re using the CTP version of SQL Server Management Studio 2016!
Bad news – you can’t just view someone else’s live query plan. The live query stats plumbing has to be turned on before the query starts.
Here’s how to start that plumbing – choose any one of the following:
- In SSMS 2016, before you run a query, click Query, Include Live Query Statistics
- In SSMS 2016, go into Activity Monitor, Active Expensive Queries, right-click on the query, and click Live Query Statistics
- In any SSMS, before you run a query, run SET STATISTICS XML ON or SET STATISTICS PROFILE ON
- Start an Extended Events session and enable the query_post_execution_showplan extended event. For example, use sp_BlitzTrace™ with @TraceExecutionPlansAndKillMyPerformance = 1. Danger: this enables live query stats on all sessions, and as you can guess by how Kendra named the parameter, there’s going to be a big performance hit for that on a busy server. (There’s a Connect request about that already.)
I know. You want the capability to get there from something like sp_WhoIsActive, but it’s not there. Look, it can’t be all candy and roses. They have to save something for the next version.