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

这一章讲什么: 原书第 11 章的最后两个配方,一个管「怎么把数据送出去好看」 (XML → HTML 邮件),一个管「怎么把引擎的建议读进来」(JSON → 调优工作流)。至此,SQL Server 内部 XML/JSON 的完整版图收口:事件(16)、诊断(17)、输出与建议(本篇)。

1. 配方 11-6:DBMail 的 HTML 报告流水线¶

解决什么问题: 给 DBA 和干系人发自动化的邮件报表(表空间、作业失败、容量预警)——纯文本邮件数字堆成一坨,没人看;HTML 表格才有人读。

1.1 主干:FOR XML RAW 生成 HTML 行¶

核心机制一句话: FOR XML RAW('TR'), ELEMENTS 把结果集的每一行变成一个 <TR> 元素、每一列变成一个 <TD> 子元素——XML 和 HTML 的血缘关系在这里兑现:HTML 表格本质上是满足 XML 良好性规则的标记,所以 FOR XML 直接产出合法的 HTML 片段,免去手工拼字符串的引号地狱¹。

流水线三段:

-- 段一:拼 HTML 骨架(标题、带内联 CSS 的表头、收尾标签),存进变量
SET @TableHead = '<html><head>…<style>td {border: solid black 1px;…}</style></head>'
               + '<table …><tr bgcolor="#F5F7EB"><td…>表头列…</td></tr>';
SET @TableTail = '</table></body></html>';

-- 段二:查询结果 → HTML 行(CTE 算好行数、MB 等,FORMAT 美化数字)
SELECT @Body = (SELECT …列… FROM Result ORDER BY … FOR XML RAW('TR'), ELEMENTS);

-- 段三:合体并发邮件
SET @GlobalBody = @GlobalBody + @TableHead + @Body + @TableTail;
EXEC msdb.dbo.sp_send_dbmail @profile_name='MyMailProfile', @recipients=…,
     @subject='Tables size report at ' + @时间戳, @body=@GlobalBody, @body_format='HTML';

表头列必须和查询列一一对应——书里的明确警告:错位一列,整张表的数据就全部对不上号²。

1.2 三种着色技巧:同一条 REPLACE 流水线¶

主干之上,书里给了三种把「行」变好看的技巧,机制完全相同:查询里打标 → REPLACE 把标记换成 bgcolor 属性。区别只在标记怎么算。

技巧一:交替行(隔行变色)。 标记 = ROW_NUMBER() OVER (ORDER BY …) % 2;两遍 REPLACE——把 <ALTLN>1</ALTLN> 换成空,把 ><ALTLN>0</ALTLN> 换成 bgcolor=#D8E2E9>——偶数行着色,奇数行留默认白³。

技巧二:交替块(按组变色)。 差别只有一个函数:把 ROW_NUMBER 换成 DENSE_RANK() OVER (ORDER BY 组列)——同一组(比如同一 schema)的所有行拿到同一个秩,% 2 后整组同色。书里的使用场景:服务器存储汇总按服务器名分组着色,「多年来收到大量好评」⁴。

技巧三:行强调(按业务阈值变色)。 不用排名函数,直接在 CTE 里用 CASE 按规则打标:行数 < 1 万不打标(1),1–2 万绿色(2),> 2 万红色(3);三遍 REPLACE 换成对应 bgcolor。书里的应用:磁盘接近容量上限时红行一眼可见;还建议加一行图例说明红绿含义⁵。

1.3 发送前的硬规矩:先在浏览器里验证¶

书里的强烈建议,值得原样记住:注释掉发邮件那段、SELECT @GlobalBody、复制结果粘进记事本、另存为 .html(保存类型必须选「所有文件」)、双击用浏览器渲染——确认无误再发。如果 SELECT 出来是 NULL,逐列检查,该 ISNULL 的地方补 ISNULL⁶。

为什么用 XML 做中间层(本章小结的立场): HTML 片段有层级、有结构,XML 的层级生成能力(再加标记-替换两段式)比纯字符串拼接更不易错、可维护——「XML 的层级特性让拼装 HTML 片段、强制结构、避开手工建 HTML 的字符串陷阱容易得多」⁷。

2. 配方 11-7:读自动调优建议的 JSON¶

解决什么问题: SQL Server 2019 起有个常被忽视的 DMV——sys.dm_db_tuning_recommendations:它暴露和自动调优(automatic tuning)同源的建议,哪怕自动调优没开,引擎也在持续评估工作负载、把建议存进去⁸。

它的形状: 列很少,但有一列 Details 是整个建议的 JSON——里面两个数组⁹:

