Another project was having some performance-related problems, so I was called in to review some of the stored procs to see if I could reduce the execution time. As a brief overview, this part of the application pulled data from a data warehouse and stored it in a data mart. Due to client requirements, all extract-transform-load was done via stored procs.
After taking some measurements (mostly time-related) and familiarizing myself with what was going on, I started trying to make improvements. One thing I noticed was that all the indexes were being created at the same time as the tables were, while they were empty. So I changed the process to have all indexes created as the last step. This reduced execution time by around 25 percent.
Why? Even though indexes usually speed performance, they have to be updated for every inserted row at the time of the insert. So SQL Server was updating several indexes per table on the insertion of each row (and there were millions of rows inserted). The indexes are necessary for the reporting part of the application, where response time is crucial. But it was much quicker in this instance to wait, so that the creation of the indexes would only involve 1 pass through the table per index, instead of 1 pass per row.
Probably the best thing to do would be to create the indexes on each table after it was filled with data, so it was more likely that the following stored procs would make use of the indexes. But it was very easy and simple to move the index creation from the beginning to the end, and the likelihood of introducing errors was very low. Sometimes this is more important when deadlines are looming than another couple of percentage points in performance.
For more info on performance tuning:
The definitive book I have found so far is Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by Ken England. If you haven't delved into query execution plans, statistics, covering indexes, etc., then pick up this book. I found it to be a good reivew and I learned a few things, even though I have worked on performance tuning for some time.
I have not read Microsoft SQL Server 2000 Performance Tuning Technical Reference, by Edward Whalen et al. It looks like a quality technical book, though.
Veritas is also offering a free ebook, The Definitive Guide to SQL Server Performance Optimization eBook. The only cost is to signup with a valid email address (hint). I've looked over it, and it is very good for the price.