sql-server – 使用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';

在我的机器上完成大约需要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.

Slow Plan XML

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

类似地,如果我将OPTION(MAXDOP 1)添加到原始查询中,那么现在显示的大小更少的高音读取数据也会加快速度.

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.

Faster Plan XML

所以我的问题是

Can anyone explain what’s going on? Why is the original plan so
catastrophically worse and is there any reliable way of avoiding the
problem?

加成：

我还发现将查询更改为INNER HASH JOIN可以在一定程度上改善事物(但仍然需要> 3分钟),因为DMV结果非常小我怀疑Join类型本身是负责任的并且假设其他必须已经改变.统计数据

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.

(And plan)

在填充扩展事件环形缓冲区(XML的DATALENGTH为4,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 |
+-------------------------------------+------+----------+

tempdb分配有明显的区别,更快的分配显示分配和释放了616个页面.这与将XML放入变量时使用的页面数量相同.

对于缓慢的计划,这些页面分配计数达到数百万.在查询运行时轮询dm_db_task_space_usage表明它似乎在tempdb中不断分配和释放页面,任何时候分配的页数在1,800到3,000页之间.

解决方法

[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非常漂亮的图形：

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!