How to identify the unit of measure of Extended Events durations
- Tipstricks , Troubleshooting
- April 10, 2019
Table of Contents
Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter.
That’s why I’ve added to Tsql.tech Github repository the following code that extracts the descriptions from XE DMVs in order to identify the unit of measure:
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 [c].[description]
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';
For your convenience, here’s the output of the query above in Azure SQL DB
package_name | event_name | event_field | DurationUnit | field_type | column_type |
sqlos | wait_info | duration | milliseconds | uint64 | data |
sqlos | wait_completed | duration | milliseconds | uint64 | data |
sqlos | wait_info_external | duration | milliseconds | uint64 | data |
sqlserver | sql_statement_completed | duration | microseconds | int64 | data |
sqlserver | sp_statement_completed | duration | microseconds | int64 | data |
sqlserver | rpc_completed | duration | microseconds | uint64 | data |
sqlserver | module_end | duration | microseconds | uint64 | data |
sqlserver | sql_batch_completed | duration | microseconds | uint64 | data |
sqlserver | logout | duration | microseconds | uint64 | data |
sqlserver | attention | duration | microseconds | uint64 | data |
sqlserver | existing_connection | duration | microseconds | uint64 | data |
sqlserver | sql_statement_post_compile | duration | milliseconds | uint64 | data |
sqlserver | lock_acquired | duration | microseconds | uint64 | data |
sqlserver | lock_deadlock | duration | microseconds | uint64 | data |
sqlserver | query_post_compilation_showplan | duration | microseconds | uint64 | data |
sqlserver | query_post_execution_showplan | duration | microseconds | uint64 | data |
sqlserver | query_plan_profile | duration | microseconds | uint64 | data |
sqlserver | query_post_execution_plan_profile | duration | microseconds | uint64 | data |
sqlserver | progress_report_online_index_operation | duration | microseconds | uint64 | data |
sqlserver | columnstore_tuple_mover_end_compress | duration | milliseconds | uint64 | data |
sqlserver | columnstore_tuple_mover_compression_stats | duration | milliseconds | uint64 | data |
sqlserver | sql_transaction | duration | microseconds | uint64 | data |
sqlserver | execution_warning | duration | microseconds | uint64 | data |
sqlserver | blocked_process_report | duration | microseconds | uint64 | data |
sqlserver | blocked_process_report_filtered | duration | microseconds | uint64 | data |
XtpEngine | xtp_ckptctrl_storage_array_grow | duration | microseconds | uint64 | data |
You’re welcome!