SQL @ 100%

[kg00se]

I have a NT 4.0 box running MSSQL7, all of a sudden the SQLServer.exe is maxing at 100% cpu usage causing all of my databases to stop responding. this is a critical production box and a lot is riding on this box until I can get everything swapped out to a new box. I would like to however find out what is causing SQL to peg the cpu usage to 100%.
can any one steer me in the right path?

thanks
Kevin

 

[SQLBill]

Are you sure it's SQL Server maxing it out?

Have you virus checked the box? Are there any other apps running?

-SQLBill

Posting advice: FAQ481-4875

 

[kg00se]

scanned the box for viruses and everything is clean,
I have tracked the proccess and it is definately SQLServer.exe maxing at 99% cpu usage. when this happens the only thing that I can do is run drwtsn32 -p (pid#) to shut it down then restart SQL server. once I do this everything runs fine for a while then it happens again.


Kevin

 

[SQLBill]

Try running Profiler to capture what is happening when. Then find what query is running at the time your server maxes out.

-SQLBill

Posting advice: FAQ481-4875

 

[MDXer]

you can use sp_who2 in QA to see what spids are running and then a dbcc inputbuffer(spid) to see the statement. Profiler is good for tracing a problem you already know about but if your in a crunch you need many more specifics to config profiler to catch the problem code and then your only going to get it on start or finish. dbcc inputbuffer will actually tell you while the issue is occuring. you can then possibly kill spid to end the process. Depeneding on what your doing the kill may result in a roll back that could take a substantial amount of time.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[SQLBill]

DBCC INPUTBUFFER will only return the first 256 characters of the command.

This is better:

DECLARE @Handle BINARY(20)
SELECT @Handle = sql_handle
 FROM SysProcesses
 WHERE SPID = 52
SELECT *
FROM ::fn_get_sql(@Handle)

Replace the 52 with the actual SPID (use sp_who2 as MDXer suggests).

-SQLBill

Posting advice: FAQ481-4875

 

[yelworcm]

Unfortunately, fn_get_sql was introduced in SQL 2000 SP3.

Run profiler to catch queries that run over, say, 1,000 reads. See if you can tune those queries in Query Analyzer. If it involves a cursor, try like heck to get rid of it.

 

[SQLBill]

AHHHHHH....<slap on the forehead>......just re-read the initial post and saw version 7 is being used.

-SQLBill

Posting advice: FAQ481-4875

 

[kg00se]

so if I have a particular spid that has 26796 reads on it is bad?

 

[yelworcm]

Not necessarily. The number of reads is a cummulative measure over the life of the spid. A spid that has been connected for 2 or 3 days could easily rack up that much or more. Anything come up in the profiler trace I suggested?

 

[kg00se]

no everything seems to be ok in the profiler.
I have since moved about a dozen of the database off of the server onto a new sql 2000 server running win 2003 and managed to get the original server down to 50% cpu usage but I am afraid that is going to be short lived.

 

[MDXer]

Are you running antivirus software on the server? if yes have you had any exclusions set up to exclude the AV from scanning you db files? seen this at a customer site.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[kg00se]

I thought that might have been the case also, I have completely shut all AV scans and services off on that box for testing purposes to see if that may be what was causing it, nothing. I have moved about a dozen db's off of that box to a new and more powerfull server. 1 db inparticular when I moved it started spiking the new server, so I definately know it's an actual db problem. I was concerned that the SQL environment might have been corrupt, that seems not to be the case now. since I moved the db off it is now running at 2 to 20% cpu usage which is normal for that box and 50 to 70% on the new box. I have now moved all efferts to that particular database to see what the actual problem is.

Thanks for all the help.

 

[Catadmin]

Now that you've got it moved to SQL 2000, verify your server properties to see what kind of Processor usage it's tuned to. And what type of box do you have it on (Single proc, double proc, double proc hyper threaded?)? Some of these options can actually help you out.

On the old box, if it's still running, you might want to check for memory leaks or a bad CPU. It's entirely possible it was a hardware issue rather than a software issue.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"