on March 24, 2016
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
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!
REGISTER