Database Reference
In-Depth Information
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID
JOIN Production.Product p
ON sod.ProductID = p.ProductID
WHERE sod.LineTotal > 1000;
GO
SET STATISTICS TIME OFF
GO
The output of
STATISTICS TIME
for the preceding
SELECT
statement is as follows:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(32101 row(s) affected)
SQL Server Execution Times:
CPU time = 328 ms, elapsed time = 643 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
The
CPU
time = 328 ms part of the execution times represents the CPU value provided by the Profiler tool and the
Server Trace option. Similarly, the corresponding Elapsed time = 643 ms represents the Duration value provided by
the other mechanisms.
A 0 ms parse and compile time signifies that the optimizer reused the existing execution plan for this query
and therefore didn't have to spend any time parsing and compiling the query again. If the query is executed for the
first time, then the optimizer has to parse the query first for syntax and then compile it to produce the execution
plan. This can be easily verified by clearing out the cache using the system call
DBCC FREEPROCCACHE
and then
rerunning the query.
SQL Server parse and compile time:
CPU time = 32 ms, elapsed time = 33 ms.
(32101 row(s) affected)
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 678 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
This time, SQL Server spent 32 ms of CPU time and a total of 33 ms parsing and compiling the query.
■
you should not run
DBCC FREEPROCCACHE
on your production systems unless you are prepared to incur the
not insignificant cost of recompiling every query on the system. in some ways, this will be as costly to your system as a
reboot or a SQl Server instance restart.
Note
