Monitor CPU Usage by SQL Server Instance with UIM
Article ID: 272222
Updated On:
Products
Issue/Introduction
What options do we have in UIM to monitor CPU Usage by SQL Server Instance?
Environment
Release : UIM 20.4.x, sqlserver any version, processes probe any version.
Cause
Guidance
Resolution
In UIM we have 2 different methods for capturing the "CPU Usage by SQL Server Instance"
One is through the processes probe, which is installed locally into the Server, which accesses the Windows performance counters directly given the access provided by the locally installed Agent.
The other method, equally valid, is via the sqlserver probe "server_cpu" checkpoint. As discussed in UIM - Checkpoint "server_cpu" calculation in sqlserver probe (broadcom.com) it captures the CPU Usage by SQL Server Instance relying on a calculation that is done taken different values from the DB and comparing them in a interval.
Below is an example of both metrics turned on on the same SQL instance. With a little difference (sampling set to 60 seconds) they follow the same path.
The processes probe has the advantage of being more accurate, given the fact that relies on the windows counters directly, but it has the disadvantage that, in case of remote monitoring, it is not possible to monitor the processes remotely, as the probe needs to run locally.
The sqlserver probe has the advantage of being able to monitor as many SQLserver CPUs as the probe supports, remotely from a single location that hosts the different credentials to log to the SQLservers. The sqlserver probe has the disadvantage that in very rare cases, given that the metric relies on a calculation, if any of the factors included in the formula presents an unexpected variation, it can create a scenario where the percentage presents unexpected values: (Known issue: sqlserver "server_cpu" usage alarms with unexpected values mush greater than 100% (broadcom.com)
Additional Information
Feedback
