SQL Server 内存授予排查实战：从 RESOURCE_SEMAPHORE 排队到排序溢出治理

dbaai

SQL Server 内存授予排查实战：从 RESOURCE_SEMAPHORE 排队到排序溢出治理

一、具体的问题

早上九点，报表开始跑批之后，一套 SQL Server 的前台查询集体变慢。DBA 第一反应通常是加内存、查 CPU，但翻监控发现两个反常的地方：内存使用率只有六成，CPU 也不高；可等待统计里 RESOURCE_SEMAPHORE 的等待时间一路上涨，几十个会话挂在同一个等待类型上，明明服务器还有好几个 G 的空闲内存，新查询却拿不到授权，最后有人收到 8645 超时错误。

另一类症状正好相反：某个大报表偶尔一跑十几分钟，拆开看发现排序没做完就往 tempdb 里吐数据，sort_warning 刷了几千条，磁盘队列跟着飙升——这就是授予不足导致的溢出（spill）。

这两类问题是同一枚硬币的两面：内存授予（Memory Grant）机制在"多给"和"少给"两边都会出事。这篇文章把两侧的排查和治理一次讲透。

二、核心原理

内存授予是 SQL Server 在查询真正执行前，给排序（Sort）、哈希（Hash Join/Aggregate）这类需要工作区的算子预留的一块内存。它的关键规则有三条：

第一，授予是串行排队，不是抢占。 整个实例有一个资源信号量，小查询走快速通道（一般 5MB 以内，GRADER 的小授权队列），大查询进入慢速队列排队。一个报错的估算让某个查询申请 8GB 授权，哪怕它实际只用了 200MB，剩下的 7.8GB 也会被"锁"在手里直到查询结束——期间所有需要大授权的查询都得排队。这就是为什么"内存明明够，查询却等授权"。

第二，授予大小来自估算行数和平均行宽，估错了两头受害。 估高了→过度授予，占用资源、拖累并发（今天的问题一）；估低了→授予不足，排序哈希装不下就溢出到 tempdb，一次溢出可能让耗时放大十倍以上（问题二）。参数嗅探、缺统计信息、表变量估算都是估算失真的常见来源。

第三，溢出不报警、授予不回收。 排序溢出只是安静地往 tempdb 写，性能劣化不会触发任何错误；过度授予也不会中途归还。所以这两类问题全靠主动巡检，不能等报错。

理解了这三条，排查路径就清晰了：先看信号量排队，再定位大户，然后判断它是"估多了"还是"估少了"。

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

步骤 0：确认现状基线

先看信号量整体状态和等待统计：

2. -- 信号量全景：目标内存 vs 已授予
3. SELECT resource_semaphore_id,
4.        target_memory_kb,
5.        max_target_memory_kb,
6.        total_granted_kb,
7.        total_used_kb,
8.        grantee_count,
9.        waiter_count,
10.        timeout_error_count
11. FROM sys.dm_exec_query_resource_semaphores;
13. -- 等待统计：排队是否成灾
14. SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
15. FROM sys.dm_os_wait_stats
16. WHERE wait_type IN ('RESOURCE_SEMAPHORE','RESOURCE_SEMAPHORE_QUERY_COMPILE','SOS_RESERVEDMEMBLOCKLIST');
18. -- 基线时间线：9:00-10:00 跑批窗口内，RESOURCE_SEMAPHORE 累计等待 41 分钟，waiter 峰值 23；另一侧总溢出读 6.1 万页

复制代码

两个信号各说明一侧的问题：waiter_count 大、等待时间长是排队成灾；timeout_error_count 增长意味着已有查询超时退出。

步骤 1：抓出授权大户

2. -- 当前正持授权的会话，按授予量排序
3. SELECT r.session_id,
4.        r.granted_memory_kb,
5.        r.used_memory_kb,
6.        r.ideal_memory_kb,
7.        r.requested_memory_kb,
8.        r.wait_time_ms,
9.        r.queue_id,
10.        t.text
11. FROM sys.dm_exec_query_memory_grants r
12. CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
13. ORDER BY r.granted_memory_kb DESC;

复制代码

判读要点：granted_memory_kb 与 used_memory_kb 差距悬殊（授予 8.4GB、实际用 320MB）就是过度授予；ideal_memory_kb 巨大则说明估算行数已经爆了。历史维度的常态化巡检用 sys.dm_exec_query_stats（SQL 2016 SP2 起）：

2. SELECT TOP 20
3.        qs.max_grant_kb / 1024 AS max_grant_mb,
4.        qs.avg_used_grant_kb,
5.        qs.max_ideal_grant_kb / 1024 AS max_ideal_mb,
6.        qs.total_spills, qs.last_spill_reads,
7.        SUBSTRING(st.text, 1, 120) AS sql_text
8. FROM sys.dm_exec_query_stats qs
9. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
10. ORDER BY qs.max_grant_kb DESC;

复制代码

一次典型发现：一个按月份汇总的报表，max_grant_mb = 8600，avg_used_grant_kb = 310000——把 8.6GB 授予攥在手里，实际只用 300MB，高峰期它一跑，后面排队 20 多个会话。

步骤 2：给溢出记账

