This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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:
1 2 |
SET STATISTICS IO ON; SET STATISTICS IO 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:
1 2 |
SET STATISTICS TIME ON; SET STATISTICS TIME OFF; |
1 2 3 4 |
SQL Server Execution Times: CPU time = 10 ms, elapsed time = 23 ms. SQL Server parse and compile time: CPU time = 115 ms, elapsed time = 50 ms. |