Basically, with SQL Server 2000, there are only 2 things that can cause bad performance.
- Hardware
- Inefficient/inappropriate T-SQL code.
T-SQL code is relatively easy to debug and analyze (SQL Trace/Profiler, Query Analyzer's Show Execution Plan etc), but when you are fighting your hardware/OS guys, trying to prove that the performance bottleneck is somewhere on the server, or even worse, your data is getting corrupted, things do get a lot trickier.
Since we are talking about a relational database, the biggest factors hardware related are Disk and RAM. The more RAM, the more SQL Server can cache data, the less it has to ask the relatively slow disk subsystem for data. But, ultimately, the Disk Subsystem is the determining factor. If it is slow, having 4 x 3.2Ghz processors wait for it, is not helping the air conditioner in your server room to do less work, and your sweating does not help either. Sometimes, instead of finding bad performance, you fine data corruption. Yes, you are on UPS, the controller has battery backup and you don't just pull the power plug on the server to shut it down, but you still get corrupt log or data files.
Microsoft has a tool to help you stress the IO subsystem, in a manner similar to how a heavily loaded SQL Server would operate. SQLIOStress. This tool really bashes the IO subsystem and will expose issues related to the controller and disks. You can get it from Microsoft here on Microsoft Knowledge Base Article 231619.
Then there is SQLIO, the Disk Subsystem Benchmark Tool. It is there to help you determine the capabilities of your subsystem (or have a contest with your friends on who has the fastest disk subsystem). Get if from Microsoft Downloads here. It will tell you in a simple manner what the throughput is of your subsystem.
Both tools are supplied by Microsoft, but are not supported by PSS. Use at own risk.
Just being able to prove that the expected throughput is not what it was promised by the hardware vendor or the OS guys goes a long way to help you fend off those "Why is SQL server so slow?" questions.
On any new system, I run both tools, firstly to find and expose any hardware problems, and secondly to put my mind to rest that what is there, is actually going to run as expected. Removing the Disk Subsystem for the list of potential problems does make it easier to sort out those 3am server room sessions where the box needs to be up by 6am and you can't tell why you are continuously getting corrupt data.