UIM - How server_cpu is calculated by sqlserver probe
Article ID: 191266
Updated On:
Products
DX Unified Infrastructure Management (Nimsoft / UIM)
Issue/Introduction
How is the server_cpu calculated in sqlserver probe.
Environment
Release : 9.2.0
Component : ACTIVE DIRECTORY & EXCHANGE SERVER AIM
Cause
The probe poll the SQL Server on each interval, and compare 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.
As per the below image, in this case, every 30 seconds.
Resolution
The percentage calculation occurs as follow
1. The probe execute the next query 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
2.3 server_cpu is calculated as follow
The above calculation return the percentage of time the cpu was used since last interval.
All this is logged in sqlserver_monitor.log (loglevel 5). Here 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] sqlserver: 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
1. The probe execute the next query 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
2.3 server_cpu is calculated as follow
The above calculation return the percentage of time the cpu was used since last interval.
All this is logged in sqlserver_monitor.log (loglevel 5). Here 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] sqlserver: 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
Attachments
Feedback
Yes
No
Powered by
