One of the coolest things in SQL Server 2016 is Live Query Statistics (LQS). But did you know that it’s available in SQL 2014? (Edit: Needs SP1)
The thing is that we haven’t been able to view it effectively before now, before SQL Server Management Studio 2016 became available.
LQS provides the ability to watch an execution plan while the query is still running.
In this image above, you can see that I have a fairly long-running query, and I’ve taken a screen shot while the query is running. I can tell it’s still running because of the dotted lines. At this point, the Hash Match has asked the Index Scan for all the rows it’s going to, and the Index Scan has been marked as 100% done. The Hash Match is now asking the Merge Join for rows. The Merge Join is pulling data out of the Sort, which has already pulled in the rows it needed, and so on.
It’s cool stuff.
And significantly, this is against SQL Server 2014 (SP1). I had to use SSMS 2016, but then the button appeared at the top to include Live Query Statistics…
…and then I could see them. When I connected to an earlier version, such as SQL Server 2012, the box was disabled and I couldn’t see them.
So why not install SSMS 2016 (it’s only the client tool – I’m not suggesting you put it on a server), and see how some of your queries look?
You shouldn’t do this against a production machine, because it takes some effort for SQL Server to produce the data used for this. But see how you go. It’s definitely a useful performance-tuning feature which is available right now.
This Post Has 9 Comments
I tried using SSMS CTP 2.2 but it doesn’t work. Is there a newer version out there perhaps?
What kind of "doesn’t work"?
Ah – make sure you have SP1 of SQL 2014. I’ve heard people who haven’t patched their SQL 2014 boxes don’t have this available… 😉
I can confirm updating SQL 2014 to the latest SP indeed does the trick 😀
Nice 🙂
Can we install just the SSMS 2016 CTP 2.3 bits ONLY?
SQL_SSMS.MSI in the x64 setup folder?
Or use the whole installer and choose only Management tools?
You can download just SSMS 2016. I don’t have SQL 2016 on my main machine, just SSMS.
Matan Yungman wrote a little utility last year for SQL Server 2014 and may be useful for those people who do not have SSMS 2016. Does the same kind of thing!
http://www.madeiradata.com/track-my-query/
Nice. I hadn’t seen that before.
I did some stuff with it this week on shorter-running queries – http://sqlblog.com/blogs/rob_farley/archive/2015/09/01/sql-2014-queries-under-a-strobe-light.aspx