MSSQL Server分区表调整：如何避免日志插入操作被阻塞

针对日志高频写入场景下的分区调整问题，以下是经过验证的低阻塞/无阻塞解决方案：

1. 从后往前拆分空分区（最优方案） #

如果2018-2024年的日志数据集中在最后一个未拆分的分区，从最远的年份开始拆分，每次操作针对的都是空分区，无需移动数据，锁持有时间极短：

操作步骤： #

假设原分区函数LOG_FF为RANGE RIGHT，现有范围截止到2018-01-01，按以下顺序执行拆分：

-- 先拆分2025年边界（空分区，无数据移动）
ALTER PARTITION FUNCTION LOG_FF() SPLIT RANGE (N'2025-01-01T00:00:00.000');
GO

-- 拆分2024年边界
ALTER PARTITION FUNCTION LOG_FF() SPLIT RANGE (N'2024-01-01T00:00:00.000');
GO

-- 依次拆分2023到2019年的边界，覆盖所有目标年份
ALTER PARTITION FUNCTION LOG_FF() SPLIT RANGE (N'2023-01-01T00:00:00.000');
GO
-- ... 重复此步骤直到拆分2019-01-01

这种方式下，每个SPLIT仅修改元数据，不会触发数据移动，只会持有极短时间的Sch-M锁，基本不会阻塞日志插入。

2. 使用SWITCH操作分离数据后拆分 #

如果必须从早年份（如2018年）开始拆分，通过SWITCH元数据操作临时转移数据，避免拆分时移动数据：

操作步骤： #

1. 创建与原表结构、分区方案完全一致的临时分区表：

CREATE TABLE dbo.LogTable_Staging (
    -- 复制原表所有列、约束、索引定义
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    LogDate DATETIME NOT NULL,
    LogContent NVARCHAR(MAX) NOT NULL
) ON LOG_PS(LOG_FF(LogDate)); -- 使用与原表相同的分区方案

2. 将目标年份的分区数据切换到临时表（元数据操作，无数据拷贝）：

ALTER TABLE dbo.LogTable 
SWITCH PARTITION $PARTITION.LOG_FF('2018-01-01') 
TO dbo.LogTable_Staging PARTITION $PARTITION.LOG_FF('2018-01-01');
GO

3. 拆分分区函数（此时原表对应分区为空，操作极快）：

ALTER PARTITION FUNCTION LOG_FF() SPLIT RANGE (N'2019-01-01T00:00:00.000');
GO

4. 将临时表的数据切换回原表的新分区：

ALTER TABLE dbo.LogTable_Staging 
SWITCH PARTITION $PARTITION.LOG_FF('2018-01-01') 
TO dbo.LogTable PARTITION $PARTITION.LOG_FF('2018-01-01');
GO

5. 清理临时表：

DROP TABLE dbo.LogTable_Staging;
GO

3. 修复在线索引重建的问题 #

你之前的在线索引重建未生效，大概率是索引未分区对齐或SQL Server版本不支持分区索引在线重建，可按以下方式修正：

关键操作： #

• 确保索引与表分区对齐：索引的分区依据列与表的分区依据列（LogDate）一致。
• 针对单个分区重建索引，而非整个表：

-- 仅重建2018年分区的索引
ALTER INDEX IXNN_LogTable_LogDate ON dbo.LogTable
REBUILD PARTITION = $PARTITION.LOG_FF('2018-01-01')
WITH (
    ONLINE = ON,
    WAIT_AT_LOW_PRIORITY (
        MAX_DURATION = 5 MINUTES, -- 等待低优先级锁的最长时间
        ABORT_AFTER_WAIT = BLOCKERS -- 超时后终止阻塞会话，而非自身
    )
);
GO

注意：SQL Server 2016及以上版本才支持分区索引的在线重建，若使用更早版本，建议采用前两种方案。

额外注意事项 #

• 提前为每个年份的分区准备对应文件组，避免空间不足导致操作失败。
• 操作前用sys.dm_tran_locks查看当前锁状态，避开有长期持锁的场景。
• 尽量选择业务低峰窗口执行，进一步降低阻塞影响。

内容的提问来源于stack exchange，提问作者Gubaja