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,lob logical reads 6767,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,lob logical reads 10741863,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

+-------------------------------------+------+----------+
|                   | 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 |
+-------------------------------------+------+----------+

解决方法@H_502_52@

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

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

此表达式标签由Compute Scalar运算符(并行计划中的节点11,并行计划中的节点13)定义.计算标量运算符与其他运算符(sql Server 2005以后)的不同之处在于,它们定义的表达式在可见执行计划中为not necessarily evaluated at the position they appear;可以推迟评估,直到后来的运算符需要计算结果.

在当前查询中,target_data字符串通常很大,使得从字符串到XML的转换变得昂贵.在慢速计划中,每当需要Expr1000结果的后续运算符被反弹时,就会执行字符串到XML的转换.

当相关参数(外部引用)发生更改时,重新绑定发生在嵌套循环连接的内侧. Expr1000是此执行计划中大多数嵌套循环连接的外部引用.表达式由多个XML读取器,流聚合和启动过滤器多次引用.根据XML的大小,字符串转换为XML的次数可以很容易地以数百万计.

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

启动过滤器

XML阅读器(内部TVF流)

流聚合

每次这些运算符重新绑定时,都会将字符串转换为XML,从而解释了嵌套循环计划中观察到的性能差异.无论是否使用并行性,都是如此.只有在指定了MAXDOP 1提示时,优化器才会选择散列连接.如果指定了MAXDOP 1,LOOP JOIN,则性能很差,就像默认的并行计划(优化器选择嵌套循环)一样.

散列连接增加了多少性能取决于Expr1000是否出现在运算符的构建或探测端.以下查询在探针端定位表达式：

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]','</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读取器和流聚合的后续调用.下面的堆栈跟踪显示了XML Reader重用的缓存值的示例.

当强制连接顺序使得Expr1000的定义出现在散列连接的构建端时,情况就不同了：

SELECT CAST (
    REPLACE (
        REPLACE (
            XEventData.XEvent.value ('(data/value)[1]','</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'

在开始探测匹配之前,散列连接完全读取其构建输入以构造哈希表.因此,我们必须存储所有值,而不仅仅是从计划的探测端处理每个线程的值.因此,散列连接使用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';

最后,我只想在下面的评论中添加Martin非常漂亮的图形：