FIX: Slow query performance occurs when you use NULL filters on Partition Key with default CE in SQL Server 2016

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

Symptoms


Assume that you have Microsoft SQL Server 2016 installed with a partitioned table and statistics on a partitioned column. You run a query that filters on the partition column by using IS NULL and with the default Cardinality Estimator (CE). In this scenario, you may observe inaccurate cardinality estimations and SQL Server uses an inefficient query plan. The following are some queries with predicates:

SELECT *FROM [MyTable] WHERE [Column1]='SomeValue' AND [PartitionColumn] IS NULLOrSELECT * FROM [MyTable]WHERE $PARTITION.PF(PartitionColumn) = 1

Resolution


This issue is fixed in the following cumulative update for SQL Server:

       Cumulative Update 3 for SQL Server 2016 SP2

Note: 

This fix requires one of the following to take effect:

Workaround


In this case, the legacy CE performs better until the fix is applied.

To work around this issue, use the FORCE_LEGACY_CARDINALITY_ESTIMATION USE HINT.

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 that Microsoft uses to describe software updates.