编程语言研究书库
SQL Server XML 与 JSON 实例

内建 XML(中)— 扩展事件、死锁图与执行计划里的 CONVERT_IMPLICIT

内建 XML(中)— 扩展事件、死锁图与执行计划里的 CONVERT_IMPLICIT

这一章讲什么: 原书第 11 章的中间三个配方,是「DBA 日常排障三件套」: 扩展事件(替代 SQL Trace 的观测系统,输出 XML)、死锁图(system_health 里 现成存着的死锁报告,也是 XML)、执行计划(XML,能查询的 XML)。 三个配方的共同句式:SQL Server 把诊断信息写成 XML → 你用第 04 章学的 shredding 把它读回来。会拆 XML 的 DBA 和不会的,差的就是这三样工具。

1. 顶层全景:三件套的分工

  配方 11-3 扩展事件   → 主动观测:自己定义抓什么事件,输出 .xel(XML),拆成报告
  配方 11-4 死锁图      → 被动收获:system_health 默认就在抓死锁,拆出来分析
  配方 11-5 执行计划    → 主动扫描:计划缓存里的计划全是 XML,一条 XPath 批量找病

  图说:一个要建、一个现成、一个要扫;读写 XML 是三者共用的钥匙。

2. 核心原理

2.1 扩展事件:四件套与一个五步走的事件管道

解决什么问题: 老工具(SQL Trace/Profiler)抓得粗、开销大、事后难分析。扩展事件(XEvents)从 2008 年起内建,设计目标四条:最小开销、精确过滤、结构化输出、引擎级集成1。

会话的四个零件,各管一段2:

零件管什么例子
事件 Event引擎里发生了什么(固定 schema 的字段)sql_statement_completed、lock_acquired、wait_info
动作 Action事件触发时附加取的上下文sql_text、database_name、client_app_name
谓词 Predicate引擎级过滤,不合格的事件根本不被物化client_app_name 排除 SSMS/SQLAgent
目标 Target数据落到哪ring_buffer(内存环形缓冲)、event_file(磁盘 .xel)

事件的管道五步走:引擎组件触发事件 → 谓词立刻求值(失败即止) → 收集动作 → 异步分发到目标 → 目标存储/聚合3。谓词在引擎里先过滤是 XEvents 和 SQL Trace 的本质区别(Trace 是抓完再筛),也是它开销低的结构性原因4。

目标二选一的经验法则: ring_buffer 在内存里、重启即失,适合临时排障;event_file 落盘、生产监控的默认选择5。会话可随时 ALTER EVENT SESSION … STATE = START/STOP,不用重启实例6。

2.2 拆 XEvent 的 XML:三段式与自定义报告

怎么做,三段式:

-- 段一:找到 .xel 文件路径(目标配置本身是 XML)
SELECT @EventFile = CAST(t.target_data AS XML).value('(EventFileTarget/File/@name)[1]', 'VARCHAR(MAX)')
FROM sys.dm_xe_sessions s JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE t.target_name = 'event_file' AND s.name = 'XEvent_SQL_Requests';

-- 段二:读出文件里的每条事件(XML 形态)
SELECT CAST(event_data AS XML) FROM sys.fn_xe_file_target_read_file(@EventFile, NULL, NULL, NULL);

-- 段三: shredding ——事件 XML 分 data 和 action 两个块
SELECT c.value('(/event/@timestamp)[1]', 'datetime2(0)') AS utc_timestamp,
       c.value('(action[@name="session_nt_username"]/value)[1]', 'varchar(50)') AS [user],
       c.value('(action[@name="database_name"]/value)[1]', 'varchar(50)') AS db,
       c.value('(data[@name="statement"]/value)[1]', 'varchar(max)') AS [Statement]
FROM 读出的行集
CROSS APPLY event_data.nodes('/event[@timestamp > sql:variable("@date")]') AS t(c);

两个高频技巧书里单列7:

  1. 想加列?换 XPath 里的 @name 就行——拿 statement 的表达式 data[@name="statement"]/value 改三个地方:@name="offset"、SQL 类型改 int、别名改 offset。action 块的列同理,把 data 换成 action。
  2. 时间戳一律 UTC,转本地要 SWITCHOFFSET + DATEPART(TZOFFSET) + AT TIME ZONE 组合(书里给了美国东部时区的完整表达式,含夏令时)。

谓词里还能用 sql:variable()(13 章军规原样复用)——时间过滤放在 nodes 的谓词里,只拆最近一小时内的事件8。

2.3 死锁图:system_health 的免费午餐

解决什么问题: 死锁(1205 错误)的难点不是发生,而是「静悄悄地发生」——等你听说,现场已经没了9。

关键事实: system_health 会话自 2008 起默认安装、默认运行,2016 起其中还带死锁捕获——零配置,每次死锁的完整报告(XML)已经在磁盘上了10。

