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.
