近一亿行历史表改结构:不要原地 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 DROPCLEANBUFFERSDBCC FREEPROCCACHEmax 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。
处理顺序是:
- 把 SQL Server 拉起来。
- 立刻
sp_configure 'max server memory (MB)', 90000并RECONFIGURE。当时读到的value_in_use确实是 9521。 - 建大索引之前先停掉这个作业。点查、小批量插入扛得住这 20 秒;并行排序扛不住。
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 看已分配页更准确。
要把空间还给磁盘,分两步:
DBCC SHRINKFILE (N'数据文件', TRUNCATEONLY)只砍文件尾部的空闲,不搬页。这次从大约 453GB 收到 422GB,十秒级完成。- 旧表留下的空洞在文件中间,尾部还堆着新拷的聚集索引。要指定目标大小再收缩,SQL Server 才会把尾部的页搬到前面。目标不要贴着已用空间,留大约 8GB,避免马上又自增长。这次从 422GB 收到 365GB 左右,大约 16 分钟,累计写了上千万页。
收缩之后聚集索引碎片是 99.99%。按主键查一行仍然走 B 树;按日期做大范围扫描会明显变差。这张历史表很少查,所以没有接着重建聚集索引。如果以后扫描变多,再在维护窗口里重建,并且同样避开那个会临时下调最大内存的作业。
可复用的顺序
- 先看校验程序比的是列名还是列序号。序号比对就不能只在表尾加列。
decimal只改小数位也要重写行。不要对上亿行做多次ALTER COLUMN。- 新表一次拷贝,按主键分批,SIMPLE 下每批提交。只保留迁移真正用到的聚集主键。
- 文件增长是 1MB 时,先一次性预分配,再把增长改成百 MB 或 1GB。
- 建大索引或收缩之前,确认没有作业会把
max server memory临时打到很低。实例如果因此退出,启动后先把配置改回去。 - 终止
CREATE INDEX是安全的,它会回滚;不要以为断线等于索引建了一半还留着。 - SSMS 里改过脚本后,关掉旧窗口再打开文件。
- 删大表之后,
TRUNCATEONLY能还多少就先还多少。必须搬页时再指定目标大小,并接受聚集索引会碎。
超实用的小助手公众号 · 小工具与随笔 |
超实用的小助手小程序·工具:健康运动、图文、挪车 |
百科学客小游戏·玩着学:亲子、学习、益智 |
| 前端 | HTML5/CSS3/JS、jQuery、Ajax、Layui、Vue 3;小程序/安卓/苹果/鸿蒙(uni-app)、小游戏 |
| 后端 | C# / .NET / ASP.NET Core(Dapper)、经典 ASP.NET;Java / Spring Boot;Python FastAPI |
| 数据库 | SQL Server / MySQL;了解 Postgres/Redis |
| 运维 | IIS、Linux(nginx/systemd)、DNS/证书、测产隔离、增量发布、小程序 CI |
| AI应用 | Cursor、WorkBuddy、Qoder、Trae、Agent/MCP |
| 兴趣 | 文学艺术;数码电子、拆装电器 |
| 态度 | 乐观豁达,少抱怨非怯懦;体谅宽容,欣赏阳春白雪与下里巴人;多想办法 |

超实用的小助手
超实用的小助手
百科学客
浙公网安备 33010602011771号