Professional SQL Server® 2005 Performance Tuning

11.5. Section 4: Partitioned Tables and Indexes

Now you'll get started with digging into the details of some of these awesome new features, starting with one that you should be particularly excited about: the new partitioned tables and indexes. You'll start with why you would need to use this new feature, and then how you should use it. You'll also discover more about what partition tables and indexes are and find out more about how you use them.

11.5.1. Reasons for Using Partitioned Tables and Indexes

Partitioned tables are a way to spread a single table over multiple partitions, and while doing so each partition can be on a separate file group. There are several reasons for doing this, which are covered here.

11.5.1.1. Faster and Easier Data Loading

If your database has a very large amount of data to load, you might want to consider using a partition table. A very large amount of data doesn't mean a specific amount of data, but any time the load operation takes longer than is acceptable in the production cycle.

A partition table lets you load the data to an empty table that's not in use by the live data and so has less impact on concurrent live operations. Clearly there will be an impact to the I/O subsystem, but if you also have separate file groups on different physical disks, even this has a minimal impact on overall system performance.

Once the data is loaded to the new table, you can perform a switch to add the new table to the live data. This switch is a simple metadata ...