Microseconds Or Milliseconds

 

While working with a customer on creating some Extended Event to collect performance trace, I was if asked if the duration is in microseconds or milliseconds.

Thinking to myself, "well in SQL 2005 and later it is in microseconds", although the Profiler UI has an option to display the value in either milliseconds or microseconds.  So surly in Xevent it will stay the same?

It was interesting, and I started to look for more information on that.

Well apparently, it depends, it can be in microseconds or milliseconds second depending on the event.

Doing some more reading on that subject shown that with the evolution of computing the processor speed as changed and by that also the precision that was expected from the counter has change to milliseconds sometimes.

Looking at these events, Wait_info, wait_info_external, this events are calculated in microseconds and task_completed is in milliseconds.

looking into this three system tables sys.dm_xe_objects, sys.dm_xe_packages and sys.dm_xe_object_columns, shows us all the needed information and if it is in Micro or Milli seconds.

The information of the duration time is taken from sys.dm_xe_object_columns under the column description , there we will seethe duration time in microseconds or milliseconds.

The duration column will be shown with a description:

“Wait duration in milliseconds” or “Indicates the total time (in microseconds) that has taken the... ”

To extract the unit, I have added a substring and charindex that looks for the Milli or Micro in the description columns.

Here is the simple but useful (well at least for me) script.

 

[sql]Select p.name package, o.name event,
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 Unit, o.description, p.description
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'[/sql]

 

Oren Bouni