Wait stats information to Synchronous Bucketizer Target in Denali CTP1 with Extended Events

Here is the code:

Create the session:

--
-- Extended Events (query level wait stats)
--
-- Eladio Rincon
-- Solid Quality Mentors 2010
-- http://creativecommons.org/licenses/by-sa/3.0/
-- Attribution-NonCommercial-ShareAlike 3.0
--
-- https://blogs.solidq.com/elrinconDelDBA
-- http://siquelnet.com
--


USE AdventureWorks
GO

SELECT DB_ID('adventureworks') dbid, @@spid spid
GO

CREATE EVENT SESSION xe_waits_bucket
ON SERVER
ADD EVENT
    sqlos.wait_info (
        WHERE  (sqlserver.database_id = 7 AND sqlserver.session_id = 59 AND opcode = 1)
    )
ADD TARGET package0.synchronous_bucketizer ( 
SET filtering_event_name='sqlos.wait_info'
, source_type=0
, source='wait_type'
)
WITH 
(
    MAX_MEMORY = 4096KB, 
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, 
    MAX_DISPATCH_LATENCY = 1 SECONDS, 
    MEMORY_PARTITION_MODE = NONE, 
    TRACK_CAUSALITY = ON, 
    STARTUP_STATE = OFF
)

Then start the session:

ALTER EVENT SESSION xe_waits_bucket
ON SERVER
STATE = START

Run a sample query:

SELECT * from HumanResources.vEmployee

Now; if you want to know where is the information, run the following query:

SELECT CAST(xest.target_data AS XML) xml_data
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'xe_waits_bucket'

Bear in mind the xes.name column that is the name of the session.

if you see the resultant XML, you’ll see something like this:

<BucketizerTarget truncated="0" buckets="256">
  <Slot count="183" trunc="0">
    <value>121</value>
  </Slot>
  <Slot count="75" trunc="0">
    <value>66</value>
  </Slot>
  <Slot count="11" trunc="0">
    <value>99</value>
  </Slot>
</BucketizerTarget>

Here is the nice part:

• value is an internal code for the wait type
• count is the number of occurrences

So, the thing is find out how to translate the value in the XML to the wait_type description.

you’ll get that information from the sys.dm_xe_map_values, DMV.

finally, the query will be the following:

-- wait stats
SELECT 
    v.count 
    , k.map_value wait_type
from (
SELECT
     t.x.value('(value)[1]', 'int') as k 
     , t.x.value('(@count)[1]', 'int') AS [count]
from ( 
SELECT CAST(xest.target_data AS XML) xml_data
FROM sys.dm_xe_session_targets xest
JOIN sys.dm_xe_sessions xes ON xes.address = xest.event_session_address
JOIN sys.server_event_sessions ses ON xes.name = ses.name
WHERE xest.target_name = 'synchronous_bucketizer' AND xes.name = 'xe_waits_bucket'
) v
CROSS APPLY xml_data.nodes ('//BucketizerTarget/Slot') as T (x)
) v
left join sys.dm_xe_map_values k
on  v.k = k.map_key
where k.name = 'wait_types'

In my case, the results will look like this:

count       wait_type
----------- -------------------------------
215         SOS_SCHEDULER_YIELD
75          PAGEIOLATCH_SH
11          NETWORK_IO

Final Notes:

• The JOIN doesn’t resolve correctly in SQL 2008 because aparently the keys doesn’t match with the dm_xe_map_values (I couldn’t find the relation).
• This works on Denali CTP1; I’m not sure it it will work on next CTPs. UPDATE: Jonathan Kehayias (SQL Server MVP) pointed this PM that this issue is fixed in SQL Server 2008 SP2 (280004); however, still doesn’t work on SQL Server 2008 R2.
• it would be nice if in the future you could select aggegate operations in other columns (such sums, avgs, maxs, etc)