CHAPTER 28 ■ PERFORMANCE TUNING
object read events for tables referenced in the query. For a query that is having performance issues (based on your business requirements and definition of “issues”), you can use SET STATISTICS IO to see where the I/O hot spots are occurring. For example, in this recipe’s result set, you can see that the SalesOrderHeader had the highest number of logical reads:
... Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 3, read- ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. ...
Pay attention to high physical (reads from disk) or logical read values (reads from the data cache)— even if physical is zero and logical is a high value. Also look for worktables (which were also seen in this recipe):
Table 'Worktable'. Scan count 1, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Worktables are usually seen in conjunction with GROUP BY, ORDER BY, hash joins, and UNION operations in the query. Worktables are created in tempdb for the duration of the query, and are removed automatically when SQL Server has finished the operation.
In the second example in this recipe, SET STATISTICS TIME was used to show the parse and compile time of the query (shown before the actual query results), and then the actual execution time (displayed after the query results):
SQL Server parse and CPU time = 50 ms,
compile time: elapsed time = 117 ms.
(10 row(s) affected)
SQL Server Execution CPU time = 40 ms,
Times: elapsed time = 87 ms.
This command is useful for measuring the amount of time a query takes to execute from end-to-end, allowing you to see if precompiling is taking longer than you realized, or if the slowdown occurs during the actual query execution.
The two other STATISTICS commands, SET STATISTICS PROFILE and SET STATISTICS XML, return information similar to SET SHOWPLAN_ALL and SET SHOWPLAN_XML, only the results are based on the actual, rather than the estimated execution plan.
Viewing Performance Statistics for Cached Query Plans
In this recipe, I demonstrate using SQL Server 2005 dynamic management views and functions to view performance statistics for cached query plans.
In this example, a simple query that returns all rows from the Sales.Individual table is executed against the AdventureWorks database. Prior to executing it, you’ll clear the procedure cache so that you can identify the query more easily in this demonstration (remember that you should only clear out the procedure cache on test SQL Server instances):
DBCC FREEPROCCACHE GO
SELECT CustomerID, ContactID, Demographics, ModifiedDate FROM Sales.Individual