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:
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:
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.
