High CPU for SQL server running on VmWare

Question

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

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

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., Commented Jan 8, 2021 at 22:36
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., Commented Jan 11, 2021 at 15:36

Vladislav Zalesak · Accepted Answer · 2021-01-08 22:27:00Z

1

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.

answered Jan 8, 2021 at 22:27

Vladislav Zalesak

1,5231 gold badge10 silver badges15 bronze badges

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
– BeginnerDBA
Commented Jan 8, 2021 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.
– Vladislav Zalesak
Commented Jan 8, 2021 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. learn.microsoft.com/en-us/sql/relational-databases/performance/… Also this is in the range where you should evaluate in-memory OLTP. learn.microsoft.com/en-us/sql/relational-databases/…
– David Browne - Microsoft
Commented Jan 9, 2021 at 17:03

Add a comment |

1 Answer 1