SQL Server: SET STATISTICS IO and SET STATISTICS TIME measure the Query Performance

Microsoft SQL Server provides different ways to measure the performance of Query Execution.
But two of my favourite options are SET STATISTICS IO and SET STATISTICS TIME.

Both the options are very easy to use and return the execution statistics of Query.
Both are session specific options; you can set ON/OFF at session level and returns the result in the Message Window.

STATISTICS IO:
Using this option, you can find that how your query is interacting with your Physical Storage.
You will get the information like:

  • Scan count: Total number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.
  • Physical Reads: Physical access of total number of 8kb pages which are not available in Memory.
  • Logical Reads: Total number of pages read from the data cache.
  • Read-ahead Reads: Total number of pages placed into the cache for the query.

SET STATISTICS IO ON/OFF:

STATISTICS TIME:

You can use SET STATISTICS TIME to see the number of milliseconds required to parse, compile, and execute of a Query.

This is very useful for tracking different stages of Query Execution.
Using CPU time, You can find server side CPU usage and using Elapsed time, you can find that how long it took to return the data to the client.

SET STATISTICS TIME ON/OFF:

Example Result of STATISTICS TIME ON:
Anvesh Patel
Sort by:   newest | oldest | most voted
Suresh
Guest

Hi Anvesh, Thanks for posting about Set statistics IO & TIME ON.
I want more detail abt how to troubleshoot high value of Elapsed time values and compare with CPU time value…
Also requested that how to check Client statistics and its values to performance issue.
I am waiting your valuable response.

wpDiscuz