A Couple of Good Older Posts from SQLCAT On Schema Changes in SQL Server 2005

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.)


Technorati Tags:
This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s