One of the several advantages you get with partitioning a very large table is the ability to add or remove partitions instantaneously to help you with sliding window scenarios. However there are caveats if best practices aren’t followed. If the right most partition (in case of a RANGE LEFT partition) or the left most partition (in case of a RANGE RIGHT partition) is not empty, then adding a partition is time and resource intensive Since it will be a logged operation and there will be data movement
Given I did not follow the best practices and don’t have an empty partition, the Split operation on a non-empty partition is expensive in particular if we have partitions with millions or billions of rows. How would I execute a split in a way that is non-logged and incurs minimal overhead?
For this scenario, the table is partitioned by a date time column with RANGE LEFT but the same holds true with other data types as well partitioned with RANGE LEFT.
Note: We recommend range right partitions for Datetime values. For additional details as to why see the whitepaper below. However for the purpose of the blog we are using RANGE LEFT given this problem occurs only on the split of the right most non-empty partition which is much more common.
CREATE PARTITION FUNCTION [Orders__Function](datetime) AS RANGE LEFT FOR VALUES
And here is how the data is partitioned in a table that uses the above partition function.
In order to understand the implications, let’s create a partitioned table and demonstrate how a split can result in data movement.
Note: The table we create has just 1000 rows in each partition, benefit is for much larger tables though.
Step 1: Scenario SETUP
drop database PartitionTest
create database PartitionTest
-- Add Filegroups
ALTER DATABASE [PartitionTest] ADD FILEGROUP [FG1];
ALTER DATABASE [PartitionTest] ADD FILEGROUP [FG2];
ALTER DATABASE [PartitionTest] ADD FILEGROUP [FG3];
ALTER DATABASE [PartitionTest] ADD FILEGROUP [FG4];
Alter database PartitionTest set recovery simple
-- Add Files
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_1', FILENAME = N'D:\Temp\PartitionTest_1.ndf') TO FILEGROUP [FG1]
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_2', FILENAME = N'D:\Temp\PartitionTest_2.ndf') TO FILEGROUP [FG2]
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_3', FILENAME = N'D:\Temp\PartitionTest_3.ndf') TO FILEGROUP [FG3]
ALTER DATABASE [PartitionTest] ADD FILE ( NAME = N'PartitionTest_4', FILENAME = N'D:\Temp\PartitionTest_4.ndf') TO FILEGROUP [FG4]
-- Create partition function
-- Create partition Scheme
CREATE PARTITION SCHEME [Orders__Scheme] AS PARTITION [Orders__Function] TO
-- 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] (OrdDate);
-- Insert rows into partitions (partition 4 in this case)
set nocount on
declare @i int
set @i = 1
declare @date Datetime
while (@i < 1000)
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;
-- Check the rowcount in each partition
select $PARTITION.[Orders__Function](Orddate) as PartionNum,COUNT(*) as CountRows from Orders
Group by $PARTITION.[Orders__Function](Orddate)
Now if we look at the distribution of rows, we will see that the last partition is not empty and has 999 rows
-- View Metadata before split
t.name as TableName,i.name as IndexName,
p.partition_id as partitionID,
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))
Now executing a Normal split, you can notice that for the 999 rows in that partition split, each record is deleted and then inserted into the new partition.
--- We now want to SPLIT a non-empty partition, so preparing for that
-- Add new Filegroup and file
ALTER DATABASE [PartitionTest] ADD FILEGROUP [FG5];
ALTER DATABASE [PartitionTest] ADD FILE
( NAME = N'PartitionTest_5', FILENAME = N'D:\Temp\PartitionTest_5.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
TO FILEGROUP [FG5]
-- Set the next used partition
Alter partition scheme [Orders__Scheme] NEXT USED [FG5]
--- Traditional Split
-- Clear Records to demonstrate Log records generated
-- Select to demonstrate that there are no log records for that table
select Operation,count(*) as NumLogRecords from fn_dblog(NULL,NULL)
where AllocUnitName= 'dbo.Orders.IX_Orders'
group by Operation
order by count(*) desc
-- Split the non-empty partition
ALTER PARTITION FUNCTION Orders__Function() SPLIT RANGE ('2013-09-30 23:59:59.99')
The result of the query below that shows us the Logs records indicate that each row was deleted and then inserted into the newly created partition.
-- Show how many log records generated, there is data movement Deletes followed by inserts
select Operation,AllocUnitName,count(*) as NumLogRecords from fn_dblog(NULL,NULL)
group by Operation,AllocUnitName
Also looking at the Metadata, you can see that the partition number 4 has been now assigned a new partition_id whereas the newly added partition (partition number 5) was assigned the partition_id that was previously assigned to partition number 4
So, the solution is to switch the data from Partition 4 into an empty table to make the last partition empty. You can then split the partition to add new boundary and then switch the data back into Partition 4.
In order to accomplish this efficiently, create a temporary staging table in the same file group (FG_unlimited in the example) as the last partition. Switch out the last partition (partition_number 4 in the example) into this staging table by using. The switch operation will finish in few seconds as it is only a metadata operation.
-- Create a copy table with intent to switch in
CREATE TABLE [dbo].[Orders_Copy](
-- Create the clustered index on the copy table on the same filegroup as the partition that we are trying to switch out.
CREATE UNIQUE CLUSTERED INDEX IX_Orders_Copy
ON [Orders_COPY](OrdDate asc,ID asc)
-- Execute the switch. After this, the last partition should be empty.
ALTER TABLE Orders SWITCH PARTITION 4 TO Orders_Copy;
-- All the data in partition 4 is now gone to the table Orders_Copy
select count(*) as NumRows from Orders_copy
Now we split an empty partition below, and there are no log record generated and this is a metadata operation only and if you notice the last 2 partitions have 0 rows.
-- Mark the Filegroup used by the last partition as the NEXT USED. This is for the SWITCH to work.
Alter partition scheme [Orders__Scheme]
NEXT USED [FG4]
-- Clear TLog Records
-- Split the now partition
ALTER PARTITION FUNCTION Orders__Function() SPLIT RANGE ('2013-09-30 23:59:59.997')
-- Will see no Logged data movement
And viewing the metadata:
You can now switch the data from the staging table back into partition 4.
Note: You need to ensure that you have a CHECK constraint created on the staging table with the appropriate date range, before you can switch the data back into the partition. Please check the following link for all requirements for Partition Switching operations.
-- Add the necessary check constraints. Otherwise you will see the following error.
--Msg 4982, Level 16, State 1, Line 1
--ALTER TABLE SWITCH statement failed. Check constraints of source table 'PartitionTest.dbo.Order_Copy' allow values that are not allowed by range defined by partition 4 on target table 'PartitionTest.dbo.Order'.
ALTER TABLE Orders_Copy ADD CHECK (OrdDate> '2013-06-30T23:59:59.997' and OrdDate <= '2013-09-30 23:59:59.997' );
-- Switch the partition that we had earlier swapped out to the Test table back.
ALTER TABLE Orders_Copy SWITCH TO Orders PARTITION 4;
-- Check and will see no logged operations on that table
And viewing the metadata:
Moral of the story:
To avoid data movement during partition split and merge operations, it is always recommended to keep at least the ‘left most’ and ‘right most’ partitions of a partitioned table to be empty. But in cases where it is not possible or if data has been inadvertently populated in to these partitions, you can use the partition switch in/out functionality to avoid data movement during split/merge operations.
As a side note, as far as partitioning goes, the SQL Cat team has a partition management utility that can be used - http://sqlpartitionmgmt.codeplex.com/.
Download Partition Script here
Kalyan Yella – Sr. Premier Field Engineer
Denzil Ribeiro – Sr. Premier Field Engineer ( @DenzilRibeiro )