Sql server does not go above 50% CPU

  • 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

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

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

  • 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

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Hi Jeff,

    I set it to 0,

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

    Thanks.

  • 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Upgrade Full-Text Search - SQL Server | Microsoft Docs

     

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

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

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