How to reduce HIGH CPU LOAD 98 % (urgent)

Question

Post reply

How to reduce HIGH CPU LOAD 98 % (urgent)

WhiteLotus

SSChampion

Points: 12122
More actions
September 24, 2014 at 10:21 pm

#296830

This morning my CPU usage is EXTREMELY HIGh ..it is around 97 %.. I check the open transaction using DBCC opentran and it show me an open transaction that has been running for 7 hours. I killed the session ID ..and monitor the CPU load after a few hours ..It is still high ..( around 93% )
I check the wait stat : SELECT * FROM sys.dm_os_wait_stats AS dows
ORDER BY wait_time_ms DESC
The result is
wait_typewaiting_tasks_countwait_time_msmax_wait_time_ms signal_wait_time_ms
LAZYWRITER_SLEEP22475275159161668316751319854831
WRITELOG27027941110006236431190489814187
BROKER_TASK_STOP2917249846673379103031426566
BROKER_TRANSMITTER4877521358638756885234
BROKER_RECEIVE_WAITFOR1304164274116353533553352952
At the moment I don’t know what to do …
Please give advice … many2 thanks

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

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

Wison SSC-Addicted Points: 425 More actions · Answer 1

you can use profiler on third server to Monitor the sql statements which cause the CPU high.

or

get the sql statements from the DMV.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

If you're going to look at waits, you have to filter out the benign waits. All but one of the ones you've mentioned should be ignored. See Jonathan Keyhaius's blog posts on the subject, or his Troubleshooting SQL Server book.

An open transaction is not necessarily a problem. Do you know what impact rolling it back had on the business processes?

As for identifying the CPU problems, have a read through these

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

https://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 3

Wison (9/25/2014)
you can use profiler on third server to Monitor the sql statements which cause the CPU high.
or
get the sql statements from the DMV.

Use trace or extended events to capture query behavior, sure, but don't point the Profiler GUI at a production server, especially one that is already under pressure.

"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

WhiteLotus SSChampion Points: 12122 More actions · Answer 4

Grant Fritchey (9/25/2014)
Wison (9/25/2014)
you can use profiler on third server to Monitor the sql statements which cause the CPU high.
or
get the sql statements from the DMV.
Use trace or extended events to capture query behavior, sure, but don't point the Profiler GUI at a production server, especially one that is already under pressure.

Hi All ,

Thanks for the response..Much appreciate it !!

I run this query instead of using SQL profiler which I dont get used to :

SELECT getdate() as "RunTime", st.text, qp.query_plan, a.* FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as st CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) as qp order by CPU_time desc

the result is :

3 records which consume the highest CPU time ...

Status BACKGROUND

wait type :BROKER_RECEIVE_WAITFOR

start time : 15 Sept ( which is a long time ago )

I wonder how to reduce it and I don't know what is the meaning of Status : Background

Please kindly advise

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

There's a reason Grant and I both recommend using traces or extended events....

The query which you decided to use instead only shows currently executing queries, which is near useless if you're trying to analyse the workload. The query that it 'identified' is a background process, a system process that starts when SQL starts and sits sleeping most of the time. September 15 would have been the last time that you restarted SQL, which is why that query shows a start time of that date. It is not a problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 6

If you really don't want to learn extended events (and I can't think of a good reason why not) and you still want to see what queries have been doing on the system, then you want to query sys.dm_exec_query_stats. It's going to be an incomplete picture since it only shows aggregate values and only for the queries currently in cache.

"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