If you are having trouble performance tuning SQL Server 2000, there is one whitepaper you need. It’s called “SQL Performance Tuning using Waits and Queues” and it’s written by Tom Davidson. It takes the guesswork and voodoo out of tuning.
The way it work is you run a stored procedure (it’s in the whitepaper) and it finds out what SQL Server is waiting on, which gives you a feel for where you need to focus your efforts to tune SQL Server.
I highly recommend reading this paper and using it to help if you are having performance problems with SQL Server 2000.
For an awesome example of how this would work, check out the SQLCAT blog here: http://blogs.msdn.com/sqlcat/archive/2005/09/05/461199.aspx.
You can find the script itself in this presentation: http://www.sqldev.net/events.htm#SQL_Connections_12-15_October_2003,_Palm_Springs_CA_ ( it’s this one: SPR201 – 4 – Performance Tuning – Using Waits & Queues.zip)
And the table to help you determine what to do is here: http://www.sqlmag.com/Files/09/40925/Webtable_01.doc
The whole thing joined together is available from SQL Server Magazine, but you need a subscription to access it.