Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Who's overusing my SQL 2K Server?
- Thread starter LCannon
- Start date
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!
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!
- Thread starter
- #4
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.
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.
- Thread starter
- #5
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;
(good site)
At this point, I'd really appreciate any ideas. This is realy driving me nuts.
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;
(good site)
At this point, I'd really appreciate any ideas. This is realy driving me nuts.
- Thread starter
- #7
- Thread starter
- #10
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.
- Thread starter
- #12
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????
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????
- Thread starter
- #13
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.
- Status
Similar threads
- Locked
- Question
