Monitor CPU Usage by MS SQL Server Instance with UIM
Article ID: 272222
Updated On: 11-29-2023
Products
Issue/Introduction
What options do we have in UIM to monitor CPU Usage by MS SQL Server Instance?
Environment
- Release: UIM 20.4.x
- Components: 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 on the server, which accesses the Windows performance counters directly given the access provided by the locally installed robot/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 taking different values from the DB and comparing them in an 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 the 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 SQL Server CPUs as the probe supports, remotely from a single location that hosts the different credentials to login to the SQL servers. 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 much greater than 100% (broadcom.com)
Additional Information
Feedback
