Live Query Statistics in SQL 2014, not just SQL 2016

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.

image

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…

image

…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.

@rob_farley

9 thoughts on “Live Query Statistics in SQL 2014, not just SQL 2016”

  1. 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… 😉

  2. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *