Useful for tuning – SET STATISTICS TIME ON

By enabling SET STATISTICS TIME ON, you are instructing SQL to return the parse, compile and execution time of each statement. This information can be very useful when tuning your queries. This is particularly true when your query runtime is under 1 second as the Query Analyzer timer (bottom-right of query window) is only accurate to the second and also displays network time to return the results to the client. SET STATISTICS TIME ON is different in that it only includes server time to process the query. This, after all, is the only part you need to worry about when tuning your queries.

Example Response:

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 166 ms.

(1000 row(s) affected)

SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 680 ms.

The parse and compile time refer to the time taken for the SQL server to ensure the query is syntactically correct (parse time) and for the Query Optimizer to determine the optimal execution plan (compile time). If the parse/compile time is 0ms, this indicates the execution plan was already in cache.

For the execution output, there is CPU time and Elapsed time. CPU time is the one to pay particular attention to, it displays the time used to process the query. The Elapsed is the actual run time of the query but it is influenced by server load, I/O load and wait times so it is not ideal when performance tuning. Also, don’t be surprised if you see CPU time is greater than Elapsed time. This just indicates that parallelism was used by the query.

Note: The SET STATISTICS TIME ON command only affects the current session. It is not a server-level setting.

SET STATISTICS IO ON is also a great companion to SET STATISTICS TIME ON.

Happy Tuning!