March 2, 2023 at 4:09 am
Hi all,
We have SQL 2017 on windows physical host. This is with Always on set up with 20 databases. We are facing high cpu usage. Previously the usage is below 85% and suddenly started 90% and above. It’s fluctuating be 80-99%.
We did the statics update on all databases. We have index defragmentation job using Hallenger’s script. It didn’t helpful. There is no changes in sp that are executing recently with highest cpu.
Is doing the failover & restarting will be helpful?
March 2, 2023 at 5:31 am
What is the physical server configuration( CPU, RAM, Disk (Hard Disk, SAN etc) , etc) .
Between 85% and 90% any changes in above items, Kindly use wait type and Perfmon to start your investigation.
Update us after checking.
Regards
Durai Nagarajan
March 3, 2023 at 3:18 am
Direct your analytical and other readable queries to secondary replica.
See if that helps.
=======================================================================
March 3, 2023 at 4:54 pm
Direct your analytical and other readable queries to secondary replica.
See if that helps.
What if the secondaries are not read only?
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/
March 3, 2023 at 11:56 pm
Michael is correct, We don’t have readable secondary. I don’t think adding more resources to the physical box is easy as VM.
March 4, 2023 at 5:08 pm
Michael is correct, We don’t have readable secondary. I don’t think adding more resources to the physical box is easy as VM.
I read through this - and seriously, if your CPUs are running at 85% through a normal workload then this system is seriously undersized or you have many other issues that need to be addressed.
Bad code is almost certainly contributing to the high CPU utilization.
Statistics and index maintenance may help - but that depends on other factors. What are the CXPACKET waits on this system - is the system using parallelism too much (MAXDOP too high, cost threshold too low) - is the system going to disk too often - are the queries pulling too much data and then using DISTINCT/GROUP BY to get rid of duplicates? A whole lot of things need to be checked here.
The simplest solution would be to get better hardware - but if the above issues exist and are not addressed then all you are doing is spending money to delay the issues.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 4, 2023 at 11:31 pm
ramana3327 wrote:Michael is correct, We don’t have readable secondary. I don’t think adding more resources to the physical box is easy as VM.
I read through this - and seriously, if your CPUs are running at 85% through a normal workload then this system is seriously undersized or you have many other issues that need to be addressed.
Bad code is almost certainly contributing to the high CPU utilization.
Statistics and index maintenance may help - but that depends on other factors. What are the CXPACKET waits on this system - is the system using parallelism too much (MAXDOP too high, cost threshold too low) - is the system going to disk too often - are the queries pulling too much data and then using DISTINCT/GROUP BY to get rid of duplicates? A whole lot of things need to be checked here.
The simplest solution would be to get better hardware - but if the above issues exist and are not addressed then all you are doing is spending money to delay the issues.
+1000 except that index maintenance usually won't help and is frequently the cause of poor performance due to the serious additional page splits that improper index maintenance causes.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 5, 2023 at 7:57 am
Let me clarify - index maintenance might help if the tables are highly fragmented now, causing additional IO and memory to be utilized. It won't directly help performance because index maintenance isn't about performance - but it could help reduce resource usage which as a by-product could improve overall system performance.
Regardless - there are serious issues here that need to be addressed and index maintenance is just a tiny portion. With that said, performance issues are always a matter of resolving one issue at a time - and there never will be a single change that will be the fix.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 5, 2023 at 1:25 pm
Is the high CPU usage on all nodes, or just the primary? If it’s just the primary, do you have synchronous or asynchronous commits? If it’s async, is tempdb significantly larger on the primary? If all of those are true, then look at the latency between the nodes. I’ve observed this in my AG’s when there were latency issues.
If it’s async, see what happens if you change it to synchronous. If the CPU drops, then latency is your issue.
What is tempdb usage on the primary? Poor tempdb performance will drive up CPU.
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/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply