SQL 服务器中的合并分区锁定表

Question

我有一个有400kk行的大表。

我想对这个表进行分区，但在合并两个较旧的分区函数时遇到问题。

我有这张表：

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

这是我的 400kk 行表。

我合并分区所做的是：

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');

问题是它在合并时锁定了两个表。

此问题的解决方法是什么？

Answer 1

如果使用此函数和方案删除第一个边界，则 2015-03-01 之前的所有数据都将按预期移动到PartitionDemo_FG_Prev文件组，而不是PartitionDemo_FG_Historical文件组。 我建议使用NULL分区边界，以确保第一个分区始终为空。 这也将允许您从此未使用的文件组中删除文件，并便于以后的分区维护。有关此做法的更多信息，请参阅 http://www.dbdelta.com/table-partitioning-best-practices/。

在SWITCH、MERGE和SPLIT操作期间将获取一个简短的模式修改锁，但这些操作应该快速元数据操作，因为不需要数据移动。物理数据移动由临时表CREATE INDEX...DROP_EXISTING-ON完成，这也避免了重建索引的排序。此脚本在SWITCH、MERGE和SPLIT操作期间获取一个独占表锁，以避免与其他活动死锁。

--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