SQL Server分区函数更新最优方案及索引操作耗时估算咨询

一、删除/重建聚集列存储索引（CCI）的耗时与内存估算方法 #

耗时估算 #

• 小比例测试法：选取同结构的小样本表（比如取原表1%的数据），执行删除+重建CCI的完整流程，记录耗时。考虑到大表会受IO带宽、存储延迟的瓶颈影响，最终估算时在比例放大的基础上额外预留30%-50%的冗余时间。
• 历史数据参考：如果该表之前有过CCI重建操作，查询SQL Server作业历史记录或sys.dm_exec_query_stats中的历史执行时长，以此作为基准调整估算值。
• 实时进度监控：在测试环境中对单表执行操作，通过sys.dm_exec_query_profiles动态管理视图实时监控执行进度，比如查看扫描的行数、完成的百分比，以此推导整体耗时。

内存估算 #

• 官方基准计算：CCI重建的单线程内存需求约等于表中所有列的最大数据类型大小之和，多线程场景下需乘以当前并行度（DOP）。例如，若表有10个列，每个列最大类型大小总和为200字节，DOP为8，则单查询内存需求约为1.6KB（实际会有额外开销，需乘以1.5-2倍系数）。
• 实际监控验证：在测试环境中执行CCI重建，通过sys.dm_exec_query_memory_grants查看实际申请的内存授予量，以此作为大表的内存需求参考。同时需确保SQL Server的最大内存配置预留足够空间，避免重建时耗尽内存导致其他业务受影响。
• 并行度限制：若担心内存占用过高，可通过MAXDOP选项限制重建时的并行度，示例代码：

  CREATE CLUSTERED COLUMNSTORE INDEX [reporting_ClaimStatus2_cci] 
  ON [reporting].[ClaimStatus] 
  WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, MAXDOP = 4)
  GO
  

二、无需删除CCI的分区修复替代策略 #

1. 提前拆分空分区（预防优先） #

这是最根本的解决方案，避免出现分区已满才处理的被动局面：

• 每月初自动执行分区拆分操作，确保分区函数始终提前覆盖未来1-2个月的范围，此时最后一个分区为空，无需修改CCI即可直接执行：

  ALTER PARTITION SCHEME [PS_RPT1_Date_Month_Right] NEXT USED [FG_ClaimDetail_2024M01]
  GO
  ALTER PARTITION FUNCTION [PF_RPT1_Date_Month_Right]() SPLIT RANGE (N'2024-01-01T00:00:00.000')
  GO
  

• 可通过SQL Server代理作业定时执行该逻辑，避免人工遗漏。

2. 分区切换临时表拆分法 #

针对当前已出现数据写入问题的场景，可通过分区切换避免全表删除CCI：

• 步骤1：创建与原表结构完全一致的临时堆表（无CCI，分区方案与原表相同）：

  CREATE TABLE [reporting].[temp_ClaimStatus]
  (
      -- 复制原表所有列定义、约束
  ) ON [PS_RPT1_Date_Month_Right]([PartitionDateColumn])
  GO
  

• 步骤2：将原表最后一个存有数据的分区切换到临时表：

  DECLARE @LastPartitionNum INT = $partition.PF_RPT1_Date_Month_Right(GETDATE())
  ALTER TABLE [reporting].[ClaimStatus] SWITCH PARTITION @LastPartitionNum TO [reporting].[temp_ClaimStatus]
  GO
  

• 步骤3：此时原表最后一个分区为空，执行分区拆分：

  ALTER PARTITION SCHEME [PS_RPT1_Date_Month_Right] NEXT USED [FG_ClaimDetail_2023M12]
  GO
  ALTER PARTITION FUNCTION [PF_RPT1_Date_Month_Right]() SPLIT RANGE (N'2023-12-01T00:00:00.000')
  GO
  

• 步骤4：在临时表上创建CCI，再切换回原表的新分区：

  CREATE CLUSTERED COLUMNSTORE INDEX [temp_ClaimStatus_cci] ON [reporting].[temp_ClaimStatus]
  GO
  ALTER TABLE [reporting].[temp_ClaimStatus] SWITCH PARTITION @LastPartitionNum TO [reporting].[ClaimStatus] PARTITION @LastPartitionNum + 1
  GO
  

• 优势：仅需处理最后一个分区的数据，无需动全表的CCI，耗时和内存占用大幅降低。

3. 在线重建CCI（SQL Server 2016+） #

若必须删除重建CCI，可使用在线重建减少业务影响，但需注意大表仍会有较长耗时：

CREATE CLUSTERED COLUMNSTORE INDEX [reporting_ClaimStatus2_cci] 
ON [reporting].[ClaimStatus] 
WITH (DROP_EXISTING = ON, COMPRESSION_DELAY = 0, ONLINE = ON)
GO

• 在线重建期间，表可正常进行读操作，写操作会有短暂阻塞（取决于SQL Server版本）。

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