question

Heisenberg avatar image
0 Votes"
Heisenberg asked ErlandSommarskog commented

cpu shoots up higher sporadically.

hi,
In one of our sql server instance, we see sporadic shoot up of the CPU. In ideal scenario CPU utilization is around 50-60 percent, but sometimes it goes to 90 percent. What i've observed is we have a one particular task that runs every 5 mins and sometimes is runs longer for more than 5-6 hours (output from sp_whoisactive) thats when cpu utilization goes beyond 90 percent.

however when i run sp_whoisactive procedure to sort processes by CPU i dont see these processes takes lot of cpu, there are other processes which takes more cpu, but these top cpu processes do not run longer may be for 5-10 mins. To bring down CPU utilization i kill these long running sessions and cpu utilization comes down to normal.

Can someone explain why these long running processes , even if not taking lot of cpu time could be causing cpu usage to spike up so high?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

It is completely normal, expected and desirable for SQL Server to use all resources available for short periods of time. This is not an indication of any problem.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered ErlandSommarskog commented

my question is why is CPU shoots up and doesnt come back to normal until i kill these long running processes , even though they are not highest consumer of the CPU.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


What processes are you killing?


0 Votes 0 ·

We would need to know more about your system, particularly we would need to see the output from sp_whoisactive or similar. Else we can only provide wild guesses.

What is the nature of these long-running processes that you kill? Are they running "big" statements? Or is there a loop involved?

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @SQLServerBro,

until i kill these long running processes , even though they are not highest consumer of the CPU

What processes that you killed as others mentioned?

Please read below blog to troubleshoot high CPU issue.

How to troubleshoot high CPU utilization issue in SQL Server?


If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered

Thanks all for the response.. i see following processes usually running on the server. This is output from sp_whoisactive sorted by CPU. Does this mean CPU utilization on the server is caused by these processes, rest of the other processes CPU number will be in 400 -700 range.

120242-image.png



image.png (15.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Heisenberg avatar image
0 Votes"
Heisenberg answered ErlandSommarskog commented

Above output is from running "sp_whoisactive @sort_order ='[cpu] desc'". Which sorts sp_whoisactive by CPU. However if i run below query it gives me totally different output. so i dont understand why is it different from sp_whoisactive output when sorted by CPU ?

SELECT s.session_id
,r.STATUS
,r.blocking_session_id
,r.wait_type
,wait_resource
,r.wait_time / (1000 60) 'wait_time(Min)'
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000
60) 'total_elapsed_time(Min)'
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1, (
(
CASE r.statement_end_offset
WHEN - 1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' + Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text
,r.command
,qp.query_plan
,s.login_name
,s.host_name
,s.program_name
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sp_whoisactive is reporting the cumulative CPU usage for the connection since it was connected. Not the current CPU usage of the connection.

It is possible you have a query which is using high CPU. But unless you are having an actual performance problem on the server, this is not a problem to fix.

0 Votes 0 ·

sp_whoisactive is reporting the cumulative CPU usage for the connection since it was connected. Not the current CPU usage of the connection.

And to clarify: in your query, you sort by sys.dm_exec_requests.cpu_time, which is the CPU time for the currenty query. I assume that sp_whoisactive sorts by sys.dm_exec_sessions.cpu_time, which is the accumulated CPU time for the session.

So if you have long-running processes that stay connected and do work, they will always be on the top of the list, and sessions that come in and produce load and then disconnect, will not be on the list, since they're gone.

I don't use sp_whoisactive myself, so I cannot give advice around it.

This particular task that you mention in your original post, what ikind of task is that? Is it a T-SQL job step? Or is something orchestrated from outside SQL Server?

0 Votes 0 ·

Thank you for the explanation both of you. Really helpful.

Erland, first query in my screenshot reads from the active transaction log and uses function fn_dblog and then it transfers data to another database. other 3 queries are that begins with "begin transaction..." are replication processes. My plan is to run the query i posted every 15 secs and let is run for couple of weeks then i will match the time of query execution from this log with the time at which CPU usage was high and try to fix those queries. What are your thoughts about this.

0 Votes 0 ·
Show more comments