SQL Server 数据文件自动增长治理实战：从单次长 12GB 卡死 40 秒到固定增量与预分配

dbaai

SQL Server 数据文件自动增长治理实战：从单次长 12GB 卡死 40 秒到固定增量与预分配

一、具体的问题

一套 CRM 主库，SQL Server 2016，数据文件 118GB，完整恢复模式。症状很固定：每周三上午十点批量导入一开跑，全系统就"顿"一下——不是某一支查询慢，是所有写操作一起排住，短的十几秒，长的四十多秒，前端直接报超时。运维第一反应是磁盘不行，于是把库挪到全闪阵列，顿挫照旧；又怀疑是锁，抓了半天阻塞链，源头会话挂在 INSERT 上，可那支 INSERT 单独跑只要几百毫秒。

真正的证据在错误日志里，一行很容易被忽略的提示：

2. Autogrow of file 'CRM_Data' in database 'CRM' took 41237 milliseconds.
3. Consider using ALTER DATABASE to set a smaller FILEGROWTH for this file.

复制代码

四十一秒。再看文件设置：growth = 10，is_percent_growth = 1。118GB 的 10%，等于每次需要空间就一次性长 11.8GB。这 11.8GB 要在增长过程中被逐块写零，而写零这件事是同步的——在它做完之前，所有需要在这个文件里分配新页的会话都得等。所以现象才会是"全线卡"而不是"某条 SQL 慢"。

顺带补一刀：这个实例的即时文件初始化（Instant File Initialization，下称 IFI）没开，instant_file_initialization_enabled = 0。这意味着每次数据文件增长，十几 GB 的零都要老老实实写进磁盘。存储再快，也只是把四十一秒压到二十几秒，性质不变。

二、核心原理

第一，增长是同步且排他的。要求文件增长的那个操作，在整个增长期间持有锁或闩锁，其他要分配空间的会话只能排队。微软 KB 2574695 写得很直白：长时间的自动增长会导致查询性能问题，可能看到分配页闩锁上的长等待，等待类型是 PREEMPTIVE_OS_WRITEFILEGATHER。

第二，代价与增长量成正比，与库大小无关。写零的吞吐是相对固定的，长 1GB 是零点几秒，长 12GB 就是几十秒。所以"我的库很大所以增长慢"这个说法不准确，准确的说法是"我的增长幅度很大所以增长慢"。

第三，百分比增长是非线性的，这是老版本默认值最坑的地方。以前 model 库默认 10%，库 10GB 时长 1GB，还感觉不到；库长到 1TB 时一次就长 100GB，足够把业务卡上几分钟。SQL Server 2016 起默认值改成了初始 8MB、增长 64MB 固定值，就是被这个坑逼出来的。但改的只是"新建库的默认值"，存量库沿用旧设置，一直没人动。

第四，为什么从性能计数器上"看不见"。零写跑在 preemptive 模式里，线程状态显示 RUNNING，SQL Server 自己的 CPU 统计里不计入；磁盘计数器上看到的是持续写入，41 秒写 12GB 折合 300MB/s，对现代阵列不算峰值。于是 CPU 曲线平滑、IO 曲线平滑、业务在叫，三方对不上。这时候只有等待类型能说话。

第五，日志文件的规则和数据文件不一样。日志文件的增长无论 IFI 开不开都要写零，唯一的例外是 SQL Server 2022 之后 64MB 以内的日志增长可以走 IFI。所以"开了 IFI 就不怕增长"只对数据文件成立。

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

步骤 0：确认现状基线

2. SELECT DB_NAME(database_id) AS dbname,
3.        name,
4.        physical_name,
5.        size * 8 / 1024 AS size_mb,
6.        growth,
7.        is_percent_growth,
8.        CASE WHEN max_size = -1 THEN -1 ELSE max_size * 8 / 1024 END AS max_mb
9. FROM sys.master_files
10. WHERE database_id = DB_ID('CRM');

