MS SQL Server tuning is very similar to any other powerful Database Management System (DBMS), with similar performance and tuning considerations. For example:
1. How effective are the memory-resident database buffers at reducing disk I/O?
2. Where have you placed the database transaction recovery log files?
3. Does the database design minimize contention for SQL Server locks and latches?
4. Are high volume SQL Server queries fine-tuned to use appropriate index fields?
The SQL Server 2000 counters provide a wealth of performance statistics that can be used to these and other questions.
We recommend reading “Inside Microsoft SQL Server 2000” by Kalen Delany of the SQL Server development team and “Microsoft SQL Server 2000 Performance Tuning Technical Reference” by Ed Whalen (and others) if you are interested in finding out more about SQL Server performance and tuning.