This week I have got a question regarding my post New Cardinality Estimator Part 4 – Single Table and Multiple Predicates. The question was about possibilities to affect the estimation with multiple predicates done by new cardinality estimator.
To demonstrates these possibilities we will use again the AdventureWorks2012 database and the SalesOrderHeader table.
SQL Server 2012
Let’s execute the following queries in SQL Server 2012. First two queries have only one predicate and in the third query they are combined with the AND operator:
SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 0;
SELECT * FROM Sales.SalesOrderHeader WHERE SalesPersonID = 282;
SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 0 AND SalesPersonID = 282;
Let’s observe the estimation details:
As we can see in one of the previous posts SQL Server 2012 assumes that there is no correlation between predicates and any additional predicate significantly reduce the estimated number of rows. SQL Server estimates (accurate) that 271 orders are made by the sales person identified by id 282, but when we in addition filter by online flag too, the estimation decreases to only 33 rows.
However, in this particular case we know that an order having a sales person as attribute is always done offline, so, its OnlineOrderFlag attribute is from business point of view always 0. Therefore these two predicates are strongly correlated and with the assumption about independency between predicates the Query Optimizer cannot make a good estimation. Can we do something to get better estimation? Yes, we can use the trace flag 4137 to enforce the full correlation between predicates (which results with the lowest cardinality of individual predicates).
SELECT * FROM Sales.SalesOrderHeader WHERE OnlineOrderFlag = 0 AND SalesPersonID = 282 OPTION (QUERYTRACEON 4137);
SQL Server 2014
In SQL Server 2014 predicates are correlated. They are not independent anymore, but they are also not fully correlated. Here are the estimations for the first three queries made by new cardinality estimator:
The estimated number of rows is higher than with SQL Server 2012, but again far away from the actual number of rows. Again, we can use the trace flag. This time the trace flag number for enforcing full correlation is 9471.
Since predicates in SQL Server 2014 are somehow correlated and you know that the predicates are not correlated you have another one flag 9472 to force the same estimation for multiple predicates as with old CE. Here are estimations with new CE:
Conclusion
In SQL Server 2012 predicates are independent. If you want to force full correlation (simple containment) use the trace flag 4137.
SQL Server 2014 assumes that predicates are correlated. To enforce full correlation use the trace flag 9471. For the same estimation as with SQL Server 2012 (no correlation) use the trace flag 9472.