Statistics - Pro SQL Server Internals - page 74

Figure 3-18. Outdated statistics: Cardinality estimations for the value, which is not a key in the histogram step
The same thing happens with the parameterized queries from Listing 3-15. The new model adjusts the estimation
based on the row count differences, while the legacy model ignores them. Figure 3-19 illustrates these estimations.
Figure 3-19. Outdated statistics: Cardinality estimations for an unknown value
Both approaches have their pros and cons. The new model produces better results when new data has been
evenly distributed in the index. This is exactly what happened in our case when ADate values were randomly
generated. Alternatively, the legacy model works better in the case of uneven distribution of new values when the
distribution of old values did not change. You can think about indexes with ever-increasing key values as an example.
Comparing Cardinality Estimators: Indexes with Ever-Increasing Key Values
The next test compares behavior of cardinality estimators when the value is outside of the histogram range. This often
happens in cases of indexes with ever-increasing key values, such as on the identity or sequence columns. Right now,
we have such a situation with the IDX_CETest_ID index. Index statistics were not updated after we inserted new rows
as shown in Figure 3-20 .