CPU high on SQL 2022 database- migrated from SQL 2014

Question

CPU high on SQL 2022 database- migrated from SQL 2014

Tomys

Mr or Mrs. 500

Points: 567
More actions
March 23, 2024 at 11:26 pm
Go to Answer
#4400863

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(is below) .
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
Here is the query sample:
SELECT ID, aName, bName, na='', na='', Place, County, Year, Month, Day, Event, Note FROM Births WHERE Births.Place LIKE '%ie%' AND (Births.Event LIKE '')
UNION ALL
SELECT ID, LastName, FirstName, LastName, FirstName, Place, County, Year, Month, Day, Event, Note
FROM Marriages WHERE Marriages.Place LIKE '%lie%' AND (Marriages.Event LIKE '')
UNION ALL
SELECT ID, LastName, FirstName, na='', na='', Place, County, Year, Month, Day, Event, Note FROM Deaths
WHERE Deaths.Place LIKE '%lie%' AND (Deaths.Event LIKE 'death') order by Year, Month, Day ASC
PS:I added most possible index after finding missing index from the execution plan, but still the query runs in parallel and CPU touching 100. Updated STATS for the 3 tables. I don’t want to add MAXDOP value in the query to limit to 1 processor as the response time of the query will reduce.
Thanks
Tomys

Mr or Mrs. 500

Points: 567
More actions
April 3, 2024 at 4:06 pm
Answer
#4407053

Just a quick update on this, the issue seems to have resolved by adding additional CPU and memory to the server and adjusting the MAX SQL memory to use 80 % of the server memory. I'll keep you all posted in case anything else comes up, thanks everyone.

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

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

Tomys wrote:

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

There aren't any joins in the query you provided, only UNION ALL

Not sure what indexes were suggested by missing indexes but I think the only indexes that will help are:

CREATE INDEX IX_Births_1 ON Births(Event) INCLUDE (ID, aName, Place, County, Year, Month, Day, Note );

CREATE INDEX IX_Marriages_1 ON Births(Event) INCLUDE (ID, LastName, FirstName, LastName, FirstName, Place, County, Year, Month, Day, Note );

CREATE INDEX IX_Deaths_1 ON Deaths (Event) INCLUDE (ID, LastName, FirstName, Place, County, Year, Month, Day, Note);

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

I'll also suggest that if you're using the Enterprise Edition of 2022, then having only 16GB of Ram isn't healthy. The reason why your CPUs may be screaming is because of the things that Jonathan mentioned and the likelihood that the system is trying to go parallel. The default of parallelism in SQL Server is set very low and that max degree of parallelism (MAXDOP) defaults to a setting of "0", which means use them all if possible.

IMHO, the server is under powered for what you have it doing. If the customers aren't complaining, though, perhaps it's "good enough".

If it were me, I'd make sure the queries are the absolute best (high performance, low resource usage, still correct answers), possibly knock MAXDOP down to 1 or 2, and possible increase the threshold of parallelism up to 50 as a starting point to see how things go.

I'd still invest in a lot more memory, though. It the cheapest major improvement that can be made and can sometimes make on heck of a difference.

--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)

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 3

Thank you guys for your valuable inputs ! So my first approach would be to increase processor count from 4 to 8 and memory from 16 to 32 GB. I am hoping with this increase the overall CPU on the server would reduce, and in even after this in case the query continues to use all 8 processors , then second option would be to suggest in the query to add MAXDOP to value 3 or 4, so that at least the remaining 4 processors will be free and the overall CPU usage will be less.

Ant-Green SSC Guru Points: 113507 More actions · Answer 4

Also remember, increasing your CPU increases your license cost, so ensure you have the licenses available before you just go increasing CPUs, otherwise you'll be in for a nasty shock when MSFT come knocking on your door.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

with few exceptions, and when number of cpu is now, maxdop should not be set to higher than half the number of cores - so one query does not hog the server.

CTFP should definitely be changed from its default to a higher value - 25 to 50 would be a good starting point.

but your desire to keep server on 50 to 60 - that is not a good target on my opinion - if users are happy with performance and server is constantly on the 80% bracket that isn't a bad thing - only if it was flatlined all the time I would worry about it.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

Sometimes, a rise in CPU usage may occur due to table fragmentation. It might be beneficial to consider rebuilding the clustered indexes on those tables to assess whether it will reduce CPU usage.

Michael L John One Orange Chip Points: 27055 More actions · Answer 7

Tomys wrote:

Thank you guys for your valuable inputs ! So my first approach would be to increase processor count from 4 to 8 and memory from 16 to 32 GB. I am hoping with this increase the overall CPU on the server would reduce, and in even after this in case the query continues to use all 8 processors , then second option would be to suggest in the query to add MAXDOP to value 3 or 4, so that at least the remaining 4 processors will be free and the overall CPU usage will be less.

Is this a VM on VMWare? There are many things you need to configure to get SQL to run well on a VM. And, if it is, are you sure you are seeing the VM's CPU usage and not the hosts CPU usage>

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27055 More actions · Answer 8

Tomys wrote:

Thank you guys for your valuable inputs ! So my first approach would be to increase processor count from 4 to 8 and memory from 16 to 32 GB. I am hoping with this increase the overall CPU on the server would reduce, and in even after this in case the query continues to use all 8 processors , then second option would be to suggest in the query to add MAXDOP to value 3 or 4, so that at least the remaining 4 processors will be free and the overall CPU usage will be less.

Is this a VM on VMWare? There are many things you need to configure to get SQL to run well on a VM. And, if it is, are you sure you are seeing the VM's CPU usage and not the hosts CPU usage?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Michael L John One Orange Chip Points: 27055 More actions · Answer 9

Tomys wrote:

Just a quick update on this, the issue seems to have resolved by adding additional CPU and memory to the server and adjusting the MAX SQL memory to use 80 % of the server memory. I'll keep you all posted in case anything else comes up, thanks everyone.

How much memory is on the server? 80% may not be a good value. If you have 32 GB of RAM, and allocate 80% to SQL, you have allocated 6.4 GB to the OS. That may not be enough.

Have you studied what VMWare and MS recommends to run SQL on VMWare? You may be adding resources when it's really a configuration issue. https://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Tomys Mr or Mrs. 500 Points: 567 More actions · Answer 10

How much memory is on the server? - yes 32 GB

Thanks for the link on VMWare and what MS recommends to run SQL on VMWare. I will go through it. Thanks a lot.