主页 / dba / 问题 / 30862
Accepted
Martin Smith
Asked: 2012-12-21 12:51:39 +0800 CST

使用 XML 阅读器优化计划

  • 772

从此处执行查询以将死锁事件拉出默认扩展事件会话

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

在我的机器上完成大约需要 20 分钟。报告的统计数据是

Table 'Worktable'. Scan count 0, logical reads 68121, physical reads 0, read-ahead reads 0, 
         lob logical reads 25674576, lob physical reads 0, lob read-ahead reads 4332386.

 SQL Server Execution Times:
   CPU time = 1241269 ms,  elapsed time = 1244082 ms.

慢计划 XML

平行

如果我删除该WHERE子句，它将在不到一秒的时间内完成，返回 3,782 行。

同样，如果我添加OPTION (MAXDOP 1)到原始查询中也可以加快速度，现在统计信息显示大量更少的 lob 读取。

Table 'Worktable'. Scan count 0, logical reads 15, physical reads 0, read-ahead reads 0,
                lob logical reads 6767, lob physical reads 0, lob read-ahead reads 6076.

 SQL Server Execution Times:
   CPU time = 639 ms,  elapsed time = 693 ms.

更快的计划 XML

串行

所以我的问题是

谁能解释发生了什么？为什么最初的计划如此灾难性地更糟，有没有可靠的方法来避免这个问题？

添加：

我还发现更改查询以INNER HASH JOIN在一定程度上改善事情（但仍然需要> 3分钟），因为 DMV 结果是如此之小，我怀疑 Join 类型本身是负责的，并假设其他东西必须改变。统计数据

Table 'Worktable'. Scan count 0, logical reads 30294, physical reads 0, read-ahead reads 0, 
          lob logical reads 10741863, lob physical reads 0, lob read-ahead reads 4361042.

 SQL Server Execution Times:
   CPU time = 200914 ms,  elapsed time = 203614 ms.

（和计划）

在填充了扩展事件环形缓冲区（DATALENGTH其中XML4,880,045 字节，其中包含 1,448 个事件。）并测试了原始查询的缩减版本（有和没有MAXDOP提示）。

SELECT COUNT(*)
FROM   (SELECT CAST (target_data AS XML) AS TargetData
        FROM   sys.dm_xe_session_targets st
               JOIN sys.dm_xe_sessions s
                 ON s.address = st.event_session_address
        WHERE  [name] = 'system_health') AS Data
       CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE  XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SELECT*
FROM   sys.dm_db_task_space_usage
WHERE  session_id = @@SPID 

给出了以下结果

+-------------------------------------+------+----------+
|                                     | Fast |   Slow   |
+-------------------------------------+------+----------+
| internal_objects_alloc_page_count   |  616 |  1761272 |
| internal_objects_dealloc_page_count |  616 |  1761272 |
| elapsed time (ms)                   |  428 |   398481 |
| lob logical reads                   | 8390 | 12784196 |
+-------------------------------------+------+----------+

616tempdb 分配与显示页面已分配和释放的更快的分配有明显差异。这与将 XML 放入变量时使用的页面数量相同。

对于慢速计划，这些页面分配计数达到数百万。在查询运行时进行轮询dm_db_task_space_usage显示，它似乎在不断地分配和释放页面，tempdb任何时候都分配了 1,800 到 3,000 个页面。

sql-server sql-server-2012