怎么拿(配方 11-10 的两段式): 用 sys.dm_os_server_diagnostics_log_configurations 拿到日志目录,拼 system_health_*.xel,用 fn_xe_file_target_read_file 读,WHERE object_name = 'xml_deadlock_report' 过滤死锁事件,取出 <deadlock> 子树就是死锁图11。

怎么分析: 死锁图里有涉事的事务、语句、锁资源;书里再进一步——executionStack/frame 元素上的 sqlhandle、stmtstart、stmtend 三个属性,拿到后喂给 sys.dm_exec_text_query_plan(sql_handle, stmt_start_offset, stmt_end_offset),就能把死锁里那条具体语句的执行计划翻出来,看访问路径和锁行为,定位根因12。这一步是「拆 XML → 反查引擎 → 再拆 XML」的连环技。

2.4 CONVERT_IMPLICIT:执行计划 XML 的实战扫描

解决什么问题: 查询「在我机器上好好的」,上了生产就慢——经典的 ad-hoc 参数化查询隐式转换问题。

根因链(全书最精彩的一段推理,值得完整复述):

  1. C# 只有一个 string 类型,等价于 nvarchar(max);AddWithValue() 不给参数显式指定 SQL 类型,"GSK" 就以 nvarchar(3) 发出去13;
  2. 列是 varchar(50)。数据类型优先级:nvarchar 高于 varchar,所以引擎必须把列转成 nvarchar 再比——「这不是 bug,是 SQL Server 按文档规则行事」14;
  3. 列被转换 = 索引作废(同 WHERE YEAR(createDate)=2026 让索引失效一个道理——索引建在列上,不是建在列的函数上)15;
  4. SSMS 里测的时候参数是手写的 varchar(50),计划是漂亮的索引 seek(123 逻辑读,231 毫秒,15,437 行);从 C# 跑就变成全表扫描,DBA 收到阻塞告警16。

修复: C# 侧 Parameters.Add(new SqlParameter("@sp", SqlDbType.VarChar, 长度)) 显式声明类型;或改用存储过程(过程参数声明为 varchar 后,入参按声明类型处理,天然免疫)17。作者补刀:这是「最常见也最能预防的性能问题」,他的客户里天天发生,十有八九是 varchar/nvarchar 撞车18。

怎么批量发现(配方 11-13 的杀招): 执行计划缓存里的计划都是 XML,直接对它跑 XPath:

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS pln)
SELECT r.session_id, r.command, …
FROM sys.dm_exec_requests r
CROSS APPLY query_plan.nodes('//pln:Warnings/pln:PlanAffectingConvert[1][contains(@Expression,"CONVERT_IMPLICIT")]') AS vt(c)
WHERE c.value('../../../pln:QueryPlan/pln:RelOp/pln:OutputList/pln:ColumnReference[1]/@Schema','varchar(50)') != '[sys]';

一句话:在所有计划的所有 Warnings 节点里,找 PlanAffectingConvert 元素的 Expression 属性含 "CONVERT_IMPLICIT" 的——正在跑的查询里谁在做隐式转换,一条 SQL 全列出来19。

3. 作者的判断与证据

架构事实(官方级): XEvents 四件套与五道管道、ring_buffer/event_file 的取舍、system_health 默认捕获死锁、时间戳 UTC——都有产品行为或文档支撑3510。

