SqlServer CPU utilization

Jun 03 2015

The Problem

Sometimes it is interesting how much CPU time your SQL Server is consuming. Well - you can look it up in the Taskmanager, but there is another solution for this: A simple query tells you the CPU time the SQL Server uses. The trick is that you query the sys.dm_os_sys_info table for it. For those who are interested: There are a lot of other tables with information about the SQL Server like memory, cache and other statistics. Have a look at the MSDN Article about the SQLServer Operating System !

The Solution

This script works for SQLServer > 2008

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
 CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
 CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
SELECT
    record.value('(Record/@id)[1]', 'int') AS record_id,
    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
   100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
   100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
 FROM (
   SELECT timestamp, CONVERT (xml, record) AS record
   FROM sys.dm_os_ring_buffers
   WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
     AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc

Get Social


(c) 2017, by bytebang e.U. - Impressum - Datenschutz / Nutzungsbedingungen
-