Statistics, Data Distribution, and Cardinality - SQL Server Query Performance Tuning - page 227

Resolving an Outdated Statistics Issue
Sometimes outdated or incorrect statistics can be more damaging than missing statistics. Based on old statistics or
a partial scan of changed data, the optimizer may decide upon a particular indexing strategy, which may be highly
inappropriate for the current data distribution. Unfortunately, the execution plans don't show the same glaring
warnings for outdated or incorrect statistics as they do for missing statistics. However, there is an event called
inaccurate_cardinality_estimate . This is a debug event, which means its use could be somewhat problematic on a
production system. I strongly caution you in its use, only when properly filtered and only for short periods of time, but
I want to point it out.
The more traditional, and safer, approach to identify outdated statistics is to examine how close the optimizer's
estimation of the number of rows affected is to the actual number of rows affected.
The following example shows you how to identify and resolve an outdated statistics issue. Figure 12-35 shows the
statistics on the nonclustered index key on column C1 provided by DBCC SHOW_STATISTICS .
DBCC SHOW_STATISTICS (Test1, iFirstIndex);
Figure 12-35. Statistics on index FirstIndex
These results say that the density value for column C1 is 0.5. Now consider the following SELECT statement:
SELECT *
FROM dbo.Test1
WHERE C1 = 51;
Since the total number of rows in the table is currently 10,002, the number of matching rows for the filter criteria
C1 = 51 can be estimated to be 5,001 (=0.5 x 10,002). This estimated number of rows (5,001) is way off the actual
number of matching rows for this column value. The table actually contains only one row for C1 = 51 .
You can get the information on both the estimated and actual number of rows from the execution plan. An
estimated plan refers to and uses the statistics only, not the actual data. This means it can be wildly different from
the real data, as you're seeing now. The actual execution plan, on the other hand, has both the estimated and actual
numbers of rows available.
Executing the query results in this execution plan (Figure 12-36 ) and performance:
Table 'Test1'. Scan count 1, logical reads 84
SQL Server Execution Times:CPU time = 0 ms, elapsed time = 16 ms.