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

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


Assume that you have Microsoft SQL Server 2016 or 2017 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


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

       Cumulative Update 13 for SQL Server 2017

       Cumulative Update 3 for SQL Server 2016 SP2


This fix requires one of the following to take effect:


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.


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


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