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

Applies to: SQL Server 2016 DeveloperSQL Server 2016 EnterpriseSQL Server 2016 Enterprise Core More

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 and 2017 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 updates 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.
 

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.