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

Figure 12-36. Execution plan with outdated statistics
To see the estimated and actual rows, you can view the properties of the Table Scan operator (Figure 12-37 ).
Figure 12-37. Properties showing row count discrepancy
From the estimated rows value vs. the actual rows value, it's clear that the optimizer made an incorrect estimation
based on out-of-date statistics. If the difference between the estimated rows and actual rows is more than a factor
of 10, then it's quite possible that the processing strategy chosen may not be very cost-effective for the current data
distribution. An inaccurate estimation may misguide the optimizer in deciding the processing strategy. Statistics can be
off for a number of reasons. Table variables and multistatement user-defined functions don't have statistics at all, so all
estimates for these objects assume a single row, without regard to how many rows are actually involved with the objects.
To help the optimizer make an accurate estimation, you should update the statistics on the nonclustered index
key on column C1 (alternatively, of course, you can just leave the auto update statistics feature on).
UPDATE STATISTICS Test1 iFirstIndex WITH FULLSCAN;
A FULLSCAN might not be needed here. The sampled method of statistics creation is usually fairly accurate and
is much faster. But, on systems that aren't experiencing stress, or during off-hours, I tend to favor using FULLSCAN
because of the improved accuracy. Either approach is valid as long as you're getting the statistics you need.
If you run the query again, you'll get the following statistics, and the resultant output is as shown in Figure 12-38 :
Table 'Test1'. Scan count 1, logical reads 4
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.