SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Better Together: Reduced Run Time and I/O

The run-time metric gets all the love, and for good reason.  It’s easily witnessed by end users and the one management cares about.  When DBAs have the task of cutting down the run time of a query, this typically starts in a non production environment, consisting of different server architecture and storage speed than production.

If you’re able to improve the run time in a lower environment, how can you know for certain it will be improved in Production as well?

 

Enter SET STATISTICS I/O

SQL Server can easily track how much I/O is happening against the various tables in your query.

In your query window in SSMS, enter the following:

SET STATISTICS IO ON
GO

This will enable the SET STATISTICS IO output in the messages tab of your current session, as seen below:

SETStatisticsIO

The main metric you’ll want to pay attention to for most query tuning activity are your logical reads.  This number tells us how many times SQL Server had to read a page from memory to return the result of your query.  Reducing this number will ultimately reduce the run time of your query.

Physical reads are important too, as this number indicates how many times SQL Server had to go to disk in order to read a page into memory.  If you run your query two times in a row, chances are you will see the physical reads drop to 0 as pictured below, since SQL Server already read the initial pages into memory with the first execution:

SETStatisticsIO_0PReads

Using SET STATISTICS IO to measure I/O in conjunction with run time will give you a better view of the improvements your tuning efforts have made.  Using these metrics together will allow you to have more concrete evidence that your query will indeed be improved in production.


Adam Kreul | Blog

I am currently a Senior DBA for Integrys Energy Services in Green Bay, WI. I’ve been a DBA since 2008 and have worked primarily with SQL Server versions 2005 and above supporting databases as large as 6TB.

Although my title says “DBA”, I also play the role of SAN administrator and data warehouse developer. I love learning and applying new technology and have a passion for making the environment I support as fast as possible. My blog is syndicated from www.adamkreul.com and you can follow me on Twitter at @AdamKreul.

Comments

Leave a comment on the original post [www.adamkreul.com, opens in a new window]

Loading comments...