1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| DECLARE @ts_now BIGINT
-- 2005 select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
-- 2008
SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info;
SELECT DATEADD(ms, -1 * (@ts_now - time_stamp), GETDATE()) AS event_date_time
, SQLProcessUtilization AS SQLServer
, 100 - SystemIdle - SQLProcessUtilization AS Other
, SystemIdle AS Free
, UserModeTime
, KernelModeTime
, PageFaults
, WorkingSetDelta
, MemoryUtilPct
FROM
(
SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'tinyint') AS SystemIdle
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'tinyint') AS SQLProcessUtilization
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]', 'bigint') AS UserModeTime
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]', 'bigint') AS KernelModeTime
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/PageFaults)[1]', 'bigint') AS PageFaults
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/WorkingSetDelta)[1]', 'bigint') AS WorkingSetDelta
, record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]', 'bigint') AS MemoryUtilPct
, time_stamp
FROM (
SELECT timestamp AS time_stamp
, CAST(record AS xml) 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
GO |
Partager