SQL Server CPU Utilization

Some helpful SQL stats from this article.

-- From the SQL Server Performance Dashboard (sys.dm_os_ring_buffers)
-- Query CPU utilization for each four minute intervals
declare @ts_now bigint
select @ts_now = cpu_ticks / convert(float, cpu_ticks_in_ms) from sys.dm_os_sys_info
select record_id,
      dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as EventTime,
      SQLProcessUtilization,
      SystemIdle,
      100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization
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 '%%') as x
      ) as y
order by record_id desc

-- 4:12 (two procs CPU=8,9,9,9)
DECLARE @count int
SELECT @count =100000000
WHILE @count > 0
	BEGIN
	SELECT @count=@count-1
	--PRINT @count
END

Leave a Reply