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.
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:
SET STATISTICS IO ON;
SET STATISTICS IO OFF;
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:
SET STATISTICS TIME ON;
SET STATISTICS TIME OFF;
Example Result of STATISTICS TIME ON:
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.
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.