Following the article yesterday (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 -- -- 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)
Latest posts by Eladio Rincón (see all)
- Wait stats information to Synchronous Bucketizer Target in Denali CTP1 with Extended Events - November 10, 2010
- Connecting to SQL Server with servername localhost - January 15, 2007
- Disponible para descargar el SP2 de SQL Server 2005 - October 25, 2006