SQL Server TempDB 争用排查实战：从 PAGELATCH 到多数据文件

dbaai

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：记录现状基线

2. -- CPU 核数（决定文件数上限）
3. SELECT cpu_count, hyperthread_ratio FROM sys.dm_os_sys_info;
5. -- 当前 tempdb 文件布局
6. SELECT file_id, name, size * 8 / 1024 AS size_mb,
7.        growth, is_percent_growth, physical_name
8. FROM tempdb.sys.database_files;
10. -- 全局等待统计快照（先清零再采样，别只看累计值）
11. DBCC SQLPERF('sys.dm_os_wait_stats', CLEAR);
12. WAITFOR DELAY '00:02:00';
13. SELECT wait_type, waiting_tasks_count,
14.        wait_time_ms / 1000.0 AS wait_s,
15.        max_wait_time_ms
16. FROM sys.dm_os_wait_stats
17. WHERE wait_type LIKE 'PAGELATCH%'
18.   AND waiting_tasks_count > 0
19. ORDER BY wait_time_ms DESC;

复制代码

步骤 1：确认争用资源指向 tempdb 分配页

2. -- 抓正在等待 PAGELATCH 的会话及其资源
3. SELECT r.session_id, r.wait_type, r.wait_resource,
4.        r.wait_time, r.blocking_session_id, t.text
5. FROM sys.dm_exec_requests r
6. CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
7. 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：找到谁在造临时对象

2. -- 各会话在 tempdb 里的空间占用（internal_object 主要是排序/spool）
3. SELECT r.session_id,
4.        t.internal_objects_alloc_page_count,
5.        t.internal_objects_dealloc_page_count,
6.        t.user_objects_alloc_page_count
7. FROM sys.dm_db_task_space_usage t
8. JOIN sys.dm_exec_requests r
9.   ON t.session_id = r.session_id AND t.request_id = r.request_id
10. WHERE t.internal_objects_alloc_page_count > 0
11. ORDER BY t.internal_objects_alloc_page_count DESC;

复制代码

这一步往往有意外收获：争用放大器常常是一两个写得烂的查询（比如 DISTINCT + ORDER BY 大结果集、嵌套 UDF 内建临时表），先治它们，等待数会断崖式下降。

步骤 3：确认是分配争用还是 version store 压力

2. -- tempdb 空间构成：user / internal / version store
3. SELECT file_id,
4.        user_objects_reserved_page_count / 128 AS user_mb,
5.        internal_objects_reserved_page_count / 128 AS internal_mb,
6.        version_store_reserved_page_count / 128 AS version_mb,
7.        unallocated_extent_page_count / 128 AS free_mb
8. FROM sys.dm_db_file_space_usage;

复制代码

• internal_mb 长期高企 → 查询产生的排序/spool，回到步骤 2 治 SQL；
  
• version_mb 高且只增不减 → 检查是否有长事务挡住版本清理：
  

2. SELECT TOP 5 session_id, host_name, program_name,
3.        last_request_start_time
4. FROM sys.dm_exec_sessions
5. WHERE is_user_process = 1
6.   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 核一个的较小值）。

2. -- 新增 7 个文件，与现有文件等大、同增长设置、同一 LUN
3. -- 一次性给足初始大小，避免自动增长抖动
4. ALTER DATABASE tempdb ADD FILE
5.   (NAME = tempdev2, FILENAME = 'D:\SQLData\tempdb2.ndf',
6.    SIZE = 8192MB, FILEGROWTH = 512MB),
7.   (NAME = tempdev3, FILENAME = 'D:\SQLData\tempdb3.ndf',
8.    SIZE = 8192MB, FILEGROWTH = 512MB);
9. -- ……以此类推到 tempdev8（脚本可用 sys.database_files 循环生成）
11. -- 把原有文件也固定到相同大小
12. ALTER DATABASE tempdb MODIFY FILE
13.   (NAME = tempdev, SIZE = 8192MB, FILEGROWTH = 512MB);

复制代码

三个硬性要求：

• 所有文件等大。SQL Server 按"剩余空间比例"在文件间轮转分配，大小悬殊会导致负载仍然偏向大文件；
  
• 同一磁盘卷、同一 RAID 层级。tempdb 文件间是并行分配的，放在不同性能层级的盘上，快盘等慢盘，反而引入新瓶颈；
  
• 重启实例生效。tempdb 每次启动重建，新文件布局要重启后才真正加载。改完 MODIFY FILE 后确认 sys.database_files 输出无误再安排重启窗口。
  

2. -- 重启后验证
3. SELECT file_id, name, size * 8 / 1024 AS size_mb
4. 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 平均等待	82ms	1.2ms
应用超时/小时	47	0
订单批量写入吞吐	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 对象，后者才轮到多文件。