Partition switching needs to be smarter

A lot has been said already on how to do partition switching in SQL Server. I would suggest reading this blog post by Cathrine Wilhelmson, which is my go-to whenever I forget the syntax for partition switching.

Partition switching needs to be smarter. Or at least, it needs to not give me an error that is a false negative.

See the example below. SQL Sever version is SQL Server 2017 CU15.

DROP TABLE IF EXISTS PartitionSwitchTarget;
DROP TABLE IF EXISTS PartitionSwitchSource;
GO
IF EXISTS ( SELECT 1
              FROM sys.partition_schemes
              WHERE name = N'PartitionSwitchScheme' )
BEGIN
  DROP PARTITION SCHEME PartitionSwitchScheme;
END;
GO
IF EXISTS ( SELECT 1
              FROM sys.partition_functions
              WHERE name = N'PartitionSwitchFunction' )
BEGIN
  DROP PARTITION FUNCTION PartitionSwitchFunction;
END;
GO

CREATE PARTITION FUNCTION PartitionSwitchFunction( bigint )
  AS RANGE RIGHT FOR VALUES( 1,
                             100,
                             1000 );
GO
CREATE PARTITION SCHEME PartitionSwitchScheme
  AS PARTITION PartitionSwitchFunction
  ALL TO ( [PRIMARY] );
GO

CREATE TABLE PartitionSwitchSource
(
  Id bigint
) ON PartitionSwitchScheme (Id);
GO
CREATE TABLE PartitionSwitchTarget
(
  Id bigint
);
GO


ALTER TABLE dbo.PartitionSwitchTarget
ADD CONSTRAINT MyCheckConstraint CHECK( Id >= 1
                                   AND Id <= 99
                                   AND Id IS NOT NULL );

GO
ALTER TABLE dbo.PartitionSwitchSource 
  SWITCH PARTITION 2 
  TO dbo.PartitionSwitchTarget

This is a RANGE RIGHT bigint partition function. This means that the left endpoints are included in each range and the right endpoints are excluded. When I run this on my SQL Server instance, I get this message:
Msg 4972, Level 16, State 1, Line 47
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'TestDb.dbo.PartitionSwitchSource' allows values that are not allowed by check constraints or partition function on target table 'TestDb.dbo.PartitionSwitchTarget'.

Since this is a bigint partition function, the “less than or equal to 99” predicate is logically equivalent to the “strictly less than 100” predicate. In fact, if I change the check constraint above to say “< 100", the partition switch works without issue.

The SQL Server optimizer also has problems with these types of filters. This blog post by Paul White has a pretty good write up about it:

“The SQL Server 2008 (and later) optimizers do not quite get the internal logic right when an interval references, but excludes, a boundary value belonging to a different partition. The optimizer incorrectly thinks that multiple partitions will be accessed, and concludes that it cannot use the single-partition optimization for MIN and MAX aggregates.”

It goes beyond MIN/MAX aggregates indeed. Look at this query using the same partitioned table above:

SELECT COUNT( * )
  FROM PartitionSwitchSource
  WHERE Id >= 1
    AND Id < 100;

The optimizer accesses two partitions:

partitioncount2

If I change the filter to “less than or equal 99”, I get 1 partition:

SELECT COUNT( * )
  FROM PartitionSwitchSource
  WHERE Id >= 1
    AND Id <= 99;

partitioncount1

If you’re a developer like me and you are trying to write code to return the “appropriate” filter for table that has partitions, it can be tricky. On the one hand, I apparently must use “strictly less than 100” so that I can do a partition switch later. On the other hand, I have to use “less than or equal to 99” so that SQL Server won’t access the partition to the right when it isn’t necessary to do so.

I have had success writing the filter like this:

  WHERE Id >= 1
    AND ( Id < 100
          OR Id <= 100 - 1 );

This filter in the check constraint allows for partition switching as desired. It also convinces the optimizer that it is OK to only look at 1 partition. For now, this may be the workaround.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s