Database Reference
In-Depth Information
Figure 12-33 shows that the statistics for the column are missing. This may prevent the optimizer from selecting
the best processing strategy. The current cost of this query, as shown by SET STATISTICS IO and SET STATISTICS
TIME , is as follows:
Table 'Test1'. Scan count 1, logical reads 84
SQL Server Execution Times: CPU time = 0 ms, elapsed time = 22 ms.
To resolve this missing statistics issue, you can create the statistics on column Test1.C2 by using the CREATE
STATISTICS statement.
CREATE STATISTICS Stats1 ON Test1(C2);
Before rerunning the query, be sure to clean out the procedure cache because this query will benefit from simple
parameterization.
DBCC FREEPROCCACHE();
this should not be run on a production system because it will cause all plans stored in cache to be
removed, causing massive recompiles on all queries, which could cause a serious negative impact on performance.
Caution
Figure 12-34 shows the resultant execution plan with statistics created on column C2 .
Table 'Test1'. Scan count 1, logical reads 34
SQL Server Execution Times:CPU time = 0 ms, elapsed time = 17 ms.
Figure 12-34. Execution plan with statistics in place
The query optimizer uses statistics on a noninitial column in a composite index to determine whether scanning
the leaf level of the composite index to obtain the RID lookup information will be a more efficient processing strategy
than scanning the whole table. In this case, creating statistics on column C2 allows the optimizer to determine that
instead of scanning the base table, it will be less costly to scan the composite index on ( C1 , C2 ) and bookmark lookup
to the base table for the few matching rows. Consequently, the number of logical reads has decreased from 84 to 34,
but the elapsed time has decreased only slightly.
 
 
Search WWH ::




Custom Search