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
Posted by at

4 comments:

  1. नीरज द्विवेदीDecember 3, 2013 at 5:27 PM

    Quite helpful, thanks Shashank.

    ReplyDelete
  2. UnknownMarch 30, 2016 at 9:25 AM

    This comment has been removed by the author.

    ReplyDelete
  3. UnknownMarch 30, 2016 at 9:28 AM

    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
  4. rmouniakJune 7, 2018 at 12:10 PM

    Thanks for sharing Good Information

    Sql server DBA Online Training

    ReplyDelete

Subscribe to: Post Comments (Atom)

Followers