近一亿行历史表改结构:不要原地 ALTER,建索引时也别砍内存

AI 生成声明:本文内容由 AI 生成或经 AI 辅助完成,请注意甄别。

业务库和历史库各有一张同名大表。历史库里大约 99,569,866 行,聚集索引约 16GB,另外 5 个非聚集索引合计约 37GB。没有压缩,也没有分区。环境是 SQL Server 2016 Enterprise,历史库恢复模式 SIMPLE。

迁移程序在搬数之前会按 列序号 比对两边的列名、类型、精度、小数位和是否可空。序号不一致就判定结构不同。这次业务库比历史库多了两列,另外四个 decimal 的小数位更宽。目标是让历史库能接住后续迁移,同时不要把这张很少被查询的表重写成一次长时间锁表。

差异本身不大,改法却很贵

业务库在原有调价列前面插入了两列:

  • decimal(18,4) NOT NULL DEFAULT (0.0)
  • 另一列同样是 decimal(18,4) NOT NULL DEFAULT (0.0)

另外四个金额列要从 (18,1) 或 (18,2) 加宽到 (18,4)。精度都是 18,存储长度仍是 9 字节。小数位变了,每一行里按比例保存的整数都要重算,所以这不是只改元数据的操作。

ALTER TABLE ADD 只能把新列加到表尾。迁移校验按序号比对,加在表尾仍然对不上。四次 ALTER COLUMN 则会把聚集索引,以及 INCLUDE 了这些列的大索引,各重写好几遍,并长时间拿架构锁。

实际采用的做法

建一张列顺序已经对齐的新表,只建聚集主键,不建非聚集索引。按主键每批 50 万行插入,CAST 到新的小数位,两个新列写 0.0。打开 IDENTITY_INSERT,拷完后用 DBCC CHECKIDENT 把种子设成当前最大 ID。

历史库很少查这张表,五个非聚集索引大约 37GB,建它们的收益抵不上排序和写盘的风险,所以最后没有建。迁移按主键分批,点查仍然走聚集索引。

切换放在一个短事务里:先对旧表加独占锁,再补一次拷贝开始后新出现的 ID,行数一致才 sp_rename。旧表留着,确认之后再删。

拷贝大约 8 分钟跑完 9957 万行。每批 50 万行、按主键顺序追加,SIMPLE 下每批提交后日志可以复用。

经验 1:文件已满时,先改增长,再谈拷贝

动手时数据文件已经用满,自增长还是 1MB。如果边拷边按 1MB 增长,几十 GB 的写入会变成几万次文件扩张。

先把文件一次加大约 60GB,并把 FILEGROWTH 改成 1024MB。磁盘剩余空间要按「新表 + 可能的日志」一起看。这次盘上大约只剩 100GB,所以没有采用一条巨大的 INSERT...SELECT。万一最小日志没有生效,单事务会把日志和数据一起写满。

重跑脚本时不要再按「剩余不足 60GB 就再加大一次」处理。文件增长已经改大之后,拷贝本身会吃掉预留空间,再次加大可能把磁盘撑满。

经验 2:建宽索引时,不要同时把 max server memory 打到十分之一

第一次建非聚集索引时,第二个宽索引(按日期做键,INCLUDE 了一长串金额列)刚开始,实例就退出了。SSMS 看到的是:

消息 109,级别 20
在从服务器接收结果时发生传输级错误。
(provider: Shared Memory Provider, error: 0 - 管道已结束。)

这不是索引脚本写坏了数据,而是 SQL Server 进程没了。错误日志里同一时刻有:

  • DBCC DROPCLEANBUFFERS
  • DBCC FREEPROCCACHE
  • max server memory (MB) 从 90000 改到大约 物理内存的 10%(这次是 9521)
  • 随后大量 701:insufficient system memory in resource pool 'default',内部资源池也同样失败
  • SQL Server shutdown has been initiated