复制代码

读法只有两种：is_percent_growth = 1 时 growth 是百分数（这里是 10 就是 10%）；is_percent_growth = 0 时 growth 单位是 8KB 页，除以 128 才是 MB（65536 就是 64MB）。顺手把 max_size 也看一眼，-1 表示不限制，等于把"把盘写满"这个选项开着。

卷余量必须一起查，不然算不出还能预分配多少：

2. SELECT DISTINCT
3.        vs.volume_mount_point,
4.        vs.total_bytes / 1073741824 AS total_gb,
5.        vs.available_bytes / 1073741824 AS free_gb
6. FROM sys.master_files mf
7. CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs
8. WHERE mf.database_id = DB_ID('CRM');

复制代码

步骤 1：把历史增长事件挖出来

不要凭感觉，先拿数据。默认跟踪默认是开的，滚动的 5 个文件通常能覆盖几天到几周的窗口：

2. DECLARE @tr nvarchar(512) = (SELECT path FROM sys.traces WHERE is_default = 1);
4. SELECT StartTime,
5.        EventClass,                              -- 92 数据文件增长，93 日志文件增长
6.        CAST(IntegerData AS decimal(18,2)) / 1024 AS grow_mb,
7.        Duration / 1000.0 AS duration_sec,
8.        TextData
9. FROM sys.fn_trace_gettable(@tr, DEFAULT)
10. WHERE EventClass IN (92, 93)
11. ORDER BY StartTime DESC;

复制代码

IntegerData 单位是 KB，Duration 单位是毫秒。有些版本里 Duration 记为 0，那就以错误日志为准：

2. EXEC sp_readerrorlog 0, 1, N'Autogrow';
3. EXEC sp_readerrorlog 1, 1, N'Autogrow';

复制代码

再拿等待统计交叉验证，这一步是确认"增长就是卡顿的元凶"：

2. SELECT wait_type,
3.        waiting_tasks_count,
4.        wait_time_ms,
5.        wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_ms
6. FROM sys.dm_os_wait_stats
7. WHERE wait_type IN ('PREEMPTIVE_OS_WRITEFILEGATHER', 'PAGELATCH_EX', 'PAGELATCH_SH')
8. ORDER BY wait_time_ms DESC;

复制代码

判读标准：PREEMPTIVE_OS_WRITEFILEGATHER 的平均每次等待上百毫秒以上，且等待次数与增长事件的次数大致对得上，基本可以定案。这台机器上这两条曲线严格同步，四十一秒的增长对应一次四万毫秒级的等待。

步骤 2：确认 IFI 到底开没开

2. SELECT servicename, service_account, instant_file_initialization_enabled
3. FROM sys.dm_server_services
4. WHERE servicename LIKE N'SQL Server (%';

复制代码

返回 N 就是没开。开启方式是给数据库引擎服务账号授予 Windows 权限 "执行卷维护任务"（Perform Volume Maintenance Tasks），然后重启服务生效，SQL Server 2016 起安装向导里就有这个勾选项。

两个必须记住的例外：一是 TDE 加密的库，即使 IFI 显示 Y，数据文件增长仍要写零；二是日志文件不受 IFI 保护，2022 之前一律写零。

步骤 3：算出目标增量，而不是拍一个数

三个输入：日均增长量、卷余量、业务能忍受的单次停顿。本例日均增长 3.2GB、峰值小时 400MB，卷余量 780GB。结论是数据文件一次 1024MB，日志一次 512MB，并把数据文件一次性预分配到 200GB 覆盖未来半年。增量不宜设太大——设成 50GB 虽然次数少，但真长起来一次还是几十秒；也不宜设太小，几十 MB 一次会在文件里切出大量碎片，还会把日志的 VLF 切碎。

步骤 4：改设置（在线，不重启，不影响业务）

