none
Query to monitor the CPU on SQL Server 2008 RRS feed

  • Question

  • Hi , 

    I use this query on SQL Server 2005 to monitor the CPU , on Nagios.  It does not works on SQL Server 2008 R2. 

    Somebody have a similiar query to monitor SQL Server 2008 R2?

    DECLARE @ts_now BIGINT
    SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

    SELECT top 1 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

    Monday, July 1, 2013 3:08 PM

Answers

  • Timing in SQL 2008 is more accurate than SQL 2005.  So you can just us ms_ticks.  That is, in SQL 2008 and later, the first two lines in your code should be

    DECLARE @ts_now BIGINT
    SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info
    Tom

    Monday, July 1, 2013 3:49 PM

All replies

  • Try this:

    declare @ms bigint
    declare @total_ms bigint
    
    select	@ms = ms_ticks,
    		@total_ms = process_kernel_time_ms + process_user_time_ms 
    from sys.dm_os_sys_info 
    
    waitfor delay '00:00:01'
    
    select	ms_ticks - @ms total_cpu_time,
    		process_kernel_time_ms + process_user_time_ms - @total_ms total_used_time,
    		(process_kernel_time_ms + process_user_time_ms - @total_ms) / cpu_count avg_used_time_per_cpu,
    		round(((process_kernel_time_ms + process_user_time_ms - @total_ms) / cast(cpu_count as decimal(10, 2))) / (ms_ticks - @ms) * 100, 2)  cpu_utilization
    from sys.dm_os_sys_info 
    You have to wait 1 sec for the result.

    m@te

    • Proposed as answer by farkas.mate Monday, July 1, 2013 3:40 PM
    Monday, July 1, 2013 3:39 PM
  • Timing in SQL 2008 is more accurate than SQL 2005.  So you can just us ms_ticks.  That is, in SQL 2008 and later, the first two lines in your code should be

    DECLARE @ts_now BIGINT
    SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info
    Tom

    Monday, July 1, 2013 3:49 PM