I have a BIG table with 400kk rows.
I want to partition this table but I`m having a problem when merging the two older Partition Functions.
I have this table:
CREATE TABLE [dbo].[PartitionDemo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[myDate] [date] NOT NULL,
[variable] [varchar](100) NULL,
CONSTRAINT [pk_PartitionDemo] PRIMARY KEY CLUSTERED
(
[myDate] ASC,
[Id] ASC
)ON [PartitionDemo_PS](mydate)
)
CREATE PARTITION SCHEME [PartitionDemo_PS] AS PARTITION [PartitionDemo_PF] TO ([PartitionDemo_FG_Prev], [PartitionDemo_FG_Historical], [PartitionDemo_FG_201609], [PartitionDemo_FG_201610], [PartitionDemo_FG_201611], [PartitionDemo_FG_201612], [PartitionDemo_FG_201701], [PartitionDemo_FG_201702], [PartitionDemo_FG_201703], [PartitionDemo_FG_201704])
GO
CREATE PARTITION FUNCTION [PartitionDemo_PF](date) AS RANGE RIGHT FOR VALUES (N'2015-03-01T00:00:00.000', N'2016-09-01T00:00:00.000', N'2016-10-01T00:00:00.000', N'2016-11-01T00:00:00.000', N'2016-12-01T00:00:00.000', N'2017-01-01T00:00:00.000', N'2017-02-01T00:00:00.000', N'2017-03-01T00:00:00.000', N'2017-04-01T00:00:00.000')
GO
This is my table with 400kk rows.
What I do to merge partition is:
CREATE TABLE [staging].[PartitionDemo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[myDate] [date] NOT NULL,
[variable] [varchar](100) NULL,
CONSTRAINT [pk_PartitionDemo] PRIMARY KEY CLUSTERED
(
[myDate] ASC,
[Id] ASC
)ON [PartitionDemo_PS](mydate)
)
GO
ALTER TABLE PartitionDemo
SWITCH PARTITION 2 TO [staging].[PartitionDemo] PARTITION 2
ALTER TABLE PartitionDemo
SWITCH PARTITION 3 TO [staging].[PartitionDemo] PARTITION 3
ALTER PARTITION FUNCTION [PartitionDemo_PF]()
MERGE RANGE ('2016-03-01');
The problem is that it locks both tables while merging.
What is the workaround with this problem?
If you remove the first boundary with this function and scheme, all data before 2015-03-01 will be moved to the PartitionDemo_FG_Prev filegroup instead of the PartitionDemo_FG_Historical filegroup as intended. I recommend a NULL partition boundary to ensure the first partition is always empty. This will also allow you to remove files from this unused filegroup and facilitate partition maintenance going forward. See http://www.dbdelta.com/table-partitioning-best-practices/ for more information on this practice.
A brief schema modification lock will be acquired during the SWITCH, MERGE, and SPLIT operations but those should fast meta-data operations because no data movement is needed. The physical data movement is done by the staging table CREATE INDEX...DROP_EXISTING-ON, which also avoids a sort to rebuild the index. This script acquires an exclusive table lock during the SWITCH, MERGE, and SPLIT operations to avoid deadlocking with other activity.
--create staging table exactly like original table
CREATE TABLE [staging].[PartitionDemo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[myDate] [date] NOT NULL,
[variable] [varchar](100) NULL,
CONSTRAINT [pk_PartitionDemo] PRIMARY KEY CLUSTERED
(
[myDate] ASC,
[Id] ASC
) ON [PartitionDemo_PS](mydate)
);
--create temporary partition function and scheme with desired end state
CREATE PARTITION FUNCTION [StagingPartitionDemo_PF](date) AS RANGE RIGHT FOR VALUES (
CAST(NULL AS datetime) --NULL boundary ensures first parttion is always empty
, N'2016-09-01T00:00:00.000' --upper boundary of historical data fg (less than this date)
, N'2016-10-01T00:00:00.000'
, N'2016-11-01T00:00:00.000'
, N'2016-12-01T00:00:00.000'
, N'2017-01-01T00:00:00.000'
, N'2017-02-01T00:00:00.000'
, N'2017-03-01T00:00:00.000'
, N'2017-04-01T00:00:00.000'
);
CREATE PARTITION SCHEME [StagingPartitionDemo_PS] AS PARTITION [StagingPartitionDemo_PF] TO (
[PartitionDemo_FG_Prev]
, [PartitionDemo_FG_Historical]
, [PartitionDemo_FG_201609]
, [PartitionDemo_FG_201610]
, [PartitionDemo_FG_201611]
, [PartitionDemo_FG_201612]
, [PartitionDemo_FG_201701]
, [PartitionDemo_FG_201702]
, [PartitionDemo_FG_201703]
, [PartitionDemo_FG_201704]
);
GO
SET XACT_ABORT ON;
BEGIN TRAN;
--acquire exclusive table lock to prevent deadlocking with concurrent activity
SELECT TOP(0) myDate FROM dboPartitionDemo WITH(TABLOCKX);
--switch first partition into staging (in case data exists before 2015-03-01)
ALTER TABLE dbo.PartitionDemo
SWITCH PARTITION $PARTITION.PartitionDemo_PF(CAST(NULL AS datetime))
TO [staging].[PartitionDemo] PARTITION $PARTITION.PartitionDemo_PF(CAST(NULL AS datetime));
--switch second partition into staging (on or after 2015-03-01 and before 2016-09-01)
ALTER TABLE dbo.PartitionDemo
SWITCH PARTITION $PARTITION.PartitionDemo_PF('2015-03-01T00:00:00.000')
TO [staging].[PartitionDemo] PARTITION $PARTITION.PartitionDemo_PF('2015-03-01T00:00:00.000');
--switch third partition into staging (on or after 2016-09-01 and before 2016-10-01)
ALTER TABLE dbo.PartitionDemo
SWITCH PARTITION $PARTITION.PartitionDemo_PF('2016-09-01T00:00:00.000')
TO [staging].[PartitionDemo] PARTITION $PARTITION.PartitionDemo_PF('2016-09-01T00:00:00.000');
COMMIT;
GO
--rebuild staging table on temporary partition scheme
CREATE UNIQUE CLUSTERED INDEX pk_PartitionDemo ON staging.PartitionDemo(
[myDate] ASC,
[Id] ASC
)
WITH(DROP_EXISTING=ON)
ON [StagingPartitionDemo_PS](mydate);
GO
SET XACT_ABORT ON;
BEGIN TRAN;
--acquire exclusive table lock to prevent deadlocking with concurrent activity
SELECT TOP(0) myDate FROM dboPartitionDemo WITH(TABLOCKX);
--modify original partition scheme to match temporary one
ALTER PARTITION SCHEME PartitionDemo_PS
NEXT USED PartitionDemo_FG_Historical;
ALTER PARTITION FUNCTION PartitionDemo_PF()
SPLIT RANGE(CAST(NULL AS datetime));
ALTER PARTITION FUNCTION PartitionDemo_PF()
MERGE RANGE('2015-03-01T00:00:00.000');
--switch historical data partition partition back to main table
ALTER TABLE staging.PartitionDemo
SWITCH PARTITION $PARTITION.PartitionDemo_PF(NULL)
TO dbo.[PartitionDemo] PARTITION $PARTITION.PartitionDemo_PF(CAST(NULL AS datetime));
--switch 2016-09-01 partition back to main table
ALTER TABLE staging.PartitionDemo
SWITCH PARTITION $PARTITION.PartitionDemo_PF('2016-09-01T00:00:00.000')
TO dbo.[PartitionDemo] PARTITION $PARTITION.PartitionDemo_PF('2016-09-01T00:00:00.000');
COMMIT;
GO