Behold!
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.
31 Comments. Leave new
This page has more animated GIFs than a Geocities page from 1995. That said, this is an exciting feature that keeps SSMS at the top of the client tools across all DBMS platforms.
That is amazing! I think I’ll download and have a play with that now 🙂
It’s not as pretty as the pictures, but you also run the following query from 2014 onwards to get a grid view of plans in action. Again, you need to have set statistics profile on, and it’s only as good as your actual/estimates allow:
SELECT st.text, eqp.physical_operator_name,
eqp.row_count, eqp.estimate_row_count,
100* eqp.row_count /eqp.estimate_row_count AS [PercentComplete]
FROM sys.dm_exec_query_profiles AS eqp
CROSS APPLY sys.dm_exec_sql_text (eqp.sql_handle) AS st
Uh, yeah, I’m kind of a picture guy. 😀
Awesome new gadget! but how much resources will this use more to do this live?
Shaun – sounds like a great thing for you to test when you’ve downloaded the build! Enjoy.
I asked this at a SQL 2014 course when they demonstrated the query I posted above. The feedback was very little effort to capture the info as SQL is kind of doing it anyway, so no reason why you couldn’t stick it on production. But, you might want to test this theory…
Microsoft asked me to clarify – about the overhead on resources, the thing to be concerned about is the overhead of turning on the plumbing, not the polling of the current query status to render the diagram.
…Interesting. This was pitched to me as a way to see query progress for that “why is my query suddenly taking a long time – what is it doing now?” problem. The catch being that you can only see the info if you’ve already turned the plumbing on which incurs an overhead…happy days.
Still useful though
Hm. Try it on a slow Columnstore query for a bad experience. “Waiting for query plan.” … For 2+ minutes. And then an error occurs. And if you press the Stop button during that 2+ minutes it doesn’t cancel the batch. 🙁
Does it work for columnstore ????
ABC – why not give it a shot and find out?
That looks great…. but 20% load! oh my goodness me… downloaded, just need to reinstall windows and upgrade to 8.1 (on 7atm) firstly……
something nice to look at while testing execution plans… but… encourages one to make them slower, just so can see the pretty lines! heh
great article, thanks,
Not impressed with such a functionality. Queries should be lightning fast and that means there would be no time to watch them run in real time.
Can you imagine the programmers making effort to slow them down in order to watch the live animation?
For those who don’t really understand query plans, I would have been happier if a critical path red line was drawn around the steps that define the total execution time. And optimization hints on the steps that take too much time (although if one follows these the database will end up having too many indices).
Savvas – interesting thought. I don’t agree, but I see where you’re coming from.
Don’t want to sound too harsh but I’m kinda fond of fast execution instead of fancy animated icons to accompany you while you wait.
Savvas – you already had that feature. Execution plans have been fast for quite a while. “Harsh” wouldn’t quite be the word I’d use to describe your comment.
Sorry, Savvas, if you are still on here – you seem to have never worked with VLDBs in the millions or tens of millions of rows. There are limits to how fast these things work. I come down on the side of Brent.
Is anyone else getting a:
SQL Server Setup failure.
——————————
SQL Server Setup has encountered the following error:
Incorrect function.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft%20SQL%20Server&EvtSrc=setup.rll&EvtID=50000&EvtType=0x60797DC7%25400xAC9299FD
On the New SQL stand alone installation or add feature to an existing instance ?
You’ve probably got a corrupt download – that’s happened to me in the past. Re-download the files.
This was the issue, I had to download it like 5 times on a 100/30mbs connection *headscratcher?*. Microsoft for the win !
Keep up the good work Brent !
I’ve been trying to get this to work on SQL 2014 and it doesn’t seem to be working, is there a trick I’m missing? I can use the sys.dm_exec_query_profiles view and that shows me data, but the button to include live query statistics is disabled. I can see the query executing in the new “Active Expensive Queries” section of Activity Monitor, but if I right-click and choose “Show Live Execution Plan” I get an error that says “Live Query Statistics unavailable. Please ensure that SSMS is up to date and try again.”
Any idea if this is user error or if they just haven’t implemented this yet in the CTP2 SSMS? I’m running SSMS version 13.0.200.172.
Pam – it works fine for me in the CTP2 SSMS, but make sure you start the live statistics plumbing before the query starts. (Clicking View, Include Live Statistics before running the query should do it.)
When in doubt, apply a service pack 🙂 I was on SQL 2014 RTM. I applied Service Pack 1 and now the Include Live Statistics button is enabled. Might want to update your article to specify SQL 2014 SP1 🙂
Pam – ooo, awesome! Will do!
But didn’t the article state:
… because it relies on DMVs that were only shipped with 2014 SP1.
Yes. What’s the question?
So I have tried running this on the released version of 2016 and I keep receiving this error when I choose live query plan and run the query “An error occurred while executing batch. Error message is: One or more errors occurred.” A coworker has tired it and it seems to be working fine
Tim – unfortunately, desktop support is a little beyond what I can do here in a blog post comment.
I understand I was just wondering if you have seen anything from Microsoft on the bug it appears in in the message tab in SSMS
[…] BrentO Post 1 […]