Extended event duration Millisecond or Microsecond?

Question

There was a question in this forum about Extended Event module_end duration unit, which I answered. Details here.

Is it always in microseconds for all events?

Answer 1

It is a mix of Millisecond, Microsecond and some are unknown. Easiest way to find I used below tsql code to determine which is what.

SELECT p.name package_name,
       o.name event_name,
       c.name event_field,
       DurationUnit= CASE
                         WHEN c.description LIKE '%milli%' 
                         THEN SUBSTRING(c.description, CHARINDEX('milli', c.description),12)
                         WHEN c.description LIKE '%micro%' 
                         THEN SUBSTRING(c.description, CHARINDEX('micro', c.description),12)
                         ELSE NULL
                     END,
       c.type_name field_type,
       c.column_type column_type
FROM sys.dm_xe_objects o
JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
WHERE o.object_type = 'event'
AND c.name ='duration'

Here is the result set from a SQL 2016 SP1 server. I only posting part of it. Look at the DurationUnit column and you can see 3 different values.

For NULL ones I find the best option is to run (if possible with start and end time) and find what the unit is.

I ran the same query against vnext SQL Server and confirmed we will carry the same confusion to the future.

Answer 2

You can also 'crack open' the XE Session in SSMS and check out the details in the 'configure' part of the Events tab - as the description for duration fields typically provides the unit of time being used.