Sql-server – Incorrect process utilization from System health extended events

extended-eventssql serversql server 2014sql-server-2012sql-server-2016

I have been recently working on understanding the data or metrics being stored in system health extended events event file.

We are trying to implement the data collection of performance metrics using system health as provided here

There are reports that gives metrics like CPU UTILIZATION, other process utilization etc being collected from system health event named

scheduler_monitor_system_health_ring_buffer_recorded

I am not able to understand for few of my busy servers why the field "process_utilization" which is listed in report as SQL CPU utilization be most of time above 100. IN peak time it varies between 120-160 and because of which the report is always showing CPU above 100 even though when i checked from servers activity monitor that's never the case.

I've raised this issue in Github but seems like no fix or response.

Therefore, my question would be

  1. How can i get accurate number for SQL CPU utilization for my servers using system health ring buffer recorded?

  2. Report also shows counter for below 2 fields calculated per their report

  1. 100-System_idle-process_utilization as OtherProcessUtil

  2. 100-system_idle as SystemUtil

What are these OtherProcessUtil and SystemUtil needed/helpful for ?

  1. I also see memory utilization every time always showing as 100. That does not seem correct as well. Has anyone noticed it?

Other tools like Idera & sentry [which i tested] do not show CPU usage above 100 % for the same servers. I did a side by side comparison for same load.

Best Answer

Values for disk and processor % counters that go over 100% Should be divided by the number of processors or disks present in the system. So 100% means it is using 100% of one processor. 250% would mean it's using two and half processors. So if it's reporting 120%, see if that is the equivalent of 120 / <number of processors>.

Note that the latest change to tigertools was June 2018. You might check out sqlwatch.io as it appears to be a much more active project.

See social.msdn.microsoft.com/Forums/sqlserver/en-US/… and just change it to TOP(1) to get the current SQL CPU usage.

– Tony Hinkle

Related Question