数组	内容	拆法
`planForceDetails`	回归计划的执行统计:查询 ID、新旧计划 ID、各自行数/中止数/平均 CPU	`OPENJSON(Details,'$.planForceDetails') WITH (…)` 拆成行
`implementationDetails`	执行建议的手段:method(总是 FORCE_PLAN 类)、script(要执行的 T-SQL 强制脚本)	`JSON_VALUE(Details,'$.implementationDetails.script')` 直接取值

书里的示例查询把两者结合:OPENJSON 拆出统计列,再算两个衍生指标——Estimated_Gain(回归计划执行次数 × 平均 CPU 换算成秒,加 recommended 侧)和 Error_Prone(回归计划错误数是否更多,YES/NO)¹⁰。

建议的类型(引擎会往里放的东西):缺失索引、该删的索引、计划回归纠正、强制执行建议、查询性能洞察¹¹。

这套配方的意义书里点明了: 「真正的魔法在 JSON 的 Details 列——学会解析它,就能搭建调优工作流、自动化索引管理,并前所未有地看见 SQL Server 内部决策」¹²。和第 16/17 篇同构:引擎把诊断写成结构化数据(XML/JSON),会读的人拿到自动化能力,不会读的人只能看图形界面。

3. 作者的判断与证据¶

机制性事实(可复跑): FOR XML RAW('TR') 的行/列映射、sp_send_dbmail 的 @body_format='HTML'、三种着色技巧的实现代码、DMV 两数组的字段清单——全部给了可直接运行的 SQL¹³⁹。

经验性建议: 「先浏览器验证再发邮件」⁶;「表头列必须与查询列匹配」²;「DENSE_RANK 是块着色的关键,行着色用 ROW_NUMBER」⁴;「自动块着色在存储报告里多年好评」⁴。

评价性语句(需要标注为立场): 「FOR XML 生成 HTML 比字符串拼接更不易错、更优雅」⁷——实务中成立(SSRS/外部模板引擎之外,纯 SQL 发邮件这是主流做法),但「更优雅」是审美判断;对复杂报表(合并单元格、嵌套表),这条路会迅速变得难看(补充判断,不在书里)。

4. 边界与局限¶

DBMail 的前提:邮件 Profile 要预先配置(sp_configure Database Mail + 建好 profile),书里默认 MyMailProfile 已存在,没讲配置过程。
着色技巧的天花板:标记-替换法依赖 XML 输出的固定格式(标记总是 <ALTLN>1</ALTLN> 形态),列内容里若恰好出现同名字符串会误替换——生产使用要挑不会撞名的标记(补充判断,不在书里)。
DMV 的建议 ≠ 圣旨:自动调优建议是引擎的统计推断,FORCE PLAN 类建议执行前需要人工评估(书里说「assessment, validation, and controlled application」,即评估、验证、受控应用)⁸。
Details JSON 的 schema 随建议类型变化:planForceDetails 只在计划回归类建议里出现;缺失索引类建议的结构不同,解析代码要按类型分支(书里只拆了计划强制这一类,补充说明)。

5. 可带走的¶

主线一行走完(XML 侧): 查询算好指标 → FOR XML RAW('TR'), ELEMENTS 得 HTML 行 → 拼骨架(表头列对齐查询列!)→ 需要着色就打标(ROW_NUMBER 隔行 / DENSE_RANK 隔组 / CASE 按阈值)→ REPLACE 换 bgcolor → SELECT @GlobalBody 存 .html 浏览器验证 → sp_send_dbmail 发 HTML 邮件。

主线一行走完(JSON 侧): sys.dm_db_tuning_recommendations → Details 列 JSON → planForceDetails 数组 OPENJSON 拆统计、implementationDetails 用 JSON_VALUE 取强制脚本 → Estimated_Gain / Error_Prone 两个衍生指标 → 建议人工评估后受控应用。

FOR XML RAW('TR'), ELEMENTS:行→TR、列→TD,HTML 片段即 XML 片段¹。
表头列必须与查询列一一对应,错一列全表错位²。
三种着色同一个机制:打标 + REPLACE 换 bgcolor;隔行用 ROW_NUMBER,隔组用 DENSE_RANK,阈值用 CASE³⁴⁵。
发邮件前先把 HTML 存 .html 用浏览器验证;NULL 就查 ISNULL⁶。
sys.dm_db_tuning_recommendations 即使自动调优没开也在积累建议(2019+)⁸。
Details JSON 两数组:planForceDetails(统计,OPENJSON 拆)、implementationDetails(脚本,JSON_VALUE 取)⁹。
建议类型:缺索引、删索引、计划回归纠正、强制计划、性能洞察¹¹。
建议要评估、验证、受控应用,别盲信引擎⁸。

