Who's overusing my SQL 2K Server?

[LCannon]

I have a SQL 2K server that is being used by about 30 clients and at certain times, the CPU goes through the use. Need to know how I can track down what or who is causing that. Any suggestions?

 

[JayKusch]

Try opening Enterprise Manager and drill down to Management..CurrentActivity..ProcessInfo and the look under the CPU header. Thanks

J. Kusch

 

[WildWest]

We had this same problem and we tracked it down to a developer who created run-away queries and improper joins against large tables. He was locking up the others users too!

sp_who and
sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

are both good commands to track down in these cases. Then you can find out the UID messing you up, kill the process from Ent Mgmr, and slap him silly. hehehe good luck!

 

[LCannon]

Jay,

Doesn't that CPU header just tell how long that process has been using the CPU? If that's the case, I have processes that run all the time, so their number would/is rather large and that's ok. Or am I reading it wrong???

Wild,

I'm kind of thinking that something that running on the server is causing these spikes. I have a programmer that has written a bunch of DTS and other programs that run against SQL, but, as usual, when the spikes happen he saying that "It's not me!".

And it looks like the sp_who is displaying the same amount of info as the Current Acctivity is. I'm having problems understanding the sp_lock feedback.

 

[LCannon]

Well, to date, I still haven't been able to figure this one out. It's really kicking me around.

About the same time every day, my SQL server gets really busy and my clients get real slow connectings. I'm monitoring what I know to monitor, Memory, Hard Drives, Processor and two SQL items. Got that from;

http://www.sql-server-performance.com/sql_server_performance_audit.asp

(good site)

At this point, I'd really appreciate any ideas. This is realy driving me nuts.

 

[JayKusch]

Do you have any scheduled job or maintenance plans that are kicked off at the said time? Thanks

J. Kusch

 

[LCannon]

Yes. But those same jobs are happening all the time. I have replication between this server and another with the agents required for replication running all the time.

My maintenance plans are running at different times.

 

[Dilyias]

Try running a trace (with the profiler) on queries that take a long time (or > 1000 msecs or something) , look at the ones that occured during the time of the slowdown...

 

[JayKusch]

If you run the trace, fine tune it, or you will incurr even more performance degradation during that time when Profiler is running. Thanks

J. Kusch

 

[LCannon]

I'm rather new to SQL. How do I run a fine tuned trace for this?

 

[SQLSister]

If you have checked all your scheduled jobs and none seem to be the problem, have you considered finding out if the network or client machines are scheduled to do anything at this time. I say this because my SQL server would suddenly seem to run slow at 12:00 pm, but actually it was because the network guy scheduled the virus checker to run in the background on all the client machines at this time. Of course the symptom that everyone noticed was that their database access was slow when actually it was because the virus checker was taking up all their processing power.

 

[LCannon]

SQLSister,

Yeah, good idea. I have elimated any virus programs and such. Plus, my programmer has sworn to me that he's not running anything at those times. I've loged the IP's that were connected to it and didn't see any of his programs do it. We have managed to find some queries and joins that he has begun to "improve". (At least something good is coming from this.)

Also, there isn't anything running in the background on the clients. They are all operators taking calls. And these times of day are high traffic times, but we have handled this same amount of traffic, and more. Plus, this came on rather sudden.

Interesting enough though, two of the monitors that would indicate that this is cased by a CPU bottleneck, Processor Time and Processor Queue Length, are not spiking at these times. So, that's telling me that there is something that is using the CPU at the same times the operators are. But it looks like I need to find out exactly what that is, but how????

 

[LCannon]

It turns out that my problem with my SQL Server was not, at least directly, related to SQL. I have another server connected to it that was having problems with a frame relay connection. The connection was delaying causing that server to work extra hard to keep the connection up. We ended up changing some configurations to allow for the delay and, almost like magic, my SQL Server settled down.