High CPU

Question

Post reply

High CPU

sandhyarao49

SSCrazy

Points: 2215
More actions
February 25, 2010 at 3:37 pm

#138453

Hi all,
My one of the production server CPU utilization is high varying from 60-90.
Checked in the backend and found a self blocking spid.Is this is causing servere CPU utilization.
can any one throw some light in which areas i need to check to find the culprit.
Thanks,
Sandhya

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

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

M&M SSC-Insane Points: 21700 More actions · Answer 1

Just see what is the process doing. Run a dbcc inputbuffer(spid) and get details of that.

Once you have that, just check the execution plan of this query and see if this could be tuned.

You could also work on configuring performance dashboard reports (a Microsoft add on tool) which

would be very useful on such cases, where it will help you to find the CPU utilization, queries

taking high CPU and lot more information.

If you are new to this, check this link.

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p1.aspx

It is very easy to install

M&M

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 2

Hi,

It is an update statement from one of the tool hosted on the server.

sandhyarao49 SSCrazy Points: 2215 More actions · Answer 3

The same updates work fine and occasionnaly we are facing the same issue.

I.e Self blocking SPID.

Last week i head killed it and it never rolled back and as a result i had to recyle the services.

Now again it happened today.

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

sandhyarao49 (2/25/2010)
Hi,
It is an update statement from one of the tool hosted on the server.

I've run into that once myself and many times for other people. Please post the UPDATE query and let me see if it's the same problem.

When it happened to me, a 2 second update slammed all the CPU's on the whole box into the wall for more than 20 minutes. The fix is unbelievably simple but I need to see your UPDATE query to see if it's the same problem.

--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)

- Win. SSCertifiable Points: 6790 More actions · Answer 5

Agree..

Jeff is correct. with simple changes in update query will help to overcome your problem.

- Win.

Cheers,
- Win.

" Have a great day "