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

Eladio — Wed, 10 Nov 2010 06:00:00 GMT

Following the article yesterday published in our http://www.solidq.com/sqj (Know Where Your Query Spends Its Time), here is the code to send the wait stats information to a synchronous bucketizer; the obvious pros are that the structure is in memory, and you don’t need to stop the session to process the data; the cons are that you cannot reset the data read (unless you re-create the session); the other big con is that you cannot sum values in the columns (in the case of the wait_stats time is important).

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
--
-- http://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)

Search results for 'app:weblogs' matching tags 'troubleshooting', 'Extended Events', and 'Denali'

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