THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

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

Published Monday, August 31, 2015 8:07 PM by Rob Farley

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Koen Verbeeck said:

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

August 31, 2015 3:29 PM
 

Rob Farley said:

What kind of "doesn't work"?

August 31, 2015 6:01 PM
 

Rob Farley said:

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... ;)

September 1, 2015 3:48 AM
 

Koen Verbeeck said:

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

September 1, 2015 5:03 AM
 

Damian said:

Nice :)

September 2, 2015 3:27 AM
 

George said:

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?

September 2, 2015 8:26 AM
 

Rob Farley said:

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

September 2, 2015 8:33 AM
 

Mark Sinkinson said:

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/

September 2, 2015 9:15 AM
 

Rob Farley said:

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

September 2, 2015 9:20 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

News? Haven't you read my blog?

My Company


Can't find something?

Contact Me

IM: rob_farley@hotmail.com
Twitter: @rob_farley
Skype: rob_farley
E: rob_farley@hotmail.com

MVP (SQL Server)




Certifications








Adelaide SQL UG

Privacy Statement