A non-optimal query plan choice causes poor performance when values outside the range represented in statistics are searched in SQL Server 2016

Symptoms

Consider the following scenario:
  • You use a cardinality estimation (CE) configuration that corresponds to database compatibility level 120 or a later level for your Microsoft SQL Server 2016 system.
  • You run a query that contains a search predicate.
  • The query searches for values that are less than or equal to any number that's less than the minimum value of RANGE_HI_KEY that's stored in the statistics object. Or, the query searches for values that are greater than the maximum value of RANGE_HI_KEY for the same statistics.

In this scenario, the query optimizer overestimates the number of rows. This could cause a less-than-optimal query plan choice and poor performance.

Resolution

The fix for this issue is included in the following cumulative update for SQL Server:


Note After you install this update, you must enable trace flag 4199 in order to enable this fix. To enable trace flag 4199, see the Trace Flags (Transact-SQL) topic on the Microsoft Developer Network (MSDN) website.

About cumulative updates for SQL Server
About cumulative updates for SQL Server

Status

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

References

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

Article ID: 3192154 - Last Review: Sep 22, 2016 - Revision: 1

Microsoft SQL Server 2016 Developer, Microsoft SQL Server 2016 Enterprise, Microsoft SQL Server 2016 Enterprise Core, Microsoft SQL Server 2016 Standard

Feedback