Share via

In which case I can conclude that my sql server needs more cpu cores?

Chirag Sachdeva 281 Reputation points
2021-07-16T03:43:10.053+00:00

Hi Folk,

I have trying to google in which case I can conclude that my CPU core are causing the performance issue on sql and I need more cpu cores to be added to the server or may be I need to replace existing cpu on the server or may be both. Can any please suggest anything on this topic.

One point I know that when we start seeing more 'SOS_scheduler_yield' wait as top wait in your server thing is one of the indicator. But I am looking for more answers

I already know CPU should always be the last thing to check after checking memory, IO, server properties etc. etc.

Thanks
Chirag

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,791 questions

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,471 Reputation points
    2021-07-16T07:12:43.683+00:00

    Hi @Chirag Sachdeva ,

    There’s no doubt that server hardware can have a huge impact on performance. There are three main pillars for SQL Server hardware performance: CPU, memory, and storage.
    Quoted from this article which provides some important performance counters.

    And refer to this

    Best regards,
    Seeya

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. Erland Sommarskog 111.1K Reputation points MVP
    2021-07-16T09:14:42.21+00:00

    Unfortunately, there is no straight answer here, because there are so many factors involved.

    If you see a high CPU load on your system, and, yes, high SOS_SCHEDULER_WAIT is a strong indication of this, the best and cheapest resolution may not be to buy more CPU, but simply tune queries and are inefficiently written or that need better indexing. In that situation to get more CPU is just throwing hardware at the real problem.

    But then again, running slow queries is not always trivial. If it is a third-party application, you have at least one hand behind your back, and if the license agreement does not permit you add indexes, both your hands are tied, and all that is left is the hardware option.

    I guess that beside SOS_SCHEULDER_WAITS, performance counters can also give you some information, but rarely looking at performance counters myself, I leave it to others to suggest the best ones to look at.

    1 person found this answer helpful.
  3. Tom Phillips 17,731 Reputation points
    2021-07-16T14:37:32.82+00:00
    1 person found this answer helpful.
  4. Seeya Xi-MSFT 16,471 Reputation points
    2021-07-19T07:08:03.88+00:00

    Hi @Chirag Sachdeva ,

    One point I know that when we start seeing more 'SOS_scheduler_yield' wait as top wait in your server thing is one of the indicator.

    There are other wait stats you can refer.
    https://knowledgebase.apexsql.com/how-to-configure-and-use-sql-server-wait-statistic-monitoring/

    https://www.sqlskills.com/blogs/paul/wait-statistics-or-please-tell-me-where-it-hurts/
    This link may be helpful.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.