CPU high on SQL 2022 database- migrated from SQL 2014

Rinu Philip 16 Reputation points
2024-03-23T23:39:05.78+00:00

Hi, I have a database that is migrated from SQL 2014 to SQL 2022,the database compatibility mode on SQL 2022 is still on SQL 2008.

One Select Query is always running against this database, that returns just 340 records. There are Joins in this Select query.

This query is running in parallel and runs within 2 seconds, & because of this constantly server is hitting high CPU (90 to 100). Wait time: CXCONSUMER

Application users are happy with this response, and no complaints.

But I want to reduce the CPU usage on this server to say stay between 50 to 60 percent. The problem did not occur when this database was on the SQL 2014 server with same CPU count there(4)

My question is, if I increase CPU of the server from current value 4 to 8, will again this query start utilizing all the CPU on the server ?

Currently when this query runs it uses all the 4 processors on the server and thus max out the CPU. So if I increase CPU count from 4 to 8 will this query use all 8 CPUs and again max out the CPU?

SQL Server : 2022 ENT END

Processor: 4

Memory : 16

Any other suggestion you have? Thanks

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
{count} votes

4 answers

Sort by: Most helpful
  1. 博雄 胡 45 Reputation points
    2024-03-27T04:44:11.9466667+00:00

    After adding CPUs, it will still utilize all CPUs.

    Suggest using maxdop 2 in the hints, for example: select * from table1 where 1=1 option (maxdop 2)

    It will limit this statement to a maximum of 2 CPUs to run, which will not exceed 50% CPU.

    If you use Lucy Chen's advice, it will limit the overall resource usage of SQL Server, including all other queries, which is a waste of server resources.

    1 person found this answer helpful.
    0 comments No comments

  2. Rinu Philip 16 Reputation points
    2024-03-27T14:14:13.6966667+00:00

    Thanks for your tip on using MAXDOP. I will check it out.

    1 person found this answer helpful.
    0 comments No comments

  3. LucyChen-MSFT 560 Reputation points Microsoft Vendor
    2024-03-25T08:10:35.21+00:00

    Hi @Rinu Philip,

    Thanks for your information. Here are some suggestions in limiting the usage of CPU:

    Using the Windows Task Manager for CPU throttling.

    • Set the process priority of SQL Server to low. We can use this method to give the right of using CPU to other processes.
    • Set the processor affinity of SQL Server.

    User's image

    Using the SQL Server Management Studio for CPU throttling.

    Best regards,

    Lucy Chen


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.

    https://docs.microsoft.com/en-us/answers/support/email-notifications


  4. Rinu Philip 16 Reputation points
    2024-04-03T15:53:13.6066667+00:00

    Thanks people, so the issue was resolved by adding additional processors and more memory to the server. Adjusted the SQL server memory to use 80% of the total server memory. This has resolved the issue for the time being, do not see high CPU usage. If anything new comes up I will update the thread, thanks.

    0 comments No comments