怎样在SQL Server中利用扩展事件排查聚合溢出瓶颈？-SQL-PHP中文网

XEvent 中 sqlserver.sql_statement_completed 无法定位聚合溢出，因其仅提供聚合指标（如 duration、writes），不暴露算子级内存使用或 spill 行为；真正关键信息在执行计划 XML 的 SpillToTempDb="1" 等字段，须通过 query_post_execution_showplan 或 plan_profile 关联语句与计划。

怎样在sql server中利用扩展事件排查聚合溢出瓶颈？

扩展事件（XEvent）能精准捕获聚合溢出（如 hash_agg、sort 溢出到 tempdb）的细节，但默认会话不包含相关字段——必须显式启用 sqlserver.query_post_execution_showplan 或使用 query_plan_hash + physical_operator_name 关联执行计划，否则只能看到“慢”，看不到“为什么慢”。

为什么 `sqlserver.sql_statement_completed` 无法直接定位聚合溢出

该事件只提供 duration、cpu_time、logical_reads 等聚合指标，但不暴露执行计划中算子级内存使用或溢出行为。即使 writes 明显升高，你也无法区分是日志写入、数据页写入，还是 tempdb 的 sort/hash 溢出写入。

常见错误现象：sql_statement_completed 显示某查询 duration=8s、writes=240000，但 tempdb.sys.dm_db_task_space_usage 同期无显著增长 → 实际是 spill 写入被归类为 “internal object writes”，不计入用户对象统计
真正反映溢出的关键字段在执行计划 XML 中：SpillToTempDb="1"、EstimatedAvailableMemoryGrantKB、ActualMemoryGrantKB
若只监听 sql_batch_completed 或 rpc_completed，连语句粒度都丢失，更无法下钻到算子

必须启用的事件与动作组合

仅靠一个事件无法闭环诊断。你需要同时捕获运行时行为和计划结构，并建立两者关联：

主事件用 sqlserver.query_post_execution_showplan：它输出完整执行计划 XML，含所有 spill 标记和内存分配详情；但开销较高，**仅建议对目标会话或特定 database_id 过滤启用**
辅助事件用 sqlserver.query_post_execution_plan_profile（SQL Server 2019+）：提供逐算子实际行数、实际内存使用、spill 次数（spills 属性），比 showplan 更轻量且带运行时度量
必须附加动作：sqlserver.sql_text（语句原文）、sqlserver.query_plan_hash（用于跨事件去重和关联）、sqlserver.database_name（避免跨库混淆）
强烈建议加谓词：database_id = DB_ID('YourDB') 和 duration > 1000000（1秒以上），否则日志爆炸

如何从事件输出里识别真实溢出

拿到 query_post_execution_showplan 输出后，不要人工翻 XML。用以下 T-SQL 快速提取关键信息：

SELECT 
  event_data.value('(/event/@timestamp)[1]', 'datetime2') AS [timestamp],
  event_data.value('(/event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS [sql_text],
  event_data.value('(/event/data[@name="showplan_xml"]/value/query_plan/RelOp/@PhysicalOp)[1]', 'nvarchar(100)') AS [physical_op],
  event_data.value('(/event/data[@name="showplan_xml"]/value/query_plan/RelOp/@SpillToTempDb)[1]', 'bit') AS [spilled],
  event_data.value('(/event/data[@name="showplan_xml"]/value/query_plan/RelOp/@EstimateRows)[1]', 'float') AS [est_rows],
  event_data.value('(/event/data[@name="showplan_xml"]/value/query_plan/RelOp/@ActualRows)[1]', 'float') AS [actual_rows]
FROM sys.fn_xe_file_target_read_file('AggSpill*.xel', null, null, null)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS T
WHERE event_data.exist('/event/data[@name="showplan_xml"]') = 1
  AND event_data.value('(/event/data[@name="showplan_xml"]/value/query_plan/RelOp/@SpillToTempDb)[1]', 'bit') = 1;

注意 @SpillToTempDb 是布尔属性，不是文本值，必须用 bit 类型读取，否则返回 NULL
如果 physical_op 是 Hash Match 或 Sort，且 spilled=1，基本可确认是聚合/排序溢出
对比 est_rows 和 actual_rows：若后者远大于前者（比如 10 倍），说明统计信息陈旧，导致内存预估严重不足

生产环境部署的三个硬约束

哪怕只监听一个数据库，XEvent 也会因 plan XML 体积大而快速占满磁盘。上线前必须确认：

目标文件路径必须有足够空间，且**不能放在系统盘或 tempdb 所在卷**——溢出本身就在往 tempdb 写，再把日志也打过去会加剧 I/O 争用
必须设置 MAX_FILE_SIZE = 50（MB）和 MAX_ROLLOVER_FILES = 5，防止单个文件过大导致解析失败
绝对不要用 ring_buffer 目标来捕获 showplan：它的内存上限通常 5–10MB，一条大计划就撑爆，且重启即丢数据

最易被忽略的是：XML 计划中 SpillToTempDb="1" 只表示发生过溢出，但不告诉你溢出了多少次、每次多少页。要量化影响，得结合 sys.dm_exec_query_stats 查 total_spills 列，并用 query_hash 关联——这一步很多人卡在权限上：VIEW SERVER STATE 是必需的，但很多运维账号只有 VIEW DATABASE STATE，查不到跨库的 spill 统计。