MCITP

MCITP

Tuesday, September 11, 2012

How to find the CPU busy stats for SQL SERVER.

This statement will give the value that will show how much the SQL SERVER CPU is busy.


DECLARE @CPU_BUSY int, @IDLE int

SELECT @CPU_BUSY = @@CPU_BUSY, @IDLE = @@IDLE
WAITFOR DELAY '000:00:01'

SELECT (@@CPU_BUSY - @CPU_BUSY)/((@@IDLE - @IDLE + @@CPU_BUSY - @CPU_BUSY) * 1.00) *100 AS CPUBusyPct

3 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Note:
    If the time returned in @@CPU_BUSY or @@IO_BUSY exceeds approximately 49 days of cumulative CPU time, you receive an arithmetic overflow warning. In that case, the value of @@CPU_BUSY, @@IO_BUSY and @@IDLE variables are not accurate.

    https://msdn.microsoft.com/en-us/library/ms186925.aspx

    Replace global variable to DMV:

    declare @cpuusage money = ( select cntr_value
    from sys.dm_os_performance_counters
    where object_name like '%Workload Group Stats%'
    and counter_name = N'CPU usage %'
    and instance_name = 'default')
    declare @cpuusagebase money = ( select cntr_value
    from sys.dm_os_performance_counters
    where object_name like '%Workload Group Stats%'
    and counter_name = N'CPU usage % base'
    and instance_name = 'default')
    select ((@cpuusage / @cpuusagebase) * 100)

    ReplyDelete

Followers