PHISSUG

This server is weird... it is an SQL server running SQL Server 2000 Enterprise Edition and this server sometimes responds really really really slow... coz my page queries upon load.  sometimes my page loads quick, sometimes it takes forever to load.  and so is my other applications, sometimes really slow sometimes okay.

 Of course, given that if 10 jobs are running simultaneously on SQL and is heavy on processing would slow down the server... BUT my question is, what if there is no job running and still performing sllloooooowwww?  I also tried checking the Administrator Tools -> Performance and checked on Processor utilization & Memory Available & Physical Disk % but all is on the bottom, no one is even spiking or loading anything BUT, i query really slow.  I also tried running Enterprise manager and just connecting to the DB takes like 30 seconds before it responds. clicking table tree will take around 20 secs and seems like hanging... this is weird. 

 * Processor Utilization - 2% to 5% utilization only

* Memory Available - 6GB, out of 8GB

* Disk Space Available - 380GB out of 500GB (only 120GB used)

* Physical Disk % - 0% to 5% utilization only

 I tried rebooting then it gets fixed.  But would happen again after like 1 to 2 days.  Can anyone help please?  I'm just out of options left to figure out why.

You should check the activity monitor (ops i think this is for sql server 2005 only, I dont know the equivalent in 2000). Check if there are processes that are running and how long it is running. check also  the blocking transactions.

I have encountered this one before. there is an stored procedure that is running in infinite loop.

Medyo insufficient ang info to really give you an asnwer. BUt off the bat, here are some things based from your post.

 * Memory Available - 6GB, out of 8GB

Activated ba yugn AWE? Configure SQL Server properly to utilize up to 8GB memory.

Or, it could be a rogue process that locks up a row or table that others are accessing. Check the list on locks and see what each of them locks. You may experiment on killing some processes to know its effect.

Or, it could be a bad query that takes forever to finish and locks up the entire system before it releases resources back to sql server.

Have you tried running the profiler on this or against or application?

Is this a new setup?

Or is this setup running perfectly fine before?

I have been in this kind of situation. I have done so many things to tune it up, and for 1 week, Our system is running very very slow,

until we found out that our clustered server disk is 90% fragmented. Check the Disk Fragmentation to check out how much is the fragmentation of the disk.

...

yeah could be fragmentation on the disk level.

you can also check on data page and index fragmentation, especially for very INSERT/UPDATE active databases. You may check out DBCC to know the health of your database.

Once, there is this bank that all of a sudden their database server crawled one evening and they could NOT get one user to operate a very very simple application. When i got there, i immediately suspected it was just a case of an index not being where it should be. I created the necessary index and voila ... it is back to sub second response time. Turned out that there was an index early on and was deleted.