SQL Server TempDB 争用排查实战：从 PAGELATCH 到多数据文件
SQL Server TempDB 争用排查实战：从 PAGELATCH 到多数据文件一、具体的问题
上周三上午十点，一套跑了两年的 SQL Server 2016（48 核，180 多个并发连接）突然全线卡顿：应用端报超时，DBA 登上去一看，CPU 才 35%，内存也够，磁盘队列正常——按常规思路根本找不到瓶颈在哪。
抓了一把等待统计，问题立刻露出来了：PAGELATCH_EX 的累计等待时间占了总等待的一半以上，平均单次等待 80 多毫秒。再一看等待资源，清一色是 2:1:1 和 2:1:133——tempdb 的 PFS 页和 SGAM 页。
这就是典型的 tempdb 分配页争用。所有会话建临时表、写表变量、排序溢出，都要去改这几页元数据，48 个核的机器上千军万马挤独木桥，CPU 却闲着。
这篇文章把 tempdb 争用的定位方法、根因分类和治理步骤完整过一遍，所有命令都可以直接照做。
二、核心原理
第一件事：分清三种"Latch"，方向完全不同。
等待类型含义指向
PAGELATCH_*内存中数据页上的闩锁最后一列是 hot page，常见 tempdb 分配页争用
PAGEIOLATCH_*从磁盘读页时的闩锁缺索引导致的大量物理读，跟 tempdb 无关
LATCH_*非 buffer 页的内部结构锁如 tempdb 元数据争用（系统表 latch）
看到 PAGELATCH 就怀疑 tempdb，是最常见的误判之一——用户库的 hot page（比如自增主键最后一页）也会产生 PAGELATCH_EX。判断标准就看等待资源格式：tempdb 永远是 dbid=2，也就是资源串 2:页文件号:页号 里的第一个 2。
第二件事：争用的是哪几页，为什么是它们。
tempdb 数据文件的头部有一组全局分配位图页，每个文件固定从这几页开始：
[*]PFS（Page Free Space）：页文件号 1，页号 1（2:1:1），记录每页大约 8096 字节范围内的空间使用情况；
[*]GAM：2:2:2，记录哪些区是空闲的；
[*]SGAM：2:1:3，记录哪些区是"半满混合区"，可从中分配单页。
在 SQL Server 2016 之前，同一文件内的页分配默认是"全比例填充"（proportional fill 对单个文件内的扩展不生效），新建对象都从文件头部开始找空页，于是所有并发分配都挤在文件最前面的 PFS/SGAM 上——争用的本质是元数据页的串行化修改，不是空间不够。这也是为什么给 tempdb 扩磁盘毫无用处。
第三件事：多数据文件为什么有效。
每个数据文件有自己独立的一组 PFS/GAM/SGAM。把 1 个文件拆成 8 个等大的文件，分配热点就分散到 8 组位图上。2016 起行为变了：文件内分配默认改为"轮转"（round-robin），一定程度上缓解了单文件热点，但大并发下多文件仍然是标准做法。
文件数量不是拍脑袋：官方建议数据文件数不超过 8 个，或每超过 8 个 CPU 核心加 1 个文件（取较小值）。网上流传的"必须 2 的幂""必须等于核数"都是误传。
第四件事：别忽略 version store。
开了 READ_COMMITTED_SNAPSHOT（RCSI）或快照隔离的库，旧版本行全存在 tempdb 的版本存储区里，这部分争用和空间压力跟临时对象无关，处理方式也不一样。
三、实例参考（动手步骤）
步骤 0：记录现状基线
-- CPU 核数（决定文件数上限）
SELECT cpu_count, hyperthread_ratio FROM sys.dm_os_sys_info;
-- 当前 tempdb 文件布局
SELECT file_id, name, size * 8 / 1024 AS size_mb,
growth, is_percent_growth, physical_name
FROM tempdb.sys.database_files;
-- 全局等待统计快照（先清零再采样，别只看累计值）
DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
WAITFOR DELAY '00:02:00';
SELECT wait_type, waiting_tasks_count,
wait_time_ms / 1000.0 AS wait_s,
max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGELATCH%'
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;
步骤 1：确认争用资源指向 tempdb 分配页
-- 抓正在等待 PAGELATCH 的会话及其资源
SELECT r.session_id, r.wait_type, r.wait_resource,
r.wait_time, r.blocking_session_id, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.wait_type LIKE 'PAGELATCH%';
输出里 wait_resource 形如 2:1:1 或 2:1:133。解读规则：数据库ID:文件ID:页号。数据库 2 是 tempdb；文件 1 是第一个数据文件；页号 1 是 PFS，2:2:2 是 GAM，2:1:3 是 SGAM。如果资源是 2:1:133 这类大页号，说明争的是首个文件的普通数据页（比如临时对象目录页），处理思路相同——分散文件。
步骤 2：找到谁在造临时对象
-- 各会话在 tempdb 里的空间占用（internal_object 主要是排序/spool）
SELECT r.session_id,
t.internal_objects_alloc_page_count,
t.internal_objects_dealloc_page_count,
t.user_objects_alloc_page_count
FROM sys.dm_db_task_space_usage t
JOIN sys.dm_exec_requests r
ON t.session_id = r.session_id AND t.request_id = r.request_id
WHERE t.internal_objects_alloc_page_count > 0
ORDER BY t.internal_objects_alloc_page_count DESC;
这一步往往有意外收获：争用放大器常常是一两个写得烂的查询（比如 DISTINCT + ORDER BY 大结果集、嵌套 UDF 内建临时表），先治它们，等待数会断崖式下降。
步骤 3：确认是分配争用还是 version store 压力
-- tempdb 空间构成：user / internal / version store
SELECT file_id,
user_objects_reserved_page_count / 128 AS user_mb,
internal_objects_reserved_page_count / 128 AS internal_mb,
version_store_reserved_page_count / 128 AS version_mb,
unallocated_extent_page_count / 128 AS free_mb
FROM sys.dm_db_file_space_usage;
[*]internal_mb 长期高企 → 查询产生的排序/spool，回到步骤 2 治 SQL；
[*]version_mb 高且只增不减 → 检查是否有长事务挡住版本清理：
SELECT TOP 5 session_id, host_name, program_name,
last_request_start_time
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND EXISTS (SELECT 1 FROM sys.dm_tran_active_snapshot_database_transactions);
步骤 4：实施多数据文件改造
确认根因是分配页争用（资源集中在 2:1:1/2:2:2/2:1:3）后动手。按 48 核，文件数上限取 8（不超过 8 或每 8 核一个的较小值）。
-- 新增 7 个文件，与现有文件等大、同增长设置、同一 LUN
-- 一次性给足初始大小，避免自动增长抖动
ALTER DATABASE tempdb ADD FILE
(NAME = tempdev2, FILENAME = 'D:\SQLData\tempdb2.ndf',
SIZE = 8192MB, FILEGROWTH = 512MB),
(NAME = tempdev3, FILENAME = 'D:\SQLData\tempdb3.ndf',
SIZE = 8192MB, FILEGROWTH = 512MB);
-- ……以此类推到 tempdev8（脚本可用 sys.database_files 循环生成）
-- 把原有文件也固定到相同大小
ALTER DATABASE tempdb MODIFY FILE
(NAME = tempdev, SIZE = 8192MB, FILEGROWTH = 512MB);
三个硬性要求：
[*]所有文件等大。SQL Server 按"剩余空间比例"在文件间轮转分配，大小悬殊会导致负载仍然偏向大文件；
[*]同一磁盘卷、同一 RAID 层级。tempdb 文件间是并行分配的，放在不同性能层级的盘上，快盘等慢盘，反而引入新瓶颈；
[*]重启实例生效。tempdb 每次启动重建，新文件布局要重启后才真正加载。改完 MODIFY FILE 后确认 sys.database_files 输出无误再安排重启窗口。
-- 重启后验证
SELECT file_id, name, size * 8 / 1024 AS size_mb
FROM tempdb.sys.database_files WHERE type = 0;
步骤 5：减少临时对象分配本身（治本）
多文件是"分流"，减少分配次数才是"减量"：
[*]表变量优先（有统计信息更适合大数据集的场景才用临时表），且在存储过程内创建的临时对象，过程缓存复用时可跳过部分分配；
[*]消灭循环体里 CREATE TABLE / DROP TABLE 的写法，改在循环外建一次；
[*]索引补齐，减少排序溢出到 tempdb 的量（sort_warden 类 internal 对象）。
治理前后对比
这套方案在该实例上的实际效果（同口径：连续 5 个工作日上午高峰）：
指标治理前（1 文件）治理后（8 等大文件）
PAGELATCH_EX 等待占比54%3%
PAGELATCH_EX 平均等待82ms1.2ms
应用超时/小时470
订单批量写入吞吐12000 行/分41000 行/分
CPU 利用率（同时段）35%71%（活儿真正跑起来了）
注意最后一行：CPU 从"闲"变"忙"才是恢复健康的标志——之前的低 CPU 是假象，大量会话在排队等分配页。
四、实操检查清单
[*]等待类型先分类：PAGELATCH_* 才是页争用，PAGEIOLATCH_* 去查索引和 IO，LATCH_* 是另一类问题，三者治理方向不同；
[*]用 wait_resource 确认 dbid=2：2:1:1（PFS）、2:2:2（GAM）、2:1:3（SGAM）才是分配页争用的铁证；
[*]采样用"清零 + 固定窗口"取增量，累计值里有几年前的陈账，会误导判断；
[*]先用 sys.dm_db_task_space_usage 找出 tempdb 大户 SQL，治 SQL 常常比加文件见效更快；
[*]查 sys.dm_db_file_space_usage 区分 internal（查询溢出）与 version store（RCSI）压力，后者要找长事务而不是加文件；
[*]数据文件数 ≤ 8，或每超 8 个核加 1 个，取较小值；不必凑 2 的幂；
[*]所有文件等大、同一卷、固定增长（MB 而非 %）、初始大小一次给足；
[*]改完必须重启实例，重启前核对 tempdb.sys.database_files；
[*]确认磁盘开启即时文件初始化（ Instant File Initialization ），重启时 tempdb 重建才不会卡几十分钟；
[*]改造后连续观察三天 PAGELATCH_EX 等待占比与平均等待，确认回落再结案。
几个容易踩的坑
1. 一看到 PAGELATCH 就狂加文件。 用户库的 hot page（自增主键最后一页、热点索引）同样报 PAGELATCH_EX，资源串 dbid 不是 2，加 tempdb 文件毫无作用。先看 wait_resource 的第一个数字。
2. 盲目把文件数加到 CPU 核数。 64 核加 64 个文件的实例并不少见，结果元数据争用换成了文件间轮转的额外开销，且每次重启重建 64 个大文件拖长恢复时间。记住上限：8 个，或每 8 核一个，取小。
3. 文件大小不一致。 加了 7 个 2GB 的小文件，原有 1 个 500GB，按比例轮转的结果是几乎所有分配仍然落在大文件上，热点纹丝不动。等大是硬要求，宁可先 SHRINK 再等分。
4. 2016+ 还在用 trace flag 1117/1118。 这两个 trace flag 在 2016 起已默认生效，启动参数里挂着只会徒增困惑；真正遗留的是老版本实例，遇到 2014 及以下再考虑。
5. 忽视 autogrow 设置。 percent growth 的 tempdb 文件在 800GB 规模下一次自动增长要几个 GB，并发下多个文件同时长，I/O 抖动足以拖垮整个实例。固定 MB 增长 + 初始给足，是唯一稳的做法。
6. 把 tempdb 争用当成空间报警处理。 收到 tempdb 满的告警就扩盘，是运维侧最自然的反应，但争用型实例磁盘明明是空的。空间问题和分配争用是两条线：前者查 version store/大查询/internal 对象，后者才轮到多文件。
页:
[1]