Special Indexing and Storage Features - Pro SQL Server Internals - page 92

Figure 4-10. Cardinality estimation with correlated predicates (Legacy Cardinality Estimator)
The Legacy cardinality estimator assumes the independence of predicates and uses the following formula:
(Selectivity of first predicate * Selectivity of second predicate) * (Total number of rows in table)
= (Estimated number of rows for first predicate * Estimated number of rows for second predicate) /
(Total number of rows in the table) = (4096 * 256) / 6912 = 151.704
The new cardinality estimator, introduced in SQL Server 2014, takes a different approach and assumes some
correlation between predicates. It uses the following formula:
(Selectivity of most selective predicate) * SQRT(selectivity of next most selective predicate) =
(256 / 6912) * SQRT(4096 / 6912) * 6912 = 256 * SQRT(4096 / 6912) = 197.069
Even though this formula provides better results in this case, it is still incorrect, as shown in Figure 4-11 .
Figure 4-11. Cardinality estimation with correlated predicates (New Cardinality Estimator in SQL Server 2014)
One solution to this problem is filtered column-level statistics. These could improve cardinality estimation in the
case of correlated predicates. Listing 4-18 creates filtered statistics on the Model column for all cars made by Toyota.