High CPU

Question

High CPU

sandhyarao49

SSCrazy

Points: 2215
More actions
April 13, 2010 at 1:35 pm

#213795

Hi ,
On one of my production server the cpu is Fluctuating between 75 – 95 %
Checked the high CPU usage queries and found 4 long running queries (inserts) which were eating most of the CPU.
Interestingly all the 4 queries are in running state and all the queries were executing the same SP.
Is this issue is due to SP problem.

Viewing 13 posts - 1 through 12 (of 12 total)

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Does this query runs batch inserts? Does the underlying tables have triggers? Does the SP do a lot of other lookups or joins before doing the insert?

-Roy

krypto69 SSChampion Points: 13604 More actions · Answer 2

If it's the inserts that are slowing it down, is the table heavily indexed?

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 3

Hi,

Yes the underlying tables has triggers on it.

I have kiiled all the 4 queries and the CPU on the server came down.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 4

I assume these are not bulk inserts. Check what is blocking. The only way to solve this issue is try to monitor and figure out what is causing the slowdown. You cannot keep on killing SPIDS.

-Roy

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 5

Also look at the disk queue (disk counters). High CPU can be correlated to long queue lengths.

arussell_10 SSCrazy Points: 2110 More actions · Answer 6

Queries could also be scanning a table instead of using proper indexes.

arussell_10 SSCrazy Points: 2110 More actions · Answer 7

arussell_10

SSCrazy

Points: 2110

April 16, 2010 at 8:49 am

#1152888

Sorry, missed the part that they were inserts.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 8

If inserts are slowing down then as suggested above check for indexes on base tables. They must be useful and any uncecessary/duplicate indexes needs to be dropped.

McSQL SSCarpal Tunnel Points: 4936 More actions · Answer 9

I have found that quite often it is reports causing the problem, and regularly where the criteria hasn't been properly specified.

If you captured the query and source while they were running then it might be worth throwing them through database engine tuning advisor anyway so you can consider optimisations/indexes etc if it's going to be a regular thing.

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 10

Hello All,

I have faced the same issue again, stuck at the same SP, CPU is hitting 90-95 %.

Captured the execution plan for the statements executing , seems to be a bad query plan not sure on this.

Again killed the SPID's.

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 11

Possibilities are ,

1. Check for the deadlock on the object involved.

2. Very poor indexes on the table. There could be huge table scan.

3. Check for any possible optimization on the queries.

Cheers,

Venkatesan Prabu .J

http://venkattechnicalblog.blogspot.com/

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

sandhyarao49 (4/16/2010)
Hello All,
I have faced the same issue again, stuck at the same SP, CPU is hitting 90-95 %.
Captured the execution plan for the statements executing , seems to be a bad query plan not sure on this.
Again killed the SPID's.

Your horse doesn't go where you want it to... so you shot it... again. 😉

First, you're giving us absolutely nothing to go on. You say you've captured the execution plan but have not shared it. If you want help, provide some useful information. See the article at the second link in my signature line below for how to post information for help on performance problems.

As a side bar, check the code for UPDATE statements that have a JOIN in them. What you need to check for is that the target of the update absolutely MUST be in the FROM clause. If it is not, then sometimes the code will work correctly and other times a 2 second update will pin all CPU's to the wall for hours before the code completes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)