2

I have two example Queries (shown below). Both (drop and) create a database and a partitioned table in it. The table has several partitions, with data in each of them (including the last, unbounded, one). The partitioned data goes from 1 to 3996.

Then each script adds a new partition. The new partition starts at 4000.

In the first script the transaction log shows that every record in the last partition (Partition 4) is deleted and re-inserted.

The second script shows that no activity for the rows took place (no deletes and inserts).

The ONLY difference between the two scripts is the RANGE LEFT vs RANGE RIGHT on the Partition Function. RANGE RIGHT causes no deletes or inserts, RANGE LEFT causes all the rows in the last partition to be deleted and reinserted.

I thought RANGE LEFT or RANGE RIGHT just controlled if the border value went with the left or right partition. But it clearly does something else too.

Is there more to RANGE LEFT and RANGE RIGHT that I don't understand?

Also, I like the idea of adding partitions without impacting my system. And I am willing to use RANGE RIGHT if it gets me that. However, I am worried that this may be a bug of some kind and that I should not rely on it (as it may be "fixed" in a later version).

Is this a "feature" that can be relied on?


Scripts:

Range Left
Does Deletes and Inserts

use master
GO
-- Comment this next line out for the first run
DROP database PartitionTest
go
create database PartitionTest
go
use PartitionTest
go
-- Add Filegroups
ALTER DATABASE [PartitionTest] ADD FILEGROUP [DatePartitionTest];
GO
Alter database PartitionTest set recovery simple
go
-- Add Files
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'D:\PartitionTest_1.ndf') TO FILEGROUP [DatePartitionTest]
GO

-- This is the ONLY part that is different between the two scripts.  LEFT vs RIGHT
CREATE PARTITION FUNCTION [Orders_Id_Function](bigint) AS RANGE LEFT FOR VALUES
(1000,2000,3000)
GO

-- Create partition Scheme
CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders_Id_Function] TO
([DatePartitionTest],[DatePartitionTest],[DatePartitionTest],[DatePartitionTest])
-- Create table
CREATE TABLE [dbo].[Orders](
       [OrdDate] [datetime] NOT NULL,
       [ID] [bigint] IDENTITY(1,1) NOT NULL,
       [Addr] varchar(100) NOT NULL)

-- Partition the table
CREATE UNIQUE CLUSTERED INDEX IX_Orders
ON [Orders](OrdDate asc,ID asc)
ON [Orders__Scheme] (ID);
GO
-- Insert rows into  partitions (partition 4 in this case)
Use PartitionTest
set nocount on
go
declare @i int
set @i = 1
declare @date Datetime
while (@i < 1000)
begin
    set @date = dateadd(mi,@i,'2012-11-01T10:17:01.000')
    --insert into testtable values (@date)
    insert into [Orders] values (@date, 'Denzil')
    insert into [Orders] values (dateadd(month,3,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,6,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,9,@date), 'Denzil')
    set @i = @i+1;
END

-- Check the rowcount in each partition
select $PARTITION.[Orders_ID_Function](ID) as PartionNum,COUNT(*) as CountRows from  Orders
Group by $PARTITION.[Orders_ID_Function](ID)

Alter partition scheme [Orders__Scheme] NEXT USED [DatePartitionTest]
GO

-- Set a check point for the log file.
CHECKPOINT
GO

-- Add the new partition
ALTER PARTITION FUNCTION Orders_ID_Function() SPLIT RANGE (4000)

select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)
where AllocUnitName= 'dbo.Orders.IX_Orders'
group by Operation
order by count(*) DESC


SELECT
t.name as TableName,i.name as IndexName,
p.partition_id as partitionID,
p.partition_number,rows, fg.name
FROM sys.tables AS t 
         INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)
     INNER JOIN sys.partitions AS p ON (t.object_id = p.object_id and i.index_id = p.index_id)
        INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)
        INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)
WHERE (t.name = 'Orders') and (i.index_id IN (0,1))

Range Right
Does NOT do Inserts or Deletes

use master
GO
-- Comment this next line out for the first run
DROP database PartitionTest
go
create database PartitionTest
go
use PartitionTest
go
-- Add Filegroups
ALTER DATABASE [PartitionTest] ADD FILEGROUP [DatePartitionTest];
GO
Alter database PartitionTest set recovery simple
go
-- Add Files
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'D:\PartitionTest_1.ndf') TO FILEGROUP [DatePartitionTest]
GO

-- This is the ONLY part that is different between the two scripts.  LEFT vs RIGHT
CREATE PARTITION FUNCTION [Orders_Id_Function](bigint) AS RANGE RIGHT FOR VALUES
(1000,2000,3000)
GO

