Pegging the CPU: Why??

Question

Pegging the CPU: Why??

Elliott Berkihiser

SSCarpal Tunnel

Points: 4068
More actions
August 1, 2007 at 12:55 pm

#117670

My 64 bit Enterprise Ed (SP2) is swamping the CPU for no apparent reason.
About once a week or so, SQL Server slows way down. When I look at Task Manager, I see that sqlservr.exe is using 80-97% of the CPU. There is nothing occuring that should cause this utilization.
To fix the problem I restart the SQL service, and then CPU usage drops way down (1-6%).
I looked at Perfmon (Database transactions/sec counter), but the max spike was only 150-200. This doesn't seem like a lot.
Any ideas where I should look? Or explanations for why re-starting the service would clear up the problem. There are no blocks occuring.
Any help would be much appreciated,
Cheers,
Elliott

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

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

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 1

Not sure if this is relevant, but we are also running one SQL 2000 and one SQL 2005 virtual server on the same Windows 2003 server. The virtual servers have very low transactional activity. (the one for Dev work is practically unused, the SQL 2000 instance simply holds warm backups for the Production SQL 2000 databases)

Elliott

Nap-170467 SSC Rookie Points: 41 More actions · Answer 2

Looks like you are just looking at performance for the server. You should be looking at what transactions are running on SQL server. SP_WHO2 will tell you if their are command running. If you shut down/restart the server it will naturally kill whatever is running.

Elliott Berkihiser SSCarpal Tunnel Points: 4068 More actions · Answer 3

Update:

I'm not sure yet that this is the reason the CPU was pegging, but I discovered that a domain password change had left the Witness server disconnected from the mirroring sessions of the subject server. It appears that the databases were continually trying to connect while the Witness server service accounts were running on the old passwords and this is what caused the high CPU usage.

I changed the service accounts for the witness server on Friday, and the primary server's CPU usage has been 1-8% ever since, as opposed to the 90% I was seeing last week.

Elliott

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

Thanks for posting what you've found, Elliott

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