有完整证据链的案例: CONVERT_IMPLICIT 一段(同一条 SQL 在 SSMS vs C# 的两张计划截图、计划 XML 里的 ParameterList 证据、修复代码)是全书叙事最完整的故障案例——推理链的每一环都可在读者环境复验1316。

经验之谈: 「扩展事件设计得当,开销可忽略,生产可安全常开」20;「CONVERT_IMPLICIT 是最常见且最能预防的性能杀手」18;「别用 AddWithValue()」13。这些和微软官方的现行建议方向一致(补充,查阅类判断不在书里:ADO.NET 文档已标注 AddWithValue 影响计划缓存)。

4. 边界与局限

  • XEvent XML 的 schema 随事件而异:data 块里有哪些 @name 字段取决于事件——加列前先 CAST(event_data AS XML) 肉眼看一下结构(书里的标准动作)。
  • ring_buffer 的读法在大缓冲时 XML 解析昂贵,书里提了局限没给替代方案(补充:常用 event_file 规避)。
  • CONVERT_IMPLICIT 扫描只覆盖计划缓存:被逐出的老计划查不到;抓历史要配合 Query Store(补充,不在书里)。
  • 死锁分析到「语句+计划」为止:如何改事务设计避免死锁(访问顺序、缩短事务),书里没展开。

5. 可带走的

主线一行走完: 建 XEvent 会话(sql_statement_completed + 动作 + 谓词排除 SSMS + event_file 目标)→ START → 读出 .xel → data/action 两块 XPath 拆列(时间戳记得 UTC 转本地)→ system_health 里现成的 xml_deadlock_report 拆死锁图 → frame 的 sqlhandle 反查计划 → 计划缓存 XPath 扫 CONVERT_IMPLICIT → 根因:AddWithValue 把 varchar 参数发成 nvarchar,列被转、索引作废 → 修复:显式 SqlParameter 类型或改存储过程。

  1. XEvents 四件套:事件、动作、谓词、目标;谓词在引擎里先过滤,这是低开销的根源24。
  2. 目标选 event_file(生产),ring_buffer 只适合临时排障5。
  3. 拆 XEvent XML 三段式:找路径(dm_xe_sessions/targets)→ 读文件(fn_xe_file_target_read_file)→ data/action 两块拆列7。
  4. 时间戳一律 UTC;转本地用 SWITCHOFFSET + AT TIME ZONE7。
  5. system_health 默认在抓死锁,零配置;WHERE object_name='xml_deadlock_report' 提取1011。
  6. 死锁图的 executionStack/frame 带 sqlhandle/stmtstart/stmtend,可反查语句级执行计划12。
  7. CONVERT_IMPLICIT 机理:C# string → nvarchar 参数 > varchar 列的优先级 → 转列 → 索引作废131415。
  8. 计划 XML 里 ParameterList 能看到参数的真实类型和编译值——铁证所在16。
  9. 修复两路:显式 SqlParameter(SqlDbType.VarChar),或改用存储过程(参数按声明类型处理)17。
  10. 批量扫描://Warnings/PlanAffectingConvert[contains(@Expression,"CONVERT_IMPLICIT")] 一条 XPath 揪出正在跑的隐式转换19。

6. 原文地图

主题原书章原文位置
XEvents 设计目标Chapter 11: XML and JSON As Internal SQL Server Processestext/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:578(搜「Minimal performance impact」)
四件套与五道管道同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:586(搜「publish–subscribe」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:680(搜「Event Invocation」)
谓词引擎级过滤同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:635(搜「before data is dispatched」)
目标与取舍同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:656(搜「ring_buffer」)
找 .xel 路径与三段式同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:715(搜「EventFileTarget」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:781(搜「Display XML」)
自定义列与 UTC 转换同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:800(搜「change [@name=」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:810(搜「record all timestamps in UTC」)
死锁的静默难题与 system_health同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:921(搜「happen silently」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:883(搜「included in the default」)
死锁图提取与 sqlhandle 反查同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:942(搜「execution analysis metadata」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:979(搜「retrieve the execution plan」)
优先级规则与 AddWithValue同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1153(搜「strict hierarchy」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1249(搜「AddWithValue」)
SSMS 正常、生产翻车对照同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1111(搜「100% nonclustered index seek」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1190(搜「CONVERT_IMPLICIT(nvarchar(50)」)
修复与存储过程免疫同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1275(搜「SqlDbType.VarChar」) · text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1318(搜「stored procedure enforces」)
XPath 批量扫描同上text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1040(搜「XPath query on the Execution Plan XML」)

Footnotes

  1. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 442–461 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:452,搜「lightweight」)。原文:「Extended Events…address these challenges by providing a lightweight, highly configurable, and extensible event-handling infrastructure built directly into the SQL Server engine」;「capture only the data you explicitly request」。 ↩

  2. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 585–598 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:586,搜「publish–subscribe」)。原文:「XEvents are based on a publish–subscribe model embedded within the SQL Server engine. The architecture consists of several core components: Events, Actions, Predicates, Targets, Event sessions」。 ↩ ↩2

  3. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 677–695 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:680,搜「Event Invocation」)。五道:Event Invocation、Predicate Evaluation(「If they fail, processing stops」)、Action Collection、Target Dispatch(「asynchronously dispatched」)、Target Processing。 ↩ ↩2

  4. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 634–651 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:635,搜「before data is dispatched」)。原文:「Predicates allow filtering at the engine level, before data is dispatched to a target. This is a critical distinction from SQL Trace, where filtering often occurred after data capture」。 ↩ ↩2

  5. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 654–664 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:656,搜「ring_buffer」)与第 711–739 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:712,搜「most commonly used」)。原文:「The event_file target persists events to .xel files on disk. This is the most commonly used target in production environments」;ring_buffer 局限:「Data is lost on SQL Server restart」「XML-based retrieval can be expensive for large buffers」。 ↩ ↩2 ↩3

  6. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 666–675 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:667,搜「started and stopped dynamically」)。原文:「Sessions can be started and stopped dynamically without restarting SQL Server」。 ↩

  7. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 749–838 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:800,搜「change [@name=」)。原文:「To return offset instead, change [@name="statement"] to [@name="offset"], change the target SQL type appropriately」;UTC:「Extended Events record all timestamps in UTC」及 SWITCHOFFSET/DATEPART(TZOFFSET)/AT TIME ZONE 组合见第 810–837 段。 ↩ ↩2 ↩3

  8. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 505–547 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:545,搜「sql:variable」)。 shredding 查询的 nodes 谓词:/event[@timestamp > sql:variable("@date")],@date 为一小时前。 ↩

  9. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 915–930 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:921,搜「happen silently」)。原文:「the real challenge with deadlocks is not that they happen, but that they often happen silently」;SQL Server 选牺牲方、返 1205 错误。 ↩

  10. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 877–891 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:883,搜「included in the default」)。原文:「The system_health Extended Events session has been included in the default SQL Server installation since SQL Server 2008. Starting with SQL Server 2016…the deadlock session was added as well」;「enabled by default…continuously collects essential diagnostic data」。 ↩ ↩2 ↩3

  11. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 893–912 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:897,搜「system_health_*.xel」)。路径取自 sys.dm_os_server_diagnostics_log_configurations 的 [path] 列;过滤 WHERE object_name = 'xml_deadlock_report';死锁子树 EventData.query('event/data/value/deadlock')。 ↩ ↩2

  12. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 940–1008 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:942,搜「execution analysis metadata」)。三个属性:sqlhandle、stmtstart、stmtend;反查:sys.dm_exec_text_query_plan(eqs.plan_handle, eqs.statement_start_offset, eqs.statement_end_offset),WHERE sql_handle = 0x… 见第 984–995 段。 ↩ ↩2

  13. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1149–1263 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1249,搜「AddWithValue」)。原文:「A C# string maps to NVARCHAR(MAX) when sent to SQL Server. The AddWithValue() method…creates a parameter without an explicit SQL data type」;「the parameter becomes NVARCHAR(3)」。 ↩ ↩2 ↩3 ↩4

  14. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1152–1179 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1153,搜「strict hierarchy」)。原文:「SQL Server always converts the lower-precedence data type to the higher-precedence data type」「NVARCHAR has higher precedence than VARCHAR, so SQL Server converts the VARCHAR column to NVARCHAR」「This is not a bug – it is SQL Server following its documented rules」。 ↩ ↩2

  15. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1208–1216 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1214,搜「wrapped in the YEAR() function」)。原文:「Because the column createDate is wrapped in the YEAR() function, SQL Server must compute the value for every row, making the index unusable」。 ↩ ↩2

  16. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1110–1137 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1111,搜「100% nonclustered index seek」)。SSMS 测试:15,437 行、123 逻辑读、231 毫秒;部署后:「an execution plan screenshot – but this time, it shows a nonclustered index scan」;计划 XML 铁证:<Warnings> 的 CONVERT_IMPLICIT(nvarchar(50),[DB].[dbo].[Attendees].[Sponsor],0)=[@sp] 与 ParameterDataType="nvarchar(3)"(第 1190–1203 段)。 ↩ ↩2 ↩3

  17. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1264–1299 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1275,搜「SqlDbType.VarChar」)。修复代码:Parameters.Add(new SqlParameter("@sp", SqlDbType.VarChar, cboSponsor.Text.Length));存储过程:「When a stored procedure parameter is declared as VARCHAR, SQL Server treats incoming values…according to the parameter's declared type」见第 1315–1319 段。 ↩ ↩2

  18. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1300–1304 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1300,搜「many of my clients」)。原文:「This scenario is real and has occurred with many of my clients. Most of the logged cases involve conflicts between varchar and nvarchar」;「one of the most common and most preventable causes」见第 1307 段。 ↩ ↩2

  19. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1040–1055 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1049,搜「PlanAffecting」)。XPath://pln:Warnings/pln:PlanAffectingConvert[1][(contains(@Expression,"CONVERT_IMPLICIT"))];排除系统对象的 WHERE 见第 1052–1053 段。 ↩ ↩2

  20. 出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 840–852 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:851,搜「negligible overhead」)。原文:「When designed correctly, Extended Events impose negligible overhead and can safely run in production environments」。 ↩

内建 XML(上)— DDL 触发器与 EVENTDATA() 的审计流水线

Previous Page

内建 XML/JSON(下)— DBMail HTML 报告与调优建议 DMV

Next Page

On this page

内建 XML(中)— 扩展事件、死锁图与执行计划里的 CONVERT_IMPLICIT1. 顶层全景:三件套的分工2. 核心原理2.1 扩展事件:四件套与一个五步走的事件管道2.2 拆 XEvent 的 XML:三段式与自定义报告2.3 死锁图:system_health 的免费午餐2.4 CONVERT_IMPLICIT:执行计划 XML 的实战扫描3. 作者的判断与证据4. 边界与局限5. 可带走的6. 原文地图Footnotes