Live Query Statistics in SQL 2014, not just SQL 2016

August 31, 2015

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

This Post Has 9 Comments

  1. Koen Verbeeck

    I tried using SSMS CTP 2.2 but it doesn’t work. Is there a newer version out there perhaps?

  2. Rob Farley

    What kind of "doesn’t work"?

  3. Rob Farley

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

  4. Koen Verbeeck

    I can confirm updating SQL 2014 to the latest SP indeed does the trick 😀

  5. Damian

    Nice 🙂

  6. George

    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?

  7. Rob Farley

    You can download just SSMS 2016. I don’t have SQL 2016 on my main machine, just SSMS.

  8. Mark Sinkinson

    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/

Leave a Reply

LobsterPot Blogs

Blog posts by Rob Farley and other LobsterPot Solutions team members.

Search