使用XML阅读器优化计划

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';

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.

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.

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.

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 

#1 楼

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

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

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

在当前查询中，target_data字符串通常很大，因此从字符串到XML的转换成本很高。在慢速计划中，每当需要XML结果的后面的运算符反弹时，都会执行字符串到Expr1000的转换。

当相关参数（外部参考）更改。 Expr1000是此执行计划中大多数嵌套循环联接的外部引用。多个XML读取器（流聚合）和启动过滤器多次引用该表达式。根据XML的大小，可以很容易地将字符串转换为XML的次数以百万计。

下面的调用堆栈显示了target_data字符串被转换为XML的示例（ConvertStringToXMLForES- ES是Expression Service的地方）：

启动过滤器



XML Reader（内部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出现在探头侧。执行计划中有趣的部分是：



在探针侧定义表达式后，将缓存值：

<仍然推迟对Expr1000的求值，直到第一个运算符需要该值（上面的堆栈跟踪中的启动过滤器），但是对计算出的值进行缓存（CValHashCachedSwitch），并由XML Reader再次用于以后的调用和流聚合。下面的堆栈跟踪显示了一个XML读取器重用的缓存值的示例。构建哈希连接的一侧，情况有所不同：

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'

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



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



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

处理大型数据项的计划可能对各种从执行计划中看不出来的原因。使用散列连接（表达式在正确的输入上）不是一个好的解决方案。它依赖于未记录的内部行为，无法保证下周将以相同的方式运行，也无法保证查询会略有不同。

信息是，tempdb操作今天可能是棘手的事情，需要优化。切碎之前将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';

最后，我只想从下面的注释中添加Martin非常漂亮的图形： br />

#2 楼

那是我最初发表在这里的文章中的代码：

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

如果您阅读注释，您将找到一些不存在您所遇到的性能问题的替代方法，一种使用原始查询的修改，另一种使用变量在处理XML之前保存XML，效果更好。 （请参阅我在第2页上的评论）来自DMV的XML处理起来可能很慢，就像从DMF解析XML来获取文件目标一样，通常可以通过先将数据读取到临时表然后进行处理来更好地实现。与使用.NET或SQLCLR之类的东西相比，SQL中的XML速度慢。