Tom Davidson had a couple of very good posts on the SQLCAT Blog back in early 2006 on how to manage database schema changes without having an overly negative effect on concurrency and availability. The posts are here and here.
I often go back to those posts when I am considering a schema change for my Production System. If you have a large, busy, 24x7x365 system, you really need to think about these issues, since one mistake can cause you lots of headaches. Along those lines, here are a couple of other bits of advice:
1. Prefer Enterprise Edition over Standard Edition
This allows you to do use the ONLINE option for most Index creation operations.
2. Use the MAXDOP setting when creating Indexes
This will let you limit the number of CPU’s that are used to create the index. This will make the index take longer to build, but will keep the index build from monopolizing all of your CPUs.
3. Use TOP When doing UPDATES and DELETES of large batches
Overall, it is usually much less costly to do Set based operations when working with SQL Server 2005. The exception to this is if you have to UPDATE millions of rows on a busy table without having a maintenance window. Rather than trying to UPDATE 10 million rows in one set based operation, in some cases, you can "nibble" away at it by doing UPDATE TOP (5000) in a loop. The overall system cost will be higher, and it will take longer, but you will not lock the table (and cause blocking, timeouts, etc.)