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:
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.
The
coloured colored (must remember I'm in America now
) up and down arrows give a great at-a-glance indicator of whether the execution is speeding up or slowing down
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