Everything slow on sql server 2000

[Luvsql]

Just this morning, our applications are taking eons to login, and each screen change is very slow. The CPU and memory look normal. We are running sql server 2000 enterprise edition sp#4 on windows 2003 cluster server.

I can't see anything hardware which would explain how everything is so slow. Is there anyway to detect specifically what is causing everything to be so slow? Since it's all applications, it can't be anything database specific.

 

[onpnt]

That could be anything. Network, hardware other applications, your applications and on...

Sorry but your question is more like a help desk call.

Have you ruled out the network yet?
Can you log into SQL Server locally with normal performance?
Has anything changed with anything? Including windows updates, end user updates, network hardware, network configuration, is this going through a access list (group) or something like that on the routers? Is there some screen saver on the server running? Get the idea of where I'm coming from yet?

If you can log into the sql box remotely with analyzer of such and get the normal performance out of it then you are probably going to have to look elsewhere in your environment for the cause

[sub]____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own[/sub]

 

[katbear]

I would reboot!

 

[Luvsql]

Unfortunately, when running on a cluster, rebooting doesn't do anything except switch nodes. We had to get all 75 users logged off while I restarted the sql service, then everything was great.

However, this is the second time in 2 weeks I have had to do this. All seems to point to memory I believe but to find the one small query that may be causing it to spike is like finding a needle in a haystack. I cannot have profiler running 24 7 to a trace table.

 

[onpnt]

What are your memory settings on the sql server in question?

[sub]____________ signature below ______________
You may get help by means of code here. Just recall what happens to a rat when he eats the cheese off a rat trap while you're Ctrl+C/Ctrl+V'ing that code as your own[/sub]

 

[Luvsql]

We have sql set to dynamically configure the memory, but have it capped 3.5Gb of the total 4Gb. We also have the 3Gb switch set.

 

[ptheriault]

Do you have AWE enable in the db server.

run sp_configure. What is the awe setting?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Luvsql]

We have the /3GB switch set in the boot.ini file which enables sql server to use up to 3Gb of RAM. Since I've since it use more than 2Gb I can assume our settings are configured properly.

 

[ptheriault]

Have you tried to run queries right on the server. By doing this you can elimanate the SQL Server as part of the problem. (or the cause) if they run slow on the SQL Server too.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[Luvsql]

We have about 2-3000 stored procs in each database, so trying to narrow down which one to choose to test, would take eons.

 

[ptheriault]

I agree with that, but you need to find a few querries you can run that will give you an indication of performance issues.

You should also run sp_who2, and sp_lock. Denny wrote a nice version of sp_who2 name sp_who3. you can find it in the FAQ section of this forum.

Look for blocking or an excesive ammount of locks caused by a spid. Then you can use fn_getsql to find the SQL that is creating all the locks.

Here is fn_getSQL.

declare @handle binary(20)
select @handle = sql_handle 
from master.dbo.sysprocesses where spid = [b]Put SPID here[/b]
select * from ::fn_get_sql(@handle)

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Sorry I hit submit to soon. Basically you want to do what you can to determine where the bottle neck is occurring.
Is it the app server, network or sql? When I experience a performance issue I always start with the SQL Server. (I guess that cuz I'm the DBA) If SQL performance is fine. I'll check the app server. If that looks good I have the network admin check the network and nic cards.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[katbear]

You might find this article on performing a SQL Server Audit useful. There is a section on identifying the poorest performing queries using profiler, which would be a necessity in your case with so many sp's.

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