2 个回答

  1. Best Answer

    性能差异的原因在于标量表达式在执行引擎中的处理方式。在这种情况下，感兴趣的表达是：

    [Expr1000] = CONVERT(xml,DM_XE_SESSION_TARGETS.[target_data],0)

    此表达式标签由计算标量运算符定义（串行计划中的节点 11，并行计划中的节点 13）。计算标量运算符与其他运算符（SQL Server 2005 及更高版本）的不同之处在于它们定义的表达式不一定在它们出现在可见执行计划中的位置进行计算；评估可以推迟到后面的运算符需要计算结果为止。

    在目前的查询中，target_data字符串通常很大，使得从字符串到XML昂贵的转换。XML在慢速计划中，每次需要结果的后续运算符Expr1000反弹时，都会执行要转换的字符串。

    当相关参数（外部引用）更改时，重新绑定发生在嵌套循环连接的内侧。Expr1000是此执行计划中大多数嵌套循环连接的外部引用。该表达式被多个 XML 读取器（包括 Stream Aggregates 和启动过滤器）多次引用。根据 的大小，XML字符串转换为的次数XML很容易达到数百万。

    下面的调用堆栈显示了target_data字符串被转换为XML（ConvertStringToXMLForES- 其中 ES 是表达式服务）的示例：

    启动过滤器

    启动过滤器调用堆栈

    XML 阅读器（内部 TVF 流）

    TVF 流调用堆栈

    流聚合

    流聚合调用堆栈

    将字符串转换为XML每次这些运算符重新绑定时都解释了使用嵌套循环计划观察到的性能差异。这与是否使用并行性无关。恰巧优化器在MAXDOP 1指定提示时选择了哈希连接。如果MAXDOP 1, LOOP JOIN指定，则性能很差，就像默认的并行计划（优化器选择嵌套循环）一样。

    哈希连接能提高多少性能取决于是Expr1000出现在操作符的构建端还是探测端。以下查询在探测端定位表达式：

    SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_sessions s
    INNER HASH JOIN sys.dm_xe_session_targets st ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

    我已经从问题中显示的版本中颠倒了连接的书面顺序，因为连接提示（INNER HASH JOIN上面）也强制整个查询的顺序，就像FORCE ORDER已经指定一样。反转是必要的，以确保Expr1000出现在探头侧。执行计划的有趣部分是：

    提示1

    使用在探测端定义的表达式，值被缓存：

    哈希缓存

    的评估Expr1000仍然推迟到第一个运算符需要该值（上面堆栈跟踪中的启动过滤器），但计算的值被缓存（CValHashCachedSwitch）并重用于 XML 读取器和流聚合的后续调用。下面的堆栈跟踪显示了 XML 阅读器重用缓存值的示例。

    缓存重用

    当强制连接顺序使得 的定义Expr1000发生在哈希连接的构建端时，情况就不同了：

    SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
            '<victim-list>', '<deadlock><victim-list>'),
        '<process-list>', '</victim-list><process-list>')
    AS XML) AS DeadlockGraph
FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st 
    INNER HASH JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

    哈希 2

    散列连接在开始探测匹配之前完全读取其构建输入以构造散列表。因此，我们必须存储所有值，而不仅仅是从计划的探测端处理的每个线程的值。因此，散列连接使用tempdb工作表来存储XML数据，并且以后的操作员每次访问结果都Expr1000需要昂贵的访问tempdb：

    访问速度慢

    下面显示了慢速访问路径的更多细节：

    慢细节

    如果强制合并连接，则输入行被排序（阻塞操作，就像哈希连接的构建输入）导致类似的安排，tempdb由于数据的大小，需要通过排序优化的工作表进行慢速访问。

    由于执行计划中不明显的各种原因，操作大型数据项的计划可能会出现问题。使用散列连接（使用正确输入上的表达式）不是一个好的解决方案。它依赖于未记录的内部行为，但不能保证下周会以同样的方式工作，或者基于稍微不同的查询。

    信息是，XML今天的操纵可能是难以优化的事情。在粉碎之前将其写入XML变量或临时表是比上面显示的任何东西更可靠的解决方法。一种方法是：

    DECLARE @data xml =
        CONVERT
        (
            xml,
            (
            SELECT TOP (1)
                dxst.target_data
            FROM sys.dm_xe_sessions AS dxs 
            JOIN sys.dm_xe_session_targets AS dxst ON
                dxst.event_session_address = dxs.[address]
            WHERE 
                dxs.name = N'system_health'
                AND dxst.target_name = N'ring_buffer'
            )
        )

SELECT XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')
FROM @data.nodes ('./RingBufferTarget/event[@name eq "xml_deadlock_report"]') AS XEventData (XEvent)
WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';

    最后，我只想从下面的评论中添加马丁非常漂亮的图形：

    马丁的图形

    • 41

  2. 这是我最初发布在这里的文章中的代码：

    http://www.sqlservercentral.com/articles/deadlock/65658/

    如果您阅读评论，您会发现一些不存在您遇到的性能问题的替代方案，一个使用对该原始查询的修改，另一个使用一个变量在处理 XML 之前保存它，这很有效更好的。（请参阅我在第 2 页上的评论）来自 DMV 的 XML 处理速度可能很慢，就像从 DMF 解析 XML 以获取文件目标一样，通常最好先将数据读入临时表，然后再处理它。与使用 .NET 或 SQLCLR 之类的东西相比，SQL 中的 XML 速度较慢。

    • 10

相关问题