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.

SQL 2014 queries under a strobe light

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at: http://blogs.lobsterpot.com.au/2015/09/01/sql-2014-queries-under-a-strobe-light/

Published Tuesday, September 1, 2015 1:12 AM 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

 

mjswart said:

I love this post Rob,

The method reminds me of the methods used to generate this video https://www.youtube.com/watch?v=-fSqFWcb4rE because you're sampling over several executions of a procedure and then assembling them (sortin them) to give a very fine-grained look at the behavior of the query execution.

You've basically taken something that's too quick and slowed it down so that it can be inspected.

You mention this as a tool that can be used to tune every last bit out of a query. But I think more valuable is the insight into internals it gives to DBAs which helps more (even if it is indirect help).

September 1, 2015 9:04 AM
 

Warwick Rudd said:

Nice post Rob, puts a different spin on capturing and identifying what is happening. As you mention LQS at the start of the article and relating it to SQL Server 2014, I have a post that shows as long as you have 2014 SP1, running queries through SSMS 2016 LQS can be used to investigate your queries :) http://www.sqlmastersconsulting.com.au/SQL-Server-Blog/sql-server-live-query-statistics-sql-2014

September 1, 2015 11:10 PM
 

Rob Farley said:

September 1, 2015 11:13 PM
 

Rob Farley said:

Michael: Yes, I agree that what you can learn about queries from this is very useful. This can provide information for tuning, but also a greater understanding about internals in general.

September 1, 2015 11:15 PM
 

Rob Farley said:

Michael - and yes, that Ted talk shows a similar concept at an even faster rate. Sometimes I think academia would be so much fun...

September 1, 2015 11:17 PM
 

Scott R. said:

Rob,

Cool stuff!  Harold Edgerton (MIT prof who invented the strobe light - cool pictures - http://edgerton-digital-collections.org/galleries/iconic) would be proud of you.

Regarding visualizations, your description of the process and the interest in visualized progress made me think of SSIS workflows and observing the progress visually through color coding of SSIS workflow nodes (similar to query execution plan nodes - yes?) and # rows / % completed on active paths in the workflow graph.  I don't know if this is the best model, but it is a model already in place for other SQL Server tools, and that has had some success.  Who knows if this approach and tooling might be adaptable by Microsoft for this new purpose, or lead to even better visualizations?

In SSIS, you are able to observe the progress in real-time (not sure of the sampling interval).  For LQS, real-time may also be interesting, in addition to step by step replay of a captured LQS "trace".

Also very cool to hear that this type of query plan progress tracking is already possible in SQL 2014 as well as the upcoming SQL 2016.

Great work!  Very enlightening.

Scott R.

September 2, 2015 3:58 PM
 

Ajit said:

Awesome ! very good analysis Rob .Yes it helps on understanding the internals .

October 21, 2015 11:10 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

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