Convert TimeStamp In dm_os_ring_buffers

Question

I see this conversion a lot when trying to convert this to a legitimate time:

SELECT DATEADD (ms, -1 * ((s.cpu_ticks) - r.[timestamp]), GETDATE())
FROM sys.dm_os_ring_buffers r
                CROSS JOIN sys.dm_os_sys_info s

I get the error:

Arithmetic overflow error converting expression to data type int.

What's the correct formula for converting this?

Answer:ms_ticks

Answer 1

See the SQL Server 2008 calculation in this article. It applies to 2012, 2014 and 2016 too.

cpu_ticks is what was used in SQL Server 2005.

declare @ts_now bigint 

select @ts_now = ms_ticks from 

sys.dm_os_sys_info 

select record_id, dateadd (ms, (y.[timestamp] -@ts_now), GetDate())
...

Answer 2

The timestamp of an event is the number of ms from a arbitrary point in time (e.g., the service start). In theory, you could figure out the time for timestamp 0 (e.g. the service start) and add any later timestamp as ms to it, but the value could overflow if the server is up more than 24 days. So, get the current timestamp and date and hope events are more recent than 24 days. (Or limit the query to not use timestamps that would overflow.)

The conversion needs a timestamp for a fixed time. Both should be measured at the same time and only once. For example,

select @now = GETDATE(), @ts_now = ms_ticks from sys.dm_os_sys_info

Now, you have a reference date and timestamp to use to convert timestamps.

select record_id, dateadd (ms, (y.[timestamp] - @ts_now), @now)

The INT max limit of 2147483647 in ms is about 24 days. Overflow is still possible. You could add whole days first and then add the remaining fraction of a day in ms to avoid the INT limit. Or use other tricks. One might be to use a reference time ~24 days ago. Then you can add or subtract up to 24 days to go back a total of about 48 days.

select @now = DATEADD(ms, -2000000000, GETDATE()), @ts_now = ms_ticks - 2000000000 from sys.dm_os_sys_info