2. -- 用扩展事件记录排序/哈希溢出事件
3. CREATE EVENT SESSION [track_spill] ON SERVER
4. ADD EVENT sqlserver.sort_warning (
5.     ACTION(sqlserver.session_id, sqlserver.sql_text, sqlserver.plan_handle)
6.     WHERE ( [sqlserver].[sql_text] LIKE N'%rpt_%' ) )
7. ADD TARGET package0.ring_buffer;
8. ALTER EVENT SESSION [track_spill] ON SERVER STATE = START;

复制代码

sort_warning 的 event_type=2（SpillToTempDb）就是要抓的形态。某报表查询每次执行溢出读 4 万多页，这就是它"有时 40 秒、有时 15 分钟"的真相。

步骤 3：分层治理

治估算是根。 对过度授予大户，先核对其计划里的估算行数与实际行数偏差（SET STATISTICS IO, TIME ON + 实际执行计划），缺统计补 UPDATE STATISTICS ... WITH FULLSCAN，表变量改成临时表或加 OPTION (RECOMPILE)。对溢出型查询，优先让索引替排序干活（建覆盖索引带 ORDER BY 列），或改写去掉不必要的 DISTINCT/窗口排序。

拦极端值是防。 对估算天然失真、又必须放行的大报表，用授予比例提示兜底：

2. SELECT ... FROM rpt_sales_detail
3. OPTION (MAX_GRANT_PERCENT = 5, MIN_GRANT_PERCENT = 1);

复制代码

再用 Resource Governor 把报表工作组的授予封顶，防止单个作业把信号量吃穿：

2. CREATE WORKLOAD GROUP rg_report
3. WITH (REQUEST_MAX_MEMORY_GRANT_PERCENT = 15);

复制代码

给队列留逃生门。 sp_configure 'query wait', 60（秒）让排队过久的查询快速失败而不是吊死，超时重试比雪崩好收场。SQL 2022 还可以吃内存授予反馈（Memory Grant Feedback），实例会按历史实际用量自动修正后续授予。

步骤 4：验证与固化

治理后再采一轮基线：RESOURCE_SEMAPHORE 累计等待从 41 分钟降到 1 分钟以内，waiter_count 峰值 23→2，总溢出读 6.1 万页→0，报表跑批从高峰串行改为可并行。把"按 max_grant_kb TOP20 + total_spills 巡检"固化成每日巡检作业，授予大户和溢出大户环比异常即告警。

四、实操检查清单

• [ ] 每日巡检 sys.dm_exec_query_resource_semaphores：waiter_count > 5 或 timeout_error_count 环比增长即告警
  
• [ ] 每日巡检 sys.dm_exec_query_stats 的 max_grant_kb TOP20：avg_used_grant_kb / avg_grant_kb < 20% 视为过度授予嫌疑
  
• [ ] 高峰时段查询批量变慢，先查 RESOURCE_SEMAPHORE 等待，再查 CPU/内存
  
• [ ] 收到 8645 错误（查询等待内存超时）当日报警处理，不要让用户"重试就好"
  
• [ ] sort_warning / hash_warning 扩展事件常开（低开销 ring_buffer），按周汇总溢出大户
  
• [ ] 新报表上线前跑一轮实际执行计划，核对估算行数与实际行数偏差超过 10 倍先修再上线
  
• [ ] 报表类查询统一评估是否需要 MAX_GRANT_PERCENT 封顶，批处理作业纳入 Resource Governor 工作组
  
• [ ] query wait 显式设置（不要用默认无限等），并告知开发超时重试约定
  
• [ ] 表变量参与大排序/哈希连接的语句逐一登记，改临时表或 RECOMPILE
  
• [ ] 升级 SQL 2022 后跟踪内存授予反馈生效情况，确认无反复抖动
  

几个容易踩的坑

坑一：把 RESOURCE_SEMAPHORE 等待当"内存不足"去加内存。 目标内存（target_memory_kb）没顶到 max_target，加内存只是把队列上限抬高，大户照旧霸占。先抓大户再谈扩容。

坑二：看见 spill 就无脑调大授权。 MIN_GRANT_PERCENT 硬抬授予只是把溢出换成排队。溢出的根子多在估算失真或索引给不了顺序，先修估算，提示只做兜底。

坑三：混淆本篇与另外两个 tempdb 问题。 分配页争用看 PAGELATCH_*，tempdb 空间爆满看版本存储与内部对象——本篇的授予溢出看 sort_warning 与 RESOURCE_SEMAPHORE，三者症状都是"tempdb 相关变慢"，账本完全不同。

坑四：query wait 保持默认无限等。 默认配置下排队查询会吊到锁超时或用户主动取消，雪崩时整池资源被死等占满，快失败反而是止血手段。

坑五：只看当前瞬时快照。 dm_exec_query_memory_grants 只能看到此刻正持授权的会话，大户跑得快就抓不到；历史统计必须配 dm_exec_query_stats 的 grant/spill 列一起看。

治理前后对比

指标	治理前	治理后
RESOURCE_SEMAPHORE 累计等待（跑批窗口）	41 分钟	<1 分钟
waiter_count 峰值	23	2
大户授予/实际用量	8.6GB / 300MB	380MB / 340MB
排序溢出读（周累计）	6.1 万页	0
8645 超时	每周 3~5 起	0（连续 60 天）
报表跑批 P95	14 分 20 秒	3 分 40 秒
前台查询高峰 P99	4.1 秒	260 毫秒