February 18, 2020 at 3:16 am
Hi All ,
I need to troubleshoot high CPU usage in a dedicated SQL server (>90%) .
Please advice on how I start ?
Appreciate your feedback
February 18, 2020 at 1:27 pm
Like any other problem in the system, most of the time it's queries. I'd use either Query Store (set up per database) or Extended Events to capture query metrics. Identify those queries that either individually use the most CPU, or cumulatively use the most CPU. Figure out what's wrong with those queries. Change them to make the problem go away. Keep going from there.
You can also look to the wait types to get an understanding of why, in general, your system is slow. It may be hardware, the OS, or even SQL Server configuration. However, the vast majority of the time, it's just queries.
A few settings you can look to right away. First, optimize for ad hoc should be enabled. I've yet to see, or even hear about, a system that this hurts. However, as with all things, testing is your friend. Next, the default value for the cost threshold for parallelism of 5, is stupid. Change it. Here's one method for determining a new value. Again, testing is your buddy.
That's it. Identify the source, understand why, make the necessary modifications. Keep going. This is performance tuning.
"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
February 18, 2020 at 8:14 pm
Another thing to check is - is it actually SQL Server using the CPU?
If you have predictable times that the CPU is high, check things out in task manager as well to ensure it is SQL Server eating your CPU.
Once you are certain it is SQL Server (and not SSIS, SSRS, antivirus, windows updates, explorer.exe, etc), I'd follow Grant's advice. Although, we have one server that gets small spikes in the 90% or more on the CPU, but these are expected and due to our ETL load and don't cause a noticeable performance hit as they happen after hours and are relatively short lived (usually go away in 1-2 minutes).
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.
February 18, 2020 at 8:44 pm
Since you already know that the server is dedicated to SQL and that CPU utilization is high, I'd look at the plan cache to see what queries are the high users of CPU. A query like this can get you started:
SELECT TOP 25
qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.total_worker_time / qs.execution_count / 1000000.0 AS avg_cpu_seconds,
qs.total_worker_time / 1000000.0 AS total_cpu_seconds,
qs.total_logical_reads, qs.total_logical_writes,
qs.total_logical_reads / qs.execution_count AS average_logical_reads,
qs.total_logical_writes / qs.execution_count AS average_logical_writes,
qs.execution_count, qs.last_execution_time, qs.creation_time,
OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid) AS schema_name, OBJECT_NAME(qt.objectid, qt.dbid) AS object_name, o.modify_date,
qp.query_plan, qs.sql_handle, qs.plan_handle,
DB_NAME(qt.dbid) AS database_name,
SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats qs
OUTER APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
--WHERE last_execution_time >= '2020-02-18'
--ORDER BY avg_cpu_seconds DESC;
ORDER BY total_worker_time DESC;
You can sort by the total CPU time, or the average CPU time across executions. This will let you see what has already happened, while an extended event session can only show you what happens while it was running.
February 19, 2020 at 3:09 am
Hi All ,
I need to troubleshoot high CPU usage in a dedicated SQL server (>90%) .
Please advice on how I start ?
Appreciate your feedback
Hi All ,
I need to troubleshoot high CPU usage in a dedicated SQL server (>90%) .
Please advice on how I start ?
Appreciate your feedback
That's actually pretty unusual. We ran into a similar problem and it turned out to be the connection properties for all the connections the GUIs were making. All of the connections were made with M.A.R.S. (Multiple Active Result Sets) enabled, which is very VERY bad for most things in SQL Server and was meant for a very narrow group of programming methods, which most people don't do and when they do, they frequently get it wrong.
Check to see if you're getting a lot of ROLLBACKs... If you are, M.A.R.S. is definitely the problem. Don't let that be the end of your search though. If your connections are setup to use M.A.R.S., there's still a high probability that it's still the problem.
The other thing to check for is any virus checking or other security software that's beating the hell out of your system. It doesn't have to be the worst problem to cause a major problem to show up as SQL Server.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2020 at 12:55 am
Thanks for your response .
I tried your query yesterday ( 19/02/2020 ) and use this command : WHERE last_execution_time >= '2020-02-15'
The result that I got is the records starting from 18/02/2020 night time...
I want to see the records at 16/o2/2020 (Sunday ) as the CPU was high at that time ...
How do I achieve this ?
Please kindly response ...
February 20, 2020 at 12:07 pm
Thanks for your response .
I tried your query yesterday ( 19/02/2020 ) and use this command : WHERE last_execution_time >= '2020-02-15'
The result that I got is the records starting from 18/02/2020 night time...
I want to see the records at 16/o2/2020 (Sunday ) as the CPU was high at that time ...
How do I achieve this ?
Please kindly response ...
What have you tried? This should be a pretty simple exercise for anyone with a nodding acquaintance with T-SQL. What do you think might work here?
"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
February 20, 2020 at 4:01 pm
If you were trying to see all the way back to the 16th there is a possibility, if this is a query that isn't called very often, that it is no longer in the plan cache. That is a limitation of looking into the plan cache unfortunately, there could be high turnover if there are a lot of ad-hoc type queries filling up the plan cache's memory.
February 21, 2020 at 12:40 am
Thanks for the response
Questions:
How do I Check and in which situation to see if I am getting a lot of Rollbacks?
So far the only situation when I see rollback is when restart is happening 🙂 ...
February 21, 2020 at 1:11 pm
IIRC, they'll actually show up in sp_Who2
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply