Statistics - Pro SQL Server Internals - page 71

Listing 3-13. Up-to-date statistics: Selecting data for value, which is a key in the histogram step
-- New Cardinality Estimator
select ID, ADate, Placeholder
from dbo.CETest with (index=IDX_CETest_ADate)
where ADate = '2013-06-24'
-- Legacy Cardinality Estimator
select ID, ADate, Placeholder
from dbo.CETest with (index=IDX_CETest_ADate)
where ADate = '2013-06-24'
option (querytraceon 9481)
As you can see in Figure 3-14 , the results are the same in both cases. SQL Server uses a value from the EQ_ROWS
column from the 14 th histogram step for the estimation.
Figure 3-14. Up-to-date statistics: Cardinality estimations for value, which is a key in the histogram step
Now let's run the query, which selects data for ADate = '2013-06-23' , which is not present in the histogram
as a key. Listing 3-14 illustrates the queries.
Listing 3-14. Up-to-date statistics: Selecting data for value, which is not a key in the histogram step
-- New Cardinality Estimator
select ID, ADate, Placeholder
from dbo.CETest with (index=IDX_CETest_ADate)
where ADate = '2013-06-23'
-- Legacy Cardinality Estimator
select ID, ADate, Placeholder
from dbo.CETest with (index=IDX_CETest_ADate)
where ADate = '2013-06-23'
option (querytraceon 9481)
Again, the results shown in Figure 3-15 are the same for both models. SQL Server uses the AVG_RANGE_ROWS
column value from the 14 th histogram step for the estimation.