CPU high on SQL 2022 database- migrated from SQL 2014

Question

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

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Using the SQL Server Management Studio for CPU throttling.

• Configure the max worker threads and maximum server memory to limit the CPU occupation and memory resource. Hope this article can help you understand the max worker threads and configure it :Configure the max worker threads (server configuration option) - SQL Server | Microsoft Learn
• You can configure the maximum server memory by reading this article. Server memory configuration options - SQL Server | Microsoft Learn

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