-- Create partition Scheme
CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders_Id_Function] TO
([DatePartitionTest],[DatePartitionTest],[DatePartitionTest],[DatePartitionTest])
-- Create table
CREATE TABLE [dbo].[Orders](
       [OrdDate] [datetime] NOT NULL,
       [ID] [bigint] IDENTITY(1,1) NOT NULL,
       [Addr] varchar(100) NOT NULL)

-- Partition the table
CREATE UNIQUE CLUSTERED INDEX IX_Orders
ON [Orders](OrdDate asc,ID asc)
ON [Orders__Scheme] (ID);
GO
-- Insert rows into  partitions (partition 4 in this case)
Use PartitionTest
set nocount on
go
declare @i int
set @i = 1
declare @date Datetime
while (@i < 1000)
begin
    set @date = dateadd(mi,@i,'2012-11-01T10:17:01.000')
    --insert into testtable values (@date)
    insert into [Orders] values (@date, 'Denzil')
    insert into [Orders] values (dateadd(month,3,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,6,@date), 'Denzil')
    insert into [Orders] values (dateadd(month,9,@date), 'Denzil')
    set @i = @i+1;
END

-- Check the rowcount in each partition
select $PARTITION.[Orders_ID_Function](ID) as PartionNum,COUNT(*) as CountRows from  Orders
Group by $PARTITION.[Orders_ID_Function](ID)

Alter partition scheme [Orders__Scheme] NEXT USED [DatePartitionTest]
GO

-- Set a check point for the log file.
CHECKPOINT
GO

-- Add the new partition
ALTER PARTITION FUNCTION Orders_ID_Function() SPLIT RANGE (4000)

select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)
where AllocUnitName= 'dbo.Orders.IX_Orders'
group by Operation
order by count(*) DESC


SELECT
t.name as TableName,i.name as IndexName,
p.partition_id as partitionID,
p.partition_number,rows, fg.name
FROM sys.tables AS t 
         INNER JOIN sys.indexes AS i ON (t.object_id = i.object_id)
     INNER JOIN sys.partitions AS p ON (t.object_id = p.object_id and i.index_id = p.index_id)
        INNER JOIN sys.destination_data_spaces dds ON (p.partition_number = dds.destination_id)
        INNER JOIN sys.filegroups AS fg ON (dds.data_space_id = fg.data_space_id)
WHERE (t.name = 'Orders') and (i.index_id IN (0,1))
5

I thought RANGE LEFT or RANGE RIGHT just controlled if the border value went with the left or right partition. But it clearly does something else too.

Is there more to RANGE LEFT and RANGE RIGHT that I don't understand?

Yes, there is other significance to RANGE LEFT and RIGHT as your scripts demonstrate. The range specification determines if the newly created partition is to the left or right of the existing split partition. The range specification also determines the partition removed by MERGE, which is the partition that includes the existing boundary.

In the first RANGE LEFT script, the existing 4th partition is split into 2 adjacent ones, with the new one on the left. The new partition becomes partition #4 and the existing one is renumbered to #5. Rows from the existing partition (now #5) must be moved to #4 to accommodate the new boundaries, hence the excessive logging. Movement during partition SWITCH (or MERGE) requires about 4x the logging of normal DML operations so it is important to plan as to avoid data movement, especially with large tables that are often used in table partitioning scenarios.

No data movement is needed in the RANGE RIGHT script because the new partition (#5) is created to the right of the existing partition 4 and the existing partition 4 already conforms to those boundaries (data >= 3000 and < 4000).

Personally, I think RANGE RIGHT is more intuitive, especially with incremental partitioning column values. See http://www.dbdelta.com/category/table-partitioning/ for more gotchas.

  • 1
    If your partitioning key is descending then range right might not be "intuitive" - the key is understanding the best configuration for the problem at hand. – Max Vernon Jun 5 '15 at 3:35
5

Is this a "feature" that can be relied on?

No, it is observed (as opposed to documented) behaviour.

To be clear, the observed behaviour is that SPLIT is a metadata operation using RIGHT boundaries if there is no data to the right of the split value; for LEFT boundaries, the operation is metadata-only if there is no data to the left (blog post with examples here).

As I understand it, this is the result of an (arbitrary) decision concerning which side of the split should become the 'new' partition. Arguably, SQL Server could be smart enough to choose the option that would result in the least data movement, but this is not implemented today.

The safe (documented and supported) option is to only split empty partitions. In a sliding-window implementation, this means maintaining an empty partition on the extreme edge (either right or left).

There are many good reasons to choose between left or right boundary values. An undocumented aspect of the behaviour with split or merge should not figure in that decision.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.