Networks

Ask something
Discussion Locked

Problem with SQL 2000 server perofrmance

0 helpful
1 reply

by bnagy

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Every Wednesday our SQL 2000 server that runs our website crawls almost to a halt. The CPU exhibits unusually high activity in the 75%+ range (when this happens, the sqlsrvr.exe process is gobbling most of the CPU) and the average disk queue length runs in the 15-20 range which i know would indicate a bottleneck with the storage subsystem, but why only on Wednesdays. Is there a way to further pinpoint where the requests are coming from and the nature of the SQL requests that are causing so much activity? Our website is running cold fusion server 5 on windows 2000 server and the SQL database the supports it is on a separate windows server 2003 machine. Network traffice to the SQL server is nominal. Every day of the week other than Wednesday things run just fine.

1 reply

by bart777

It's possible that there is a runaway process or query giving you trouble.

Open the SQL query analyzer and run an SP_WHO command. This will show you who is connected to the DB's and who is actually running something. Once you determine who is in a runnable state track them down and see if they are running some overly large query. Most likely this is some scheduled thing that is trying to generate a report and is either a poorly written query or is using too large of a search index.

Good luck

This discussion is closed to new replies.