October 20, 2020 at 3:29 pm
Hello,
DBA Gurus, so we have a prod SQL Server VM instance. I noticed that when CPU is high, >95% , I changed the min memory setting and max setting to be same ( say 48 GB To sql server on a 64 GB memory Virtual Machine) and the CPU Util seems coming down ( 60% or so ) which is great , Can you give me details how changing the min memory setting is fixing this problem!
Thanks in Advance
October 20, 2020 at 9:02 pm
My guess (as I've not seen your system or that behavior before) is that it isn't actually fixing anything. What I expect is happening is you have a query that uses a bunch of CPU, you see the CPU spike to over 95% and adjust the min memory, then the query finishes and CPU comes down.
SQL is generally good about requesting memory and managing it once it has it, but not letting it go.
Next time you see high CPU, it may not hurt to check for queries that are using a lot of CPU.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 21, 2020 at 12:42 pm
In the end, most SQL Server problems come down to the queries. I'd suggest setting up some kind of query monitoring. Since you're posting in the 2019 forum, the best two choices are Query Store or Extended Events. Query Store will give you an aggregated view of query behavior, by database. It aggregates on the hour, so you can compare one hour of data to another, giving you a really good way to understand changes in behavior. You can also aggregate the aggregates to get a good idea of overall resource use over a period of time. Focus on the queries that are consuming CPU. Next, Extended Events are for if you want to get extremely detailed or extremely granular in your data collection. For example, a given query/procedure runs badly sometimes and well others. Query Store, which captures aggregates, won't tell you the parameter values used between these runs. Extended Events can be used to capture that kind of detailed data.
Then, with the query data in hand, you can easily answer this question.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply