sqlserver checkpoint "server_cpu" calculation in sqlserver probe

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

How is the server_cpu calculated in sqlserver probe checkpoint server_cpu? 

Environment

Release : UIM 20.x

Component : SQLServer 5.x

Resolution

The sqlserver checkpoint server_cpu monitors the percentage of CPU Usage by SQL Server instance in interval.

 

 

 

The probe polls the SQL Server on each interval and compares the current value with the previous value obtained to calculate the percentage of cpu usage for a specific SQL instance
As per the below image, in this case, every 30 seconds.

The percentage calculation occurs as follows:

1. The probe executes the query below on each interval:



2. The first calculation occurs after the second "check interval"

    2.1 calculate the difference between last two cpu_busy obtained for the query
    2.2 calculate the difference between last two time obtained for the query
 

    The above calculation returns the percentage of time the CPU was used since the last interval.

     All this is logged in sqlserver_monitor.log (loglevel 5).

     Below is an extract, from a test environment (the interval is set to 30 seconds)

May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine PDS Data - column: check, type: PDS_PCH, value: server_cpu
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine PDS Data - column: profile, type: PDS_PCH, value: uimdb_profile
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine PDS Data - column: instance, type: PDS_PCH, value: uimdb
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine  PDS Data - column: cpu_busy, type: PDS_INT, value: 30829
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine  PDS Data - column: timeticks, type: PDS_INT, value: 31250
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine  PDS Data - column: time, type: PDS_INT, value: 1589572595
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, engine  PDS Data - column: cpu_count, type: PDS_INT, value: 8
May 15 15:56:35:134 [2532] SQL server: getIntervalValueF value pdsnow = 30829.000000
May 15 15:56:35:134 [2532] sqlserver: getIntervalValueF value_o pdslast = 30819.000000
May 15 15:56:35:134 [2532] sqlserver: getIntervalValueF value (value - value_o) = 10.000000
May 15 15:56:35:134 [2532] sqlserver: getIntervalValueL value pdsnow = 1589572595
May 15 15:56:35:134 [2532] sqlserver: getIntervalValueL value_o pdslast = 1589572561
May 15 15:56:35:134 [2532] sqlserver: getIntervalValueL value (value - value_o) = 34
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, fwCheckAlarm - object: none, mode: 3
May 15 15:56:35:134 [2532] sqlserver: Profile: uimdb_profile/server_cpu, GetDoubleValue - value 0.114890, samples: 1

   In the above extract, 

diff(cpu_busy) = 30829 - 30819 = 10
diff(time) = 1589572595 - 1589572561 = 34
timeticks = 31250
cpu_count = 8

 

 

The calculation is then

10 x 31250 x 100
-----------------------   =  0.114890%
34 x 1000000 x 8

 

Additional Information

Monitor CPU Usage by SQL Server Instance with UIM (broadcom.com)

sqlserver "server_cpu" usage alarms with unexpected values mush greater than 100% (broadcom.com)

sqlserver Metrics (broadcom.com)