Microsoft SQL Server 2008 R2 Unleashed- P140:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1334 CHAPTER 36 Query Analysis where between 1B100 and B199 go SQL Server parse and compile time CPU time 0 ms elapsed time 0 ms. --output deleted 1077 row s affected Table sales_noclust . Scan count 100 logical reads 1383 physical reads 0 read-ahead reads 0 lob logical reads 0 lob physical reads 0 lob read-ahead reads 0. Table stores . Scan count 1 logical reads 3 physical reads 0 read-ahead reads 0 lob logical reads 0 lob physical reads 0 lob read-ahead reads 0. SQL Server Execution Times CPU time 0 ms elapsed time 187 ms. Here you can see that the total execution time denoted by the elapsed time was relatively low and not significantly higher than the CPU time. This is due to the lack of any physical reads and the fact that all activity is performed in memory. NOTE In some situations you might notice that the parse and compile time for a query is displayed twice. This happens when the query plan is added to the plan cache for possible reuse. The first set of information output is the actual parse and compile before placing the plan in cache and the second set of information output appears when SQL Server retrieves the plan from cache. Subsequent executions still show the same two sets of output but the parse and compile time is 0 when the plan is reused because a query plan is not being compiled. If elapsed time is much higher than CPU time the query had to wait for something either I O or locks. If you want to see the effect of physical versus logical I Os on the performance of a query you need to flush the pages accessed by the query from memory. You can use the DBCC DROPCLEANBUFFERS command to clear all clean buffer pages out of memory. Listing shows an example of clearing the pages from cache and rerunning the query with the STATISTICS IO and STATISTICS TIME options enabled. Download from Query Statistics 1335 TIP To ensure that none of the table is left in cache make sure all pages are marked as clean before running the DBCC
