FIX: Poor query performance due to low cardinality estimation in SQL Server 2016 when you use default CE and column is covered by both single and multi-column statistics

Applies to: SQL Server 2016 Service Pack 2SQL Server 2016 DeveloperSQL Server 2016 Enterprise

Symptoms


Assume that you run a query involving a join or aggregationon a column that's covered by both single column and multiple columnstatistics (in which this column is not the leading column). If you are usingdefault Cardinality Estimator (CE) in SQL Server 2016, it may underestimate numberof distinct rows. This leads to a sub-optimal query execution plan (for example: low estimation may cause insufficient memory grant for a hash operator, which cause the query to spill to tempdb).

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. 

Resolution


This issue is fixed in the following cumulative update for SQL Server:
Note In order for this fix to take effect, you have to enable Query Optimizer Hotfixes using Trace Flag (TF) 4199 or corresponding database scoped configuration or query hint option.

About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:

Workaround


This issue can be fixed by adding the following query hint: 
USEHINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')

References


Learn about the terminology that Microsoft uses to describe software updates.