Database Reference
In-Depth Information
Figure 3-20.
Indexes with ever-increasing keys: Histogram
Listing 3-17 shows the queries that select data for parameters, which are not present in the histogram. Figure
3-21
shows the cardinality estimations.
Listing 3-17.
Indexes with ever-increasing key values: Test queries
-- New Cardinality Estimator
select top 10 ID, ADate
from dbo.CETest
where ID between 66000 and 67000
order by PlaceHolder;
-- Legacy Cardinality Estimator
select top 10 ID, ADate
from dbo.CETest
where ID between 66000 and 67000
order by PlaceHolder
option (querytraceon 9481);
Figure 3-21.
Cardinality estimations for indexes with ever-increasing keys
As you can see, the legacy model estimated just the single row while the new model performed the estimation
based on the average data distribution in the index. The new model provides better results and lets you avoid frequent
manual statistics updates for indexes with ever-increasing key values.
