Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test data, used to be <30ms each, now it's 100-200ms, but there are several, so the delay is becoming apparent in the UI).
migrated from stackoverflow.com Jun 11 '11 at 11:32
You can use
Also, turn on the client statistics in query analyzer to see the wait times on the client side.
I am assuming you hardware has not changed since your initial test, so since they are constant, I wouldn't doubt them.
Log system resources or look at task manager to see how many system resources are used by the processes.
Run the missing index DMV to see, well, missing indexes:
...and most expensive DMV queries
Otherwise, this SO question has good tips from me and other SQL high rep types: http://stackoverflow.com/q/4118156/27535 (I won't copy/paste all 3 longish answers)
Interesting thing that should be considered is the version of MSSQL 2000 running
There are four versions of the binaries
Each of those versions have limits in terms of RAM and CPU.
It is worth exploring the possibility that the amount of data currently stored has simply outgrown the capabilities of the version of MSSQL 2000 due to queries needing more RAM to fulfuill queries/subqueries or inadequate CPU utilization. You may require upgrading the binary version to the MSSQL 2000 Entrprise version (probably a long shot becasue of how old your version of MSSQL is) or the best version your budget can afford.
You may even want to get out of MSSQL 2000 since 2008 is the latest and has current support available. Again, this could be a budget issue. If you are already using Enterprise, or your budget cannot allow for any major upgrade, now you can explore DB Statistics or DB Design.
Disclaimer : I'm not a SQL Server DBA