Symptoms

Assume that you run a query involving a join or aggregation on a column that's covered by both single column and multiple column statistics (in which this column is not the leading column). If you are using default Cardinality Estimator (CE) in SQL Server 2016, it may underestimate number of 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:

USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')

References

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

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×