An adventure on SQL Server performance and features

How to identify the unit of measure of Extended Events durations

H

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_nameevent_nameevent_fieldDurationUnitfield_typecolumn_type
sqloswait_infodurationmillisecondsuint64data
sqloswait_completeddurationmillisecondsuint64data
sqloswait_info_externaldurationmillisecondsuint64data
sqlserversql_statement_completeddurationmicrosecondsint64data
sqlserversp_statement_completeddurationmicrosecondsint64data
sqlserverrpc_completeddurationmicrosecondsuint64data
sqlservermodule_enddurationmicrosecondsuint64data
sqlserversql_batch_completeddurationmicrosecondsuint64data
sqlserverlogoutdurationmicrosecondsuint64data
sqlserverattentiondurationmicrosecondsuint64data
sqlserverexisting_connectiondurationmicrosecondsuint64data
sqlserversql_statement_post_compiledurationmillisecondsuint64data
sqlserverlock_acquireddurationmicrosecondsuint64data
sqlserverlock_deadlockdurationmicrosecondsuint64data
sqlserverquery_post_compilation_showplandurationmicrosecondsuint64data
sqlserverquery_post_execution_showplandurationmicrosecondsuint64data
sqlserverquery_plan_profiledurationmicrosecondsuint64data
sqlserverquery_post_execution_plan_profiledurationmicrosecondsuint64data
sqlserverprogress_report_online_index_operationdurationmicrosecondsuint64data
sqlservercolumnstore_tuple_mover_end_compressdurationmillisecondsuint64data
sqlservercolumnstore_tuple_mover_compression_statsdurationmillisecondsuint64data
sqlserversql_transactiondurationmicrosecondsuint64data
sqlserverexecution_warningdurationmicrosecondsuint64data
sqlserverblocked_process_reportdurationmicrosecondsuint64data
sqlserverblocked_process_report_filtereddurationmicrosecondsuint64data
XtpEnginextp_ckptctrl_storage_array_growdurationmicrosecondsuint64data

You’re welcome!

About the author

Emanuele Meazzo

1 comment

An adventure on SQL Server performance and features

Emanuele Meazzo

My Social Profiles

If you're interested in following me around in the social world, here are some URLs for you