As I said in a previous post, I spent the past 3 days in class with Kimberly Tripp (from SQLSkills.com) teaching us performance tuning & optimization for SQL 2000.  Here are some of the key points I took away from the class.  This list is an abridged version of my notes and far from encompassing all of the topics.  If you ever get the opportunity to take a SQL class from Kimberly I highly recommend you do so!

Key Take-Aways:

1. Do NOT use "Select *" queries!
1. This tells the query optimizer to not even bother using any of the indexes and goes straight to a table scan to gather the information.
2. Use "narrow" queries (fewer select columns) wherever possible to give the optimizer more options!
2. Use stored procedures whenever possible and reduce ad-hoc query usage!
1. This way you can control the dataset being queried and returned which allows you to tune your indexes accordingly.
2. Also allows for better security:
1. Give INSERT, DELETE, UPDATE access to the stored proc instead of to individuals.
2. Require an input parameter(s) for the SPs so that only a specific row/column can be UPDATED or DELETED preventing accidents that might occur in ad-hoc queries.
3. Do NOT name stored procedures to start with "sp_" because that is a special identifier used by SQL to denote System Procedures.  When a SP starting with "sp_" is called SQL looks for it in the Master database first instead of the current database which causes a hit to performance.
4. Indexes - Use fewer, wider indexes tuned to your biggest queries (SPs) instead of many, narrow indexes.
1. By adding non-clustered indexes with a few columns that are used extensively by SPs/queries you can significantly reduce the time for the queries.
2. Narrow indexes must still hit the clustered index or a table scan in order to reference additional columns.
3. Example:  For a query on Customer Fname & Lname you can create a NC-index on Fname & Lname instead of a NC-index on only Fname.
5. Non-Clustered indexes are always sorted by the order of the columns from left-to-right and in ascending order.
1. Keep this in mind if you have SPs that query for each of the two columns, but in different orders (i.e.: Fname & Lname).
6. Use the Index Tuning Wizard (ITW) for suggested index improvements after you create indexes on your PKs, FKs, UKs and for your large/frequently used SPs.
7. Create and tune your NC indexes for your expensive and frequently used SPs/queries. 
8. Regular index maintenance is a must (defrag & rebuild)!
1. Use DBCC SHOW_STATISTICS (tablename, indexname) to find out the current state of the index and the last time it was rebuilt.
9. Do NOT use Simple Recovery!
1. ...unless you don't care about losing data since your last full backup! 
2. Use Full Recovery with transaction log backups to retain all current data.
3. If disk space is an issue then set the transaction log backups to only be retained for a specified period of time then deleted.
10. Rebuild tables when they become highly fragmented or have low scan density (lots of empty space on leaf pages).
1. Use DBCC SHOWCONTIG (tablename) to find out this information.
2. To do this programatically you can use DBCC SHOWCONTIG (tablename) WITH TABLERESULTS to put the scan results into a table for further processing.