9

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?
  • The disk and processor % counters that I've seen that go over 100 are related to 1 processor/disk. 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>. – Tony Hinkle Mar 6 at 19:09
  • @TonyHinkle: 1. Not sure if xtended events uses the same processors counters info and second i am still not getting if thats the correct understanding of high CPU pressure. How come other tools like Idera & sentry [which i tested] is not showing cpu usage above 100 % for the same servers. I did side by side comparison for same load – BeginnerDBA Mar 7 at 14:58
  • I don't have specific answers as I haven't tested this to find out if I can repro it. I have set it all up, but haven't had time to try to repro it yet. One thing--this "solution" is something that they put together to be able to quickly/cheaply deploy something--it is not a "product" that undergoes QA testing and such, so I would set my expectations accordingly. – Tony Hinkle Mar 7 at 15:36
  • 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. – Tony Hinkle Mar 7 at 15:40
  • 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 Mar 9 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.