Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSMS Client Statistics feature

UPDATE BELOW! 

I always get a pleasant surprise when I find a new feature that I didn't previously know about in a product that I use often. That happened today when I stumbled across SQL Server Management Studio's Client Execution Statistics feature. A colleague told me that it existed in the forerunner, Query Analyser, as well but I didn't know about it then either. I figured that if I didn't know about there must be a few other people that don't either so I'd make people aware of it here.

Basically it captures statistics on each execution of a SQL batch. Nothing particularly fantastic there you might say but au contraire - how many times have you done something like the following to work out how long it takes to execute a SQL statement:

declare @start datetime

set @start = CURRENT_TIMESTAMP
 

select *
from sales.customer c
inner join sales.salesorderheader oh
on c.customerid = oh.customerid


 
'exec time = ' + convert(varchar, datediff(ms, @start, CURRENT_TIMESTAMP))

 

Quite alot probably. I know I have.

"But Jamie" I hear you say, "What about using SET STATISTICS TIME ON?" Well yeah, that's definately useful but not as good as the Client Statistics feature. Observe.

Here's the output of Client Statistics after 6 seperate executions of the same statement:

How much more user-friendly is that? Here's what I love about this feature:

  1. The most obvious thing is that you can compare multiple executions in the same place. That's invaluable when performance tuning. Imagine for example if you wanted to see the effect of adding an index to a table - its much quicker and easier if using this.
  2. The coloured colored (must remember I'm in America now Smile) up and down arrows give a great at-a-glance indicator of whether the execution is speeding up or slowing down
  3. The results can be copied to Excel for further analysis

 

I love things that make my SQL life easier and this definately falls into that category. Is the news to anyone else or am I in a very small minority of people that didn't know about this? Let me know.

-Jamie

 

UPDATE - 2006-10-14: Chris Webb pointed out that I didn't actually say how you get to use the feature. Well, its tucked away on the SQL Editor toolbar right here:

The fact that is was MDX guru Chris that asked me led me to ponder whether this works for MDX queries also. The bad news is that it does not. If you want to change that, go to my Connect posting here and do something about it. Leave some comments, don't just vote.

-Jamie

 

 

Published Thursday, October 12, 2006 11:17 PM by jamie.thomson
Filed under:

Comments

 

Great SQL Server Management studio freature « Charlie Maitland’s Blog said:

October 13, 2006 6:10 PM
 

ronald said:

I think the 'print' is missing...

print 'exec time = ' + convert(varchar, datediff(ms, @start, CURRENT_TIMESTAMP))

November 28, 2006 8:46 PM
 

Jim Russell said:

Wonderful. I found a query open in SSMS from a day or two ago, with the "Client Statistics" tab, but had no clue how I had gotten it. Your article came up in a Google search to help me figure it out. But so far, I have find nothing about it in BOL. (Questions like what constitutes a trial, how are the trials numbered, etc.)

Do you have a reference?

July 16, 2008 4:13 PM
 

Jim Russell said:

Wonderful. I found a query open in SSMS from a day or two ago, with the "Client Statistics" tab, but had no clue how I had gotten it. Your article came up in a Google search to help me figure it out. But so far, I have find nothing about it in BOL. (Questions like what constitutes a trial, how are the trials numbered, etc.)

Do you have a reference?

July 16, 2008 4:13 PM
 

jamie.thomson said:

Jim,

Unfortunately not. I've never seen any docs on it.

-Jamie

July 17, 2008 9:48 AM
 

How to verify the query time in SSMS? | keyongtech said:

January 22, 2009 1:28 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems