SQL Server: Understanding Output of SET STATISTICS TIME ON

In one of my early post Which is More Important for Query Optimization, CPU or Logical Reads? We have discussed about importance of logical page read values, that when we need to compare performance of two different versions of same query. If fewer pages are being accessed by one of our query, then it would be considered efficient as compare to other one.

But how much time a query is taking can be best measured by setting STATISTICS TIME on by using statement SET STATISTICS TIME ON.

--DBCC FREEPROCCACHE

SET STATISTICS TIME  ON

SELECT TOP (10) ProductID,[Name],Color,ListPrice,[Size]

FROM Production.Product

SET STATISTICS TIME  OFF

SQL Server parse and compile time:

   CPU time = 0 ms, elapsed time = 6 ms.

(10 row(s) affected)

SQL Server Execution Times:

   CPU time = 0 ms,  elapsed time = 1 ms.

This output can be grouped as

• Parse Time (in milliseconds) 
• Compile Time (in milliseconds) 
• Execution Time (in milliseconds)

Parse time is the time spent during checking SQL statement for syntax errors, breaking the command up into component parts, and producing an internal execution tree

Compile time is time spent during compiling an execution plan in cache memory from the execution tree that has just been produced.

And execution time is total time spent during execution of compiled plan.

All above three outputs are further divided to two subparts each i.e. CPU time and Elapsed time. CPU time is time used by CPU resources to complete a task (parse, compile or execute), while elapsed time is the total time took by a task from start to its end.

For example execution CPU time is just the time for which the CPU was busy executing the task and Elapsed time is the amount of time it took for the query to execute from start to completion. Most of the time elapsed time larger then CPU time because it also includes time spent during I/O operations required by query.

Elapsed time can be different for same query syntax on same server during different execution times because it depends upon other resources availability. Hence CPU time for execution of a query is important during performance comparison process.

Keep in mind that, ZERO parse and compile time is an indication that optimizer has reused the existing plan and hence made no effort and took no time for these processes.

DBCC FREEPROCCACHE is used to clear out the cache BUT DON’T use it on production servers.