加入收藏 | 设为首页 | 会员中心 | 我要投稿 核心网 (https://www.hxwgxz.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 编程 > 正文

sql-server – 使用XML阅读器优化计划

发布时间:2021-05-23 15:01:13 所属栏目:编程 来源:网络整理
导读:执行 the query from here以将死锁事件拉出默认的扩展事件会话 SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ('(data/value)[1]','varchar(max)'),'victim-list','deadlockvictim-list'),'process-list','/victim-listprocess-list') AS XML)
副标题[/!--empirenews.page--]

执行 the query from here以将死锁事件拉出默认的扩展事件会话

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

(编辑:核心网)

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

热点阅读