how to checkf if SQL Server CPU/RAM has bottleneck

Question

how to checkf if SQL Server CPU/RAM has bottleneck

892717952

SSC Eights!

Points: 843
More actions
March 23, 2021 at 1:59 pm

#3861238

how to checkf if SQL Server CPU/RAM has bottleneck? thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

I am sure there are a lot of ways, but my approach is to check each individually.

Perfmon will tell you quite nicely how much CPU SQL Server is using compared to the rest of the system. Having CPU sitting at 100% for long periods of time MAY indicate you have a CPU bottleneck, or may indicate that you have things misconfigured, or may indicate things are working as expected. I say "working as expected" because if your system is not using CPU, that CPU is being wasted. If it is sitting constantly at 100%, then you may need more cores (if you are getting a lot of parallel operations) or you may need more single core performance. Another thing to watch for is if it is using 10% of the CPU on a 10 core CPU, that MAY be 1% per core, or you may have a heavy single-threaded operation using 100% of 1 core. That is an indicator that better single core performance may help SQL Serer. That being said, I wouldn't be looking at CPU being the bottleneck UNLESS the CPU is sitting near 100% due to SQL server for extended periods of time. Brief bursts to 100% (or 100% on a single core) are not really anything I'd investigate. Our CPU sits pretty idle most of the time (10-20% total for most of the day) with spikes to 100% during our ETL, but the ETL is fairly brief when it spikes.

Now as for RAM, you want SQL to use up as much RAM as you can. Not quite 100% or the OS may suffer, but if you are only using 10% of your RAM (for example), you may want to reconfigure SQL Server. As for if the RAM is a bottleneck, looking at your page life expectancy is a good starting point. If pages are in memory for very short periods of time, you likely will benefit from reconfiguring to use more memory or adding more memory. Adding more is very unlikely to hurt anything, but if you aren't having page life expectancy problems, more RAM likely won't make much of a difference.

But, it may not be either of those things being your bottleneck. A good approach is to look at "what problem am I trying to solve?". If it is a single query that is running slower than you'd like, CPU and RAM are going to be low on my list of things to look at with the query, indexes, and the data being higher priority. If the entire system is feeling sluggish, then I may start looking at hardware, but even then I wouldn't narrow it down to JUST CPU and RAM. You also have the disk and the network to look at. And even then, it could be configuration such as having all of your databases and log files on the same disk which will likely hurt performance.

For most queries that I have seen on my system, if they are slow, it is rare that it is a hardware problem. Most of the time it is a poorly written query or poor indexes that are causing the performance issues. Once we had a RAM issue but that was more of a configuration issue as SSIS ate up the memory before SQL could request more and it caused queries to have to spill to tempdb while the SSIS package ran. Once the package finished, memory was freed up on the server and SQL gobbled it up and performance of the database improved while SSIS degraded (needed to page to disk which is a horribly slow operation). Fixed that by reconfiguring SQL so SSIS and SQL could share the system better.

Not trying to say CPU/RAM is NOT your problem (it very much could be), just saying it doesn't hurt to look at everything on the system.

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.

Steve Jones - SSC Editor SSC Guru Points: 736173 More actions · Answer 2

+1 to what Brian wrote. Analyze a number of things to try and find the bottlenecks. Bad code can cause the CPU to rise, which may not be a problem with better code.

Jo Pattyn SSC-Dedicated Points: 32345 More actions · Answer 3

There is a stored procedure that shows if there are queries waiting for memory or cpu

sp_PressureDetector | Erik Darling Data

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Another way is to use Extended Events to capture wait metrics for the query. Alternatively, use sys.dm_os_wait_stats (for on-premises, sys.dm_db_wait_stats for Azure SQL Database) to capture the wait statistics. That will tell you what the query is waiting on. Is it CPU, disk or memory?

"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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

892717952 wrote:

how to checkf if SQL Server CPU/RAM has bottleneck? thanks!

There are a ton of decent articles out there on this subject. You really need to do a little "googlin'". Try "CPU BOTTLENECKS SQL SERVER" and "MEMORY BOTTLENECKS SQL SERVER".

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