convert Timestamp to Real time

select r.ring_buffer_address,

r.ring_buffer_type,

dateadd (ms, r.[timestamp] - sysinfo.sqlserver_start_time_ms_ticks, sysinfo.sqlserver_start_time) as record_time,

cast(r.record as xml) record

from sys.dm_os_ring_buffers r

cross join sys.dm_os_sys_info sysinfo

where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR' 

order by 3 desc

 

select r.ring_buffer_address,

r.ring_buffer_type,

dateadd (ms, r.[timestamp] - sysinfo.ms_ticks, getdate()) as record_time,

cast(r.record as xml) record

from sys.dm_os_ring_buffers r

cross join sys.dm_os_sys_info sysinfo

where ring_buffer_type='RING_BUFFER_RESOURCE_MONITOR' 

order by 3 desc

 

DATEADD (ms, -1 * ((sys.cpu_ticks / sys.cpu_ticks_in_ms) - r.timestamp), GETDATE()) as record_time -- sys.cpu_ticks_in_ms 在2008R2中才有

 

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info);

SELECT TOP(2)

SQLProcessUtilization AS [SQL Server Process CPU Utilization],

SystemIdle AS [System Idle Process],

100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],

DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] FROM (

SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')

AS [SystemIdle],

record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')

AS [SQLProcessUtilization], [timestamp] FROM ( SELECT [timestamp],

CONVERT(xml, record) AS [record] FROM sys.dm_os_ring_buffers

WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'

AND record LIKE '%<SystemHealth>%') AS x ) AS y ORDER BY record_id DESC;