2. ALTER DATABASE CRM MODIFY FILE (NAME = N'CRM_Data', FILEGROWTH = 1024MB);
3. ALTER DATABASE CRM MODIFY FILE (NAME = N'CRM_Log',  FILEGROWTH = 512MB);
4. ALTER DATABASE CRM MODIFY FILE (NAME = N'CRM_Data', MAXSIZE = 900000MB);

复制代码

MAXSIZE 建议显式设置成略小于卷容量。不设上限的风险不是卡顿，是某个凌晨把盘写满——盘满之后这个实例上所有库一起进只读或报 1105，那才是真事故。

步骤 5：预分配，把成本一次性付在窗口里

2. ALTER DATABASE CRM MODIFY FILE (NAME = N'CRM_Data', SIZE = 204800MB);

复制代码

SIZE 只增不减，磁盘要预留够。注意预分配本身在 IFI 关闭时同样要写零，所以要放在维护窗口做，这是这台机器最后一次吃这个成本。之后若把 IFI 开起来，后续增长几乎瞬间完成。

治理前后对比

指标	治理前	治理后
增长设置	10% 百分比	数据 1024MB / 日志 512MB 固定
单次增长量	约 11.8GB	1GB
单次停顿	41 秒	预分配后 3 个月内 0 次
月增长次数	6 至 9 次	1 至 2 次
该类等待累计	约 320 秒/周	2 秒/周以内
业务超时工单	每周 1 至 2 起	0
磁盘占用	118GB	200GB（预占）

四、实操检查清单

• 所有用户库、model、tempdb 的 is_percent_growth 全部为 0，百分比增长一个不留。
  
• 换算后数据文件增量落在 512MB 至 2GB 区间，日志落在 512MB 至 1GB 区间。
  
• 每个文件都有显式 max_size，且小于所在卷可用空间，留出至少 20% 余量。
  
• 用 sys.dm_os_volume_stats 核对余量，要求大于未来 6 个月增长量加 30%。
  
• instant_file_initialization_enabled 为 Y；TDE 库单独标注"仍会写零"。
  
• AUTO_SHRINK、AUTO_CLOSE 全部关闭，收缩与增长的来回振荡是最坏组合。
  
• 错误日志里没有 5144（增长超时被取消）。5144 比 5145 严重，说明增长根本没完成。
  
• 完整恢复模式的库确认有定时日志备份，日志大小靠备份稳住，不靠增长。
  
• 新建库一律用显式脚本写死 FILEGROWTH。改 model 库没用——model 的文件属性除数据文件初始大小外都会被忽略，新库照样按默认 64MB 建。
  
• 变更后复查 sys.master_files，并把库名、文件名、旧值、新值写进变更单。
  
• 备份还原演练后复查目标实例的文件大小与增长设置，还原不会把 IFI 状态和增长计划带过去。
  
• AlwaysOn 辅助副本上同样执行一遍本清单，主库改完不等于备库改完。
  

几个容易踩的坑

以为改了数据文件就万事大吉，忘了日志。日志在完整恢复模式下靠备份定大小，不备份的话增长设置再合理也是无限的。

以为开了 IFI 就免疫。TDE 库照样写零，日志照样写零，Windows 上还要求服务账号真有那个权限并且重启过服务。

预分配完立刻用 DBCC SHRINKFILE 把文件"退回去"。收缩带来碎片，下次再长回来，纯负收益，还要再付一次写零。

以为只管用户库，不管 tempdb。tempdb 每次重启后都从初始大小重新长，初始值设成 8MB 的实例，开机后会在晨峰连续增长十几次，每次都是一次小停顿。tempdb 初始大小按实际峰值设足，多个数据文件大小与增量保持一致。

百分比增长会跟着数据一起搬家。备份还原、分离附加、可用性组同步，原设置都会带过去，新环境第一件事就是复查。

最后一句，增长这件事本身不产生任何业务价值，它只是在你没算准容量的时候替系统兜底。兜底的代价，最好安排在业务低峰付清。