January 16, 2021 at 5:45 pm
Hello SSC friends,
I am a SQL Developer who is learning how to be a DBA. I am not very good at understanding CPU usage. I had an interview yesterday and I was asked...
"Your SQL Server CPU should not be at 100%. What is the best threshold of CPU on your SQL server and why?"
I have been in environments where the DBA has told me that 100% CPU is ok for a SQL instance. That doesn't sound correct. My answer... "80% CPU should be max because you need additional space for your log file growth and peaking your CPU is never good. It locks up everything." I could see on my interviewers face that I was wrong. Unfortunately, I did not get the job. I did make it to the second round of interviews, but I think this question eliminated me.
Can anyone answer this question or guide me in the right direction?
Thanks in advance for your guidance!
The are no problems, only solutions. --John Lennon
January 16, 2021 at 5:53 pm
what may have left you bad was mixing cpu with log file growth.
although for SQL to grow a file it requires a small amount of cpu that is not enough to keep 20% allocated for that possibility.
so. server always at 100% flatline - not good on my opinion, but if the server is jumping from 80 or less to 100 for a short'ish period of time even if multiple times then that is ok.
server at 100% during a batch window where heavy etl is being done? that's fine in most cases.
obviously mileage varies - I have servers with 24 cpu's that are overspec'ed for the majority of their work - during "business hours" it never goes above 10-20%.
then at night during a period of 3 hours it jumps to 70-85% - with occasional spikes to 100%.
January 16, 2021 at 6:07 pm
Thank you so much for your response. I worked with a DBA that said 99% CPU is ok because SQL Server hogs all the resources. That can't be right. That was my first DBA role. I suppose you should always go with common sense when answering these questions.
Makes sense that off hours can utilize more CPU, but what about 24/7 environments? Does this rule still apply?
The are no problems, only solutions. --John Lennon
January 17, 2021 at 4:32 am
I don't think there is a single "best" CPU%. But I would be OK with generally 80-90%, as long as there weren't periods where it spiked above 95% and stayed there for any significant period of time.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 17, 2021 at 3:14 pm
Ok, so I am just trying to understand here. It looks like we are getting conflicting answers.
Fedrico said during business hours CPU should be between 10-20% max, but Scott said during business hours 80-90% max? Scott's answer makes the most sense, since I haven't seen a SQL instance with CPU as low as 20%, it's usually much higher.
Can someone please verify?
The are no problems, only solutions. --John Lennon
January 17, 2021 at 3:31 pm
sorry but I did not say that is should be 10-20% max.
What I said is that in one of my servers (and we have over 800 SQL servers) we have it overspec'ed for the majority of its usage - during business hours it is indeed at 10-20 - only at night during their batch window it goes to 70-85%.
January 17, 2021 at 4:02 pm
It sounds like Scott is saying 80-90% during business hours and you are saying 10-20% during business hours. I understand off hours, that makes complete sense, but during business hours is the question.
I have a 3rd round of interviews this coming week, so I want to be able to speak intelligently about this topic if it comes up, plus I would like to learn the proper baseline.
Thanks again for your responses, this is a great help.
The are no problems, only solutions. --John Lennon
January 17, 2021 at 10:32 pm
You should also definitely know what queries (and what kind of queries, what they look like) are the top consumers in your SQL Server.
There are many ways to do that. One of which is most straightforward, querying DMVs:
You can get such list, among other ways, via the following query:
select top 10 rank() over(order by total_worker_time desc,sql_handle,statement_start_offset) as row_no
, (rank() over(order by total_worker_time desc,sql_handle,statement_start_offset))%2 as l1
, creation_time
, last_execution_time
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [logicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/(execution_count + 0.0) as [AvgIO]
, case when sql_handle IS NULL
then ' '
else ( substring(st.text,(qs.statement_start_offset+2)/2,(case when qs.statement_end_offset = -1 then len(convert(nvarchar(MAX),st.text))*2 else qs.statement_end_offset end - qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as database_name
, st.objectid as object_id, object_name (st.objectid) [OBJECT_NAME]
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0
order by total_worker_time desc
Likes to play Chess
January 17, 2021 at 11:26 pm
Thanks Voldemar. I'll save that query. I have researched DMV's, Query store and Data Collector, but my issue is that I am all theory when it comes to CPU, I/O, file growth, and wait_types. These are my weakness. Wait_types throw me off, but from what I understand, wait_types is how you identify issues. I better learn them I suppose. LOL 🙂
I can handle maintenance plans, index rebuilds and DB restores, but understanding CPU and I/O has always been challenging. I spent most of my career as a SQL Developer. So, from what I understand from this post, keeping your CPU around 80% is acceptable. Spikes should be brief or happen off hours. I was hoping that there was a strict baseline for this sort of thing, but it seems like it depends on your environment.
I will eventually have to troubleshoot this for real, so if any of you have advice for me, I'll gladly take it. This Admin stuff is a new world for me and is very different from development.
As always, thank you all for your advice and input. SSC is a great site. I have been here for around 15 years and it's still the best SQL site.
Stay safe folks!
Dave
The are no problems, only solutions. --John Lennon
January 18, 2021 at 3:47 pm
Thank you so much for your response. I worked with a DBA that said 99% CPU is ok because SQL Server hogs all the resources. That can't be right. That was my first DBA role. I suppose you should always go with common sense when answering these questions.
Makes sense that off hours can utilize more CPU, but what about 24/7 environments? Does this rule still apply?
If this is what the DBA said, they are not really a DBA. Generalized statements that "SQL Server hogs all the resources" are not really true. SQL Server will consume all available MEMORY, not CPU, unless you set the max memory settings. This article describes that pretty well. https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
The answer to the question "Your SQL Server CPU should not be at 100%. What is the best threshold of CPU on your SQL server and why?", is "It depends". There is no hard and fast rule for how much CPU should be used. It depends upon many factors.
As Fredrico tried to explained, during the business day, where there is typical activity by users against the database, the CPU is low. However, at night, when there are probably backups, reindexing, updating statistics, checkdb's, ETL process, and so forth running, the CPU usage jump because there is significantly more activity on the server.
CPU spikes are not bad things. It's normal. The system is working. However, a sustained high level of CPU usage, coupled with issues in performance, is something that should be looked into.
Using the various systems I have in place as an example, there is one server who's baseline is in the 80-85% CPU usage at all times. When maintenance runs, CPU spikes to 100%. But, there are no issues in the applications that are running against this database. Conversely, I have another system with a baseline that is in the 30-35% CPU range. There are a number of very complicated ETL processes that run "off-hours", and the CPU spikes. When these intense processes run, there is a slowdown experienced by the users.
If the people interviewing you are looking for a specific answer, such as 40%, then I think I would probably run from that company. Making a statement like ""Your SQL Server CPU should not be at 100%" is simply not a statement that is true in all cases.
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/
January 18, 2021 at 5:32 pm
Thank you very much for clearing this up, Michael. Also, thank you to everyone on SSC who commented! I really, really appreciate it! This was very educational for me.
I will read this article for sure.
Be well and stay safe folks.
Dave
The are no problems, only solutions. --John Lennon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply