CPU Utilization more than 95% for more than 3 hours

Question

CPU Utilization more than 95% for more than 3 hours

VastSQL

SSC-Dedicated

Points: 36097
More actions
October 15, 2014 at 4:12 am

#291017

HI Experts,
In one of our servers CPU Utilization more than 95% for more than 3 hours, can anyone please guide me on troubleshoot or to find th root cause of this issue.
No jobs,no blocking exists... Top CPU processes in sysprocesses are system processes .CHECKPOINT is showing CPU 21256586 which is highest.
TIA

Viewing 15 posts - 1 through 15 (of 30 total)

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

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

Sysprocesses is useless for this, it's the cumulative totals since SQL started, so of course the system processes will show high.

Did you have any form of extended events trace running at the time? Any profiler trace? Were you looking at active processes during those 3 hours? Do you have wait information from that time?

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks Gail for the reply.

We don't have any Extended events or trace running. Was check the sysprocesses only.

Please advise what to check on this.

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

If you're out of the period, there's not really any way to know what was happening without having set up the extended events ahead of time, or turning it on when you're seeing an issue. During the period, you could try querying the sys.dm_exec_requests to see which queries are using the most resources. But after the fact, there's nothing that records all the calls on the system unless you set it up.

"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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system. I have run my very tight collection of fields/events on systems with well over 1K executions per second with minimal impact.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

Thanks Grant and Kevin,

CPU is still high .

What all events i need to select while create a profiler trace in this case?

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

TheSQLGuru (10/15/2014)
A proper profiler-to-local-disk script can be run to capture heavy CPU users with very little overhead on the system.

And an extended events session will have even less.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

Thanks Gail,

Since we are still facing the issue i hope creating an Extended Event now also will give some insight.

Can you please help me on creatin one

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

Create an extended events session or server-side trace to record what's running against the server. Let it run long enough to catch a good sample of data. Look at the results, identify what was using the most CPU, then analyse from there.

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 9

Have your already determined that it's SQL Server consuming the CPU and not something else? The network admins could have other things (system updates, etc.) running on the server. It would be worth a 10-second check to make sure you're looking in the right area.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 10

Thanks Gail,

Yes its SQL Server using more than 95% now.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 11

I have got around 130 trace files now.

In between the CPU came down to 13 at 7:54 and back to 95 again i have trace in between those times.

Can anyone help me to pin point the cause these traces?

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

I'm sure I've given you this link before.

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

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 13

VastSQL

SSC-Dedicated

Points: 36097

October 15, 2014 at 7:05 am

#1752514

Yes Gail,

Thanks Again.

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

Use extended events because they're much lower cost than trace (much, much, much lower). Capture rpc_complete and sql_batch_complete. That can show you which queries are running to completion and what resources they use.

"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