但是，它会将数据作为一个很大的 XML 值提取出来。如果需要将其进一步分解，可使用图 5 所示的代码。
图 5 分解 XML 数据
SELECT
Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,
Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,
Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID
FROM
(SELECT CAST(xest.target_data AS XML) StatementData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON
xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer'
AND xes.name = 'MonitorIO') Statements
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results);
GO
Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,
Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,
Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID
FROM
(SELECT CAST(xest.target_data AS XML) StatementData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON
xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer'
AND xes.name = 'MonitorIO') Statements
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results);
GO
这种做法效果不错，但会为捕获的每个事件都生成一行输出。这并不是一种可怕的格式，另外我还希望得到汇总输出，因此我决定使用派生表，如图 6 所示。
图 6 获取聚合输出
SELECT DT.ResourcePoolID,
SUM (DT.Reads) as TotalReads,
SUM (DT.Writes) AS TotalWrites
FROM
(SELECT
Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,
Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,
Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID
FROM
(SELECT CAST(xest.target_data AS XML) StatementData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON
xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer'
AND xes.name = 'MonitorIO') Statements
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results)) AS DT
WHERE DT.ResourcePoolID > 255 –- only show user-defined resource pools
GROUP BY DT.ResourcePoolID;
GO
SUM (DT.Reads) as TotalReads,
SUM (DT.Writes) AS TotalWrites
FROM
(SELECT
Data2.Results.value ('(data/.)[6]', 'bigint') AS Reads,
Data2.Results.value ('(data/.)[7]', 'bigint') AS Writes,
Data2.Results.value ('(action/.)[1]', 'int') AS ResourcePoolID
FROM
(SELECT CAST(xest.target_data AS XML) StatementData
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON
xes.address = xest.event_session_address
WHERE xest.target_name = 'ring_buffer'
AND xes.name = 'MonitorIO') Statements
CROSS APPLY StatementData.nodes ('//RingBufferTarget/event') AS Data2 (Results)) AS DT
WHERE DT.ResourcePoolID > 255 –- only show user-defined resource pools
GROUP BY DT.ResourcePoolID;
GO
大功告成!其中肯定包含一些复杂的代码，但它们都运行正常。至此我已得到了我所需的结果。看一下有关我的测试数据的本次查询输出，如图 7 所示。