6. 原文地图¶

主题	原书章	原文位置
FOR XML RAW 行/列映射	Chapter 11: XML and JSON As Internal SQL Server Processes	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1458`(搜「Each row becomes」)
表头与查询列对齐	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1428`(搜「must match」)
交替行三步	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1492`(搜「tagged with a value」)
交替块与 DENSE_RANK	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1531`(搜「block-level coloring」)
行强调与阈值规则	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1572`(搜「business rules」)
浏览器验证流程	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1653`(搜「validating the HTML」)
XML 作中间层的立场	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1684`(搜「intermediate layer」)
调优 DMV 与两数组	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1707`(搜「intelligent performance」) · `text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1769`(搜「two arrays」)
planForceDetails 字段	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1773`(搜「queryId」)
implementationDetails 与 JSON_VALUE	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1797`(搜「method」) · `text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1804`(搜「JSON_VALUE(details」)
本章小结四主题	同上	`text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1818`(搜「Chapter Summary」)

出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1453–1460 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1458,搜「Each row becomes」)。原文:「Each row becomes a <TR> (table row) element, and each column becomes a <TD> (table data) element. This technique avoids manual string concatenation and ensures well-formed HTML output」。 ↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1422–1430 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1428,搜「must match」)。原文:「The list of table header columns must match the columns returned by the SQL query. Any mismatch between the HTML header and the SQL result set will cause the data to appear misaligned」。 ↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1477–1516 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1492,搜「tagged with a value」)。原文:「each returned row is tagged with a value indicating whether it should be styled as "even" or "odd"」;三步:ROW_NUMBER 打标、[ALTLN] = RN % 2、双重 REPLACE 换 bgcolor(第 1507–1511 段)。 ↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1518–1566 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1531,搜「block-level coloring」)。原文:「instead of using the ROW_NUMBER() function, we use DENSE_RANK(), another member of the SQL ranking function family」;「all rows belonging to the same schema share the same rank value」;好评见第 1526–1529 段。 ↩↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1568–1631 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1572,搜「business rules」)。阈值示例:<1 万无色、1–2 万绿、>2 万红;CASE 打标 [TRRow],三遍 REPLACE;图例建议见第 1604–1615 段。 ↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1650–1679 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1653,搜「validating the HTML」)。原文:「I strongly recommend validating the HTML in a web browser before sending any emails」;「Set Save as type to All Files (.) – this is critical」;NULL 排查见第 1663–1665 段。 ↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1682–1700 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1684,搜「intermediate layer」)。原文:「By leveraging XML as an intermediate layer, you can construct complex HTML structures…XML's hierarchical nature makes it far easier to assemble HTML fragments, enforce structure, and avoid the string-manipulation pitfalls」。 ↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1701–1719 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1707,搜「intelligent performance」)。原文:「This DMV exposes the same tuning insights used by automatic tuning – even if automatic tuning is disabled」「enabling assessment, validation, and controlled application of recommendations」。 ↩↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1755–1799 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1769,搜「two arrays」)。原文:「The JSON document in the details column has two arrays: planForceDetails and implementationDetails」;字段表见第 1771–1799 段。 ↩↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1721–1738 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1726,搜「Estimated_Gain」)。示例查询的衍生列:Estimated_Gain = (regressedPlanExecutionCount + recommendedPlanExecutionCount) * …、Error_Prone = IIF(regressedPlanErrorCount > recommendedPlanErrorCount, 'YES', 'NO');拆法 CROSS APPLY OPENJSON(Details, '$.planForceDetails') WITH (…) 与 JSON_VALUE(details, '$.implementationDetails.script') 见第 1800–1809 段。 ↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1740–1754 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1744,搜「Missing index」)。清单:「Missing index suggestions」「Dropped index suggestions」「Plan regression corrections」「Forced plan suggestions」「Query performance insights」;「Even when automatic tuning is not enabled, SQL Server still evaluates workload patterns and stores recommendations here」。 ↩↩
出处:「Chapter 11: XML and JSON As Internal SQL Server Processes」第 1811–1815 段(text/15-ch11-chapter-11-xml-and-json-as-internal-sql-server-p.txt:1812,搜「real magic」)。原文:「The real magic lies in the JSON-based details column…you can build sophisticated tuning workflows, automate index management, and gain unprecedented visibility into SQL Server's internal decision-making」。 ↩