Sql server does not go above 50% CPU

Question

Sql server does not go above 50% CPU

andre1992

SSC Enthusiast

Points: 183
More actions
November 20, 2020 at 8:55 am

#3811907

Hi everyone,
I have this issue: in a single complex processing performed in a virtual machine with 4 virtual processors and 4 sockets, the CPU never manages to exceed 50% thus doubling the previous runtime when it was performed on a physical server. Is it possible that there is a limit imposed by SQL server on the virtual machine?
Thanks a lot

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

If someone has gone and setup CPU affinity on the instance then yes.

Do you have multiple SQL instances on the Virtual Machine? Maybe someone has setup affinity so that it doesn't kill the other SQL instances.

SQL standard supports the lesser of 4 sockets or 24cores, so the 1-1 mapping here is OK, anything further you would need to look at adding extra vCPU's to the existing sockets and not increasing the socket count as then you wont get any benefit. Personally I would of done the 4sock4core as 1sock4core but that's just my preference.

SQL enterprise core supports operating system maximum

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

It's also just possible that you're not CPU bound. In fact, it's pretty common to run out of other resources first. Disk I/O and Memory are much more frequently the bottlenecks on systems and CPU can be pretty low use.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

andre1992 SSC Enthusiast Points: 183 More actions · Answer 3

No, there is only one instance in the virtual machine.

We also add that I tried to give 8 processors and 8 sockets to the machine and the cpu was blocking at 25% for single operation.

The particular thing is that: with 4 similar operations performed simultaneously, the CPU went to 100% so it is able to reach maximum power. The problem lies precisely in managing the single operation where instead it seems to have a block and therefore the response time obviously increases.

Do you have an idea why?

Thanks

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Nope. Not without tons more information. What do the wait statistics look like? What's causing it to slow down?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

Query or server set to MAXDOP 2 by any chance? Or resource governor implemented to restrict CPU for a particular workgroup classification?

andre1992 SSC Enthusiast Points: 183 More actions · Answer 6

Yes Anthony,

It was just the MAXDOP set to 2!! Now it's back to working properly.

Also analyzing the differences with the previous server, I noticed that the Full-Text Upgrade Option property is currently set to Rebuild while previously it was Import. Could you kindly explain to me the difference and how to choose one of these?

Thank you so much in advance

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 7

andre1992 wrote:

Yes Anthony,
It was just the MAXDOP set to 2!! Now it's back to working properly.
Also analyzing the differences with the previous server, I noticed that the Full-Text Upgrade Option property is currently set to Rebuild while previously it was Import. Could you kindly explain to me the difference and how to choose one of these?
Thank you so much in advance

So what did you end up setting MAXDOP to?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

andre1992 SSC Enthusiast Points: 183 More actions · Answer 8

Hi Jeff,

I set it to 0,

And please, could you answer the other question I asked?

Thanks.

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 9

andre1992 wrote:

Hi Jeff,
I set it to 0, And please, could you answer the other question I asked?
Thanks.

On the above, you might want to check your baseline performance for a couple of days and then change it to MAXDOP 4 and do the same comparison. I've got 32 hyperthreaded processors and I had MAXDOP set to 8. Due to some extenuating circumstances, I had to change it to 4 for a while and, lo and behold, many of my larger runs cut their duration by 50%. It's worth the experiment.

I also have Cost Threshold of Parallelism set to 50 (for a long time).

As to your Full Text question, I'll have to bid a bye and let someone else answer that because I don't use Full Text Search anywhere.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113445 More actions · Answer 10

Upgrade Full-Text Search - SQL Server | Microsoft Docs

Part way down is upgrading full text with the different IMPORT/REBUILD/RESET options