原因是一个定时「自释放内存」的过程:先清干净缓存和计划缓存,把最大内存改成物理内存的十分之一,等 20 秒,再改回原来的值。宽索引的并行排序还拿着很大的内存授予,目标突然从 90GB 掉到 9.5GB,系统任务都起不来,实例退出。恢复语句来不及执行,配置就停在 9521。服务不会自己回到 90000。

处理顺序是:

  1. 把 SQL Server 拉起来。
  2. 立刻 sp_configure 'max server memory (MB)', 90000 并 RECONFIGURE。当时读到的 value_in_use 确实是 9521。
  3. 建大索引之前先停掉这个作业。点查、小批量插入扛得住这 20 秒;并行排序扛不住。
  4. CREATE INDEX 是事务性的。连接断开或 KILL 之后,这个索引会回滚,已经拷进新表的行还在。

后来决定历史库不建这些非聚集索引,作业就可以重新打开。如果仍然要建,作业必须全程停着,并且用 MAXDOP = 1,让排序空间不够时落盘,而不是一次要几十 GB 内存。

经验 3:SSMS 执行的是窗口里的文本,不是磁盘上的文件

脚本改成「删除已有非聚集索引、不再创建」之后,重新执行仍然打出「创建某个宽索引」。查 sys.dm_exec_requests,会话里的语句就是旧的 CREATE NONCLUSTERED INDEX。

磁盘上的文件已经没有这条语句。SSMS 查询窗口还开着修改前的内容,执行的是窗口,不是刚才保存的文件。以消息文本为准。发现不对就 KILL 该会话,关掉窗口,从磁盘重新打开再跑。

已完成的步骤要写成可重入:新表已存在就跳过建表,拷贝从新表当前最大 ID 继续,已改名的对象不要再改一次。

经验 4:删旧表不会把空间还给操作系统

切换完成后旧表仍占大约 52GB。DROP TABLE 很快,空间先回到数据文件内部。FILEPROPERTY(..., 'SpaceUsed') 可能还停在旧数字,用 sys.allocation_units 看已分配页更准确。

要把空间还给磁盘,分两步:

  1. DBCC SHRINKFILE (N'数据文件', TRUNCATEONLY) 只砍文件尾部的空闲,不搬页。这次从大约 453GB 收到 422GB,十秒级完成。
  2. 旧表留下的空洞在文件中间,尾部还堆着新拷的聚集索引。要指定目标大小再收缩,SQL Server 才会把尾部的页搬到前面。目标不要贴着已用空间,留大约 8GB,避免马上又自增长。这次从 422GB 收到 365GB 左右,大约 16 分钟,累计写了上千万页。

收缩之后聚集索引碎片是 99.99%。按主键查一行仍然走 B 树;按日期做大范围扫描会明显变差。这张历史表很少查,所以没有接着重建聚集索引。如果以后扫描变多,再在维护窗口里重建,并且同样避开那个会临时下调最大内存的作业。

可复用的顺序

  1. 先看校验程序比的是列名还是列序号。序号比对就不能只在表尾加列。
  2. decimal 只改小数位也要重写行。不要对上亿行做多次 ALTER COLUMN。
  3. 新表一次拷贝,按主键分批,SIMPLE 下每批提交。只保留迁移真正用到的聚集主键。
  4. 文件增长是 1MB 时,先一次性预分配,再把增长改成百 MB 或 1GB。
  5. 建大索引或收缩之前,确认没有作业会把 max server memory 临时打到很低。实例如果因此退出,启动后先把配置改回去。
  6. 终止 CREATE INDEX 是安全的,它会回滚;不要以为断线等于索引建了一半还留着。
  7. SSMS 里改过脚本后,关掉旧窗口再打开文件。
  8. 删大表之后,TRUNCATEONLY 能还多少就先还多少。必须搬页时再指定目标大小,并接受聚集索引会碎。
posted @ 2026-11-17 09:03  超小助手  阅读(13)  评论(0)    收藏  举报