0

I am trying to understand the below behavior for one of our SQL server running on VM.

SQL Server version 2017 has 64vCores and Host has 96 cores with base speed 2.1 GHZ

Based on the article from https://sqlperformance.com/2017/05/monitoring/troubleshooting-cpu-vmware i collected the counters and can see below"-

Processor% Processor Time in Task Manager or perfmon: Toggles around 80 in peak business hours

% Processor Time for VM ware is always pegging around 100%

Effective VM speed MhZ averages around 140,305 so does not look to be limited anywhere. ( Minimum 8219 and maximum 157,612)

Host processor speed is 2091

Is there a way to find what does that 100% coming from? Or is that OK to be 100 % for that counter where SQL seems to be between 75-80%?

Additional info:-

Avg Batch re/sec on high time = 30K

Blocking/deadlocks= none

Queries are generally fast like 100K executions/min and avg CPU they spend is few ms.

Mostly all the above queries are single threaded due to various reasons.

CTOP= 200 and MAXDOP =8 Thanks

3
  • It's possible it can be an issue or it might not be, hard to say without other correlative data points. But out of curiosity which counter are you looking at Process\% Processor Time: The sum of processor time on each processor OR Processor(_Total)\% Processor Time: The total for all processors?
    – J.D.
    Jan 8 at 22:36
  • @J.D.- Its Processor(_Total)\% Processor Time Jan 11 at 14:34
  • Then it might not be a problem necessarily. More information would be needed to evaluate, since 100% total processor time divided by 64 cores is less than 2% per core on average. But I'd listen to the other comments, especially David Browne's, as I'm not an expert on this.
    – J.D.
    Jan 11 at 15:36
0

thats seems to be a lot of CPU being burned and should be approached IMHO.

Do you have any performance issue? I would try sp_blitz https://www.brentozar.com/blitz/

and if you have querystore enabled, take a look at queries that eat up the CPU.

3
  • I've already used those procs and could not find much. App dev says its expected but users have reported few issues and time outs. Added additional info, thanks Jan 8 at 22:31
  • 30k batch requests per second? Even with 1ms per batch request, thats what, 30s of CPU time per 1s...... Based on queries per minute its like avg ms time is 26ms. Without more info hard to quess, i would take a look at queries. If you dont have some quries that do row by row instead set based operations. Jan 8 at 22:55
  • 1
    And with 64 cores, that looks about right. 30K batches per second is a lot. Trimming a few ms of CPU time from common queries might make a big difference. docs.microsoft.com/en-us/sql/relational-databases/performance/… Also this is in the range where you should evaluate in-memory OLTP. docs.microsoft.com/en-us/sql/relational-databases/… Jan 9 at 17:03

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.