SQL Server 列存储索引退化实战：从 delta store 堆积到段消除失效

dbaai

一、具体的问题

一套报表库，事实表 fact_sales 大约 1.2 亿行，宽 40 多列。上线时为了压查询，在上面建了聚集列存储索引（CCI），当时效果很好：月度汇总从 46 秒降到 400 毫秒，占用空间从行存的 92GB 压到 38GB，谁都满意。

三个月后同一个报表变成 11 到 15 秒，而且越跑越慢。这期间没人改过 SQL，服务器没加负载，统计信息也刚更新过。执行计划看起来"正常"——走了 Columnstore Index Scan，没有回表，没有并行度问题。但 SET STATISTICS IO ON 一开就露了底：一次查询的 lob logical reads 高达一千多万页，实际扫描行数是估算的二十几倍。

更头疼的是加载。每天凌晨 ETL 分 60 批往表里灌大约 900 万行，每批 15 万行左右，结果加载耗时从 18 分钟涨到 42 分钟，而且表的磁盘占用从 24GB 一路涨到 38GB。

把三件事串起来看就明白了：CCI 退化通常不是"索引坏了"，而是数据进错了地方——行全堆在 delta store 里没被压缩，rowgroup 碎成几千个小块，段消除（segment elimination）彻底失效，查询只能把每个段都读一遍。

二、核心原理

要把这个问题拆开，得先记住 CCI 的三层结构。

第一层，rowgroup。 每个 rowgroup 最多 1,048,576 行。这是压缩的基本单位。

第二层，column segment。 每个 rowgroup 的每一列单独压成一个段，段头上带着这一列在本段内的最小值、最大值（sys.column_store_segments 里的 min_data_id / max_data_id）。

第三层，字典与压缩。 字符串列走字典编码，每段一份字典；数值列走位压缩。

关键就在第二层。查询带 WHERE order_date >= '2026-07-01' 时，优化器根本不去读数据页，而是先读段的 min/max 元数据：如果某段的 max 都小于 7 月 1 日，这一段整个跳过，一次 I/O 都不用。这叫段消除。一个健康的 CCI，日期过滤通常能砍掉九成以上的段。

段消除成立有个前提：每个 rowgroup 内部的数据在过滤列上必须相对集中。如果数据是按随机顺序写进去的，每个 rowgroup 的 min/max 都覆盖 2020 到 2026 整个区间，那么任何日期过滤都会和所有段 overlap，段消除率归零，退回全表扫。这就是"压缩率没变、空间没涨太多、查询却慢二十倍"的原因——CCI 的性能主要不来自压缩，来自跳读。

再说 delta store。CCI 表上的 INSERT 不是直接进压缩段，而是先写进一个行存的 B-tree（delta store）。只有当积攒到一个完整 rowgroup（1,048,576 行），或者手动 REORGANIZE，才会关闭并压缩成 CCI 段。这里有两个阈值必须记住：

• 单次批量插入至少 102,400 行，才可能直接生成压缩 rowgroup；
  
• 不足 102,400 行的批次，无论重复多少次，都留在 delta store 里等，只有当某个 delta rowgroup 攒满 1,048,576 行才转为压缩。
  

所以"每天 60 批、每批 15 万行"听起来不小，但如果加载脚本用了 SqlBulkCopy 的默认 batch size（几千行一批）、或者走的是逐行 INSERT，那 900 万行会全部趴在 delta store。查询时 SQL Server 必须把 delta store 的 B-tree 和压缩段做 UNION ALL 再合并——行存那部分没有段消除、没有列裁剪，性能差距在一到两个数量级。

第三件事是删除。CCI 上的 DELETE 不真删，只往 delete bitmap 里记一笔，deleted_rows 累加。查询要先读 bitmap 再过滤，deleted_rows 占比超过 10% 后收益明显下降，官方建议到 10% 就处理。UPDATE 更贵，实质是"删一笔 + 插一笔进 delta store"，所以高频更新的表上做 CCI，等于持续给 delta store 添货。

三、实例参考（动手步骤）

步骤 0：确认现状基线

先看 rowgroup 的分布，这一步能直接定位问题：

2. SELECT
3.     rg.state,                       -- COMPRESSED / OPEN / CLOSED
4.     COUNT(*)                        AS row_groups,
5.     SUM(rg.total_rows)              AS total_rows,
6.     SUM(rg.deleted_rows)            AS deleted_rows,
7.     SUM(rg.size_in_bytes)/1024/1024 AS mb,
8.     AVG(rg.total_rows)              AS avg_rows_per_rg,
9.     rg.trim_reason
10. FROM sys.dm_db_column_store_row_group_physical_stats('dbo.fact_sales') rg
11. GROUP BY rg.state, rg.trim_reason;

复制代码

健康的输出长这样：COMPRESSED 状态约 115 个 rowgroup，avg_rows_per_rg 接近 1,048,576，deleted_rows 接近 0，CLOSED/OPEN 为 0。

当时这套库的实际输出是：COMPRESSED 有 1,340 个 rowgroup，平均每个 6.8 万行；OPEN 状态 9 个，合计 912 万行还堆在 delta store；deleted_rows 占比 14.3%。三个指标全红。

再看段消除的空间跨度，判断能不能跳读：

2. SELECT TOP 20
3.     s.row_group_id,
4.     s.column_id,
5.     COL_NAME(ic.object_id, ic.column_id) AS col,
6.     s.min_data_id, s.max_data_id,
7.     s.on_disk_size/1024 AS kb
8. FROM sys.column_store_segments s
9. JOIN sys.partitions p
10.      ON p.hobt_id = s.hobt_id
11. JOIN sys.index_columns ic
12.      ON ic.object_id = p.object_id
13.     AND ic.index_id = p.index_id
14.     AND ic.column_id = s.column_id
15. WHERE p.object_id = OBJECT_ID('dbo.fact_sales')
16. ORDER BY s.on_disk_size DESC;

复制代码

看 min_data_id / max_data_id 的区间：如果每个 rowgroup 的日期列都横跨 2020-01 到 2026-10，说明数据是乱序写入的，段消除一定失效。

步骤 1：把 delta store 先清干净

在动手重建之前，先压缩 delta store——它不占额外空间，风险最低，而且能立刻收回一部分性能：

2. ALTER INDEX CCI_fact_sales ON dbo.fact_sales
3. REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

复制代码

这条命令把所有 OPEN / CLOSED 状态的 delta rowgroup 强制压缩。注意三点：它是单线程为主的操作、会产生不小的日志、期间对表有锁（不是全程排他，但 DDL 级别），所以放在业务低峰；AlwaysOn 环境下还要等日志传到辅助副本。大表建议先做完基线记录再执行，方便算耗时。

步骤 2：按过滤列重建成有序 CCI

数据乱序是根因，重建时必须让它有序。SQL Server 2022 起可以直接写 ORDER 子句：

2. -- SQL Server 2022 及以上
3. CREATE CLUSTERED COLUMNSTORE INDEX CCI_fact_sales
4. ON dbo.fact_sales
5. ORDER (order_date, customer_id)
6. WITH (DROP_EXISTING = ON, MAXDOP = 4);

复制代码

2016 到 2019 没有 ORDER 子句，替代办法是"按序插入 + REBUILD 合并"：把数据先按 order_date 排好写进一张暂存表，再一次性 INSERT ... SELECT 或 SWITCH 过来，使物理顺序与逻辑顺序一致，然后 REORGANIZE 压缩。实测这套办法能把段消除率做到和 2022 的 ORDER 差不多，前提是后续增量也守序。

重建要留意内存。CCI 构建的 memory grant 相当可观，资源调控器默认给单个工作负载组的授予上限可能不够，会看到 8657 一类"无法分配内存块"的报错。稳妥做法是临时给对应工作负载组放开 CAPACITY，或者把 MAXDOP 降到 2 到 4 换取更小的单次授予。

步骤 3：让增量按顺序、按够大的批量进来

这一步决定"三个月后又退化"会不会重演。加载脚本要满足两条：

• 每批至少 102,400 行，最好 50 万到 100 万行一批；
  
• 批内按聚簇顺序（这里是 order_date）排好再写。
  

2. -- 加载侧：按日期顺序、单批 >= 102400 行
3. INSERT INTO dbo.fact_sales WITH (TABLOCK)
4. SELECT * FROM dbo.stg_fact_sales_202610
5. ORDER BY order_date, customer_id;

复制代码

TABLOCK 在本例中让插入走最小日志、并有利于排序后顺写；如果表和分区已对齐，更好的做法是用 ALTER TABLE ... SWITCH PARTITION 整段换进来，几乎零成本且天然有序。

步骤 4：管住删除，别让它悄悄积累

删除的治理方向不是"少删"，而是"不删"：事实表按时间分区，清理老数据用 SWITCH 到归档表再 TRUNCATE，CCI 与分区对齐（2016+ 支持分区 CCI），这样删除成本是元数据级的。若做不到分区，就设一个巡检阈值，deleted_rows / total_rows > 10% 时按分区 REORGANIZE：

2. ALTER INDEX CCI_fact_sales ON dbo.fact_sales
3. REORGANIZE PARTITION = 71
4. WITH (COMPRESS_ALL_ROW_GROUPS = ON);

复制代码

治理前后对比

指标	治理前	治理后
delta store 行数	912 万	0
rowgroup 数量	1,340	118
平均每 rowgroup 行数	6.8 万	104 万
deleted_rows 占比	14.3%	0.2%
关键报表 P95	11.8 s	0.42 s
单次查询逻辑读	1240 万页	48 万页
日加载耗时	42 min	11 min
表占用空间	38 GB	24 GB

空间变小不是因为压缩率提高，而是因为上千个碎 rowgroup 的段头、字典副本被合并了——碎片本身就是成本。

四、实操检查清单

• [ ] 用 dm_db_column_store_row_group_physical_stats 拉基线：state、total_rows、deleted_rows、trim_reason 四项都要看；trim_reason 若大量是 BULKLOAD 之外的 MEMORY_LIMITATION，说明构建内存不够。
  
• [ ] 确认 avg_rows_per_rg 是否接近 1,048,576。低于 60 万就要怀疑碎片化或小批量导入。
  
• [ ] 确认 delta store 行数占全表比例。超过 5% 就是查询退化的直接原因，优先压缩。
  
• [ ] 用 sys.column_store_segments 抽查过滤列的 min/max 跨度。跨度普遍等于全局范围 = 段消除失效。
  
• [ ] 加载脚本的批次大小逐条核对，必须 ≥ 102,400 行；不够就先在暂存表攒够再写。
  
• [ ] 确认加载脚本有 ORDER BY（或源数据本身有序），无序写入等于每次重建都在白干。
  
• [ ] 检查 deleted_rows 占比，设 10% 阈值纳入日常巡检，超阈值按分区 REORGANIZE。
  
• [ ] 事实表是否按时间分区、CCI 是否与分区对齐。对齐后才能用 SWITCH 做低成本的进出和清理。
  
• [ ] 重建前确认磁盘剩余空间 ≥ 表大小 1.2 倍（DROP_EXISTING 期间新旧结构并存），并确认窗口足够。
  
• [ ] 重建的内存与并行度先算好：MAXDOP 2 到 4、必要时调整资源调控器 CAPACITY，避免中途 8657。
  

几个容易踩的坑

坑一：以为"批量插入"就一定进 CCI。 分批策略看得是单条语句的行数，不是一天的合计。60 批 × 15 万行若被驱动拆成几千行的多条语句，全部落 delta store，这就解释了为什么"每天灌 900 万行"却越灌越碎。

坑二：把 REORGANIZE 当成重建的替代品。 COMPRESS_ALL_ROW_GROUPS 只解决"没压缩"，不解决"没排序"。段里数据还是乱的，min/max 照样横跨全局，查询依旧慢。压缩管结构，排序管跳读，两件事。

坑三：以为 ORDER 子句能管住后续插入。 不管哪个版本，ORDER 只在构建那一刻生效。之后的插入完全按到达顺序落 rowgroup，三个月后照样乱。要么守序加载，要么把"定期重建有序 CCI"写进运维日历。

坑四：在 CCI 上跑高频 UPDATE。 每行更新都是 delete bitmap + delta store 插入，两个指标一起恶化。有高频更新的窄表，用非聚集列存索引（NCCI）挂在一张行存主表上更合适，NCCI 的 delta store 更新代价小得多；CCI 适合"一次写入、只读分析"的宽表。

坑五：SELECT * 打穿列裁剪。 列存的收益一半来自只读用到的列。报表里 SELECT * 或者视图里 sp_* 展开全部字段，40 列全读，比行存还慢。上线前用 SET STATISTICS IO 核一遍实际读取的列数。

坑六：只更新统计信息，不看 rowgroup。 CCI 表的统计信息由构建/重建时创建，日常 UPDATE STATISTICS ... WITH FULLSCAN 对 CCI 的基数估计帮助有限，真正决定扫描量的是 rowgroup 与段的元数据。方向找错，越修越远。

坑七：忽略加速的"数字好看"陷阱。 刚建完 CCI 那 400 毫秒，是"数据刚好有序、delta 为空"的理想态。别把它当基线，把它当目标——退化检测要盯 delta 行数与 avg_rows_per_rg 两个数字，而不是等业务报警。