3 Tricks with STATISTICS IO and STATISTICS TIME in SQL Server

When  you need to measure how long a query takes and how many resources it uses, STATISTICS TIME and STATISTICS IO are great tools for interactive testing in SQL Server. I use these settings constantly when tuning indexes and query.

Here’s three tricks that come in really handy to up your STATISTICS game.

1. You can turn both STATISTICS IO and STATISTICS TIME on and off with a single line of code

I learned this trick from Michael J. Swart a while back. Most people do this, because it’s what the documentation shows:

SET STATISTICS IO ON;
GO
SET STATISTICS TIME ON;
GO

But you can just do this, and it works perfectly:

SET STATISTICS IO, TIME ON;
GO

The same trick works for turning the settings off.

This shortcut has probably saved me an hour of typing in the last year alone. (I totally made that metric up, but hooray for saved keystrokes.)

2. When writing demo code,  you should remember to turn off the lights

I’m sure he’s not the first person in the universe to do this, but Tim Ford was the first person I noticed who did this consistently in his code samples:

SET STATISTICS IO, TIME ON;
GO

/* Query you want to measure goes here*/
SELECT name
FROM sys.databases;

SET STATISTICS IO, TIME OFF;
GO

This is worth doing, because having output spewing out to the Messages tab when you don’t want to look at it can be distracting, and might slow some queries down.

Yep, this is more keystrokes creeping back in. Look, we already saved up a bunch so we’ve got keystrokes to spare.

When STATISTICS IO gets opinionated

When STATISTICS IO gets opinionated

3. You can make someone else format your output

Want to make your output pretty and keep it for yourself offline? Vicky Harp built an Excel parser for STATISTICS IO output.

Want to format your output online in a web browser? Richie Rump built StatisticsParser.com.

Thanks to Michael, Tim, Vicky, and Richie for making query tuning easier and more effective!

Previous Post
Truncate Table with Partitions Fails if You Have Non-Aligned Indexes
Next Post
NO_PLAN and NO_INDEX: Breaking a Forced Query Store Plan

Related Posts

7 Comments. Leave new

Pedro Bonilla
June 5, 2016 9:11 am

Short & sweet! I really like these small tips. Thanks Kendra!

Reply

Any way to eliminate all the extra lines in SET STATISTICS TIME? – It can give up to 4 rows per statement..
SQL Server parse and compile time – I just want a simple elapsed time for statement..:

CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

(269 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

https://msdn.microsoft.com/en-us/library/ms190287.aspx?f=255&MSPPError=-2147217396

Reply

    You can eliminate the parse and compile time by letting it use a cached plan– so running it twice, as long as it doesn’t have a recompile hint, would do that.

    If you’re running a procedure I believe you’ll always get statement level results as well as a procedure level results, though.

    One option is to use a formatter like http://statisticsparser.com/ to simplify the results.

    Another option is to avoid statistics time altogether. If you want to do it programmatically, running an extended events trace and querying the results can be efficient for many load testing scenarios when collecting it with statistics time would be too cumbersome.

    Reply

Using the print function to wrap the output is also a helpful trick, especially when you have a large output. My base template looks like this —

set statistics io on; set statistics time on; set nocount on; –set statistics xml on;
go

print’— begin ———————————————————————-‘
select *
from mytable
print’— end ————————————————————————‘
end
go

set statistics io off; set statistics time off; set nocount off; –set statistics xml off;
go

Reply

Whoops —

set statistics io on; set statistics time on; set nocount on; –set statistics xml on;
go

begin
print’— begin ———————————————————————-‘
select *
from mytable;
print’— end ————————————————————————‘
end
go

set statistics io off; set statistics time off; set nocount off; –set statistics xml off;
go

Reply

I couldn’t remember the name of the objects to turn on and off and a google search brought me here. That is very handy because even though I have been programming for a while I am just not getting a taste of having some data access responsibilities. Thanks for sharing. I am now a follower of your blog. Thanks.

Reply

Since the output of statistics io is so unpleasant to view I have this little tool for everyone to benefit from that runs right inside of SSMS https://analyticsbar.com/blog/statistics-reporter-ssms-extension/

So you no longer need to strain your eyes or go online to parse anymore:)

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu