Yesterday was the first day of the SQL Server 2008 R2 airlift.  I attended a great session on Implementing a Fast Track Data Warehouse.   Lots of really interesting information was discussed during the meeting. However, an offline discussion about switching partitioned data in from a stage table provided a good tip that could help reduce fragmentation.

If you are constantly switching data into your partition table you could potentially introduce some unwanted fragmentation in your database.  The following steps should help to reduce the fragmentation:

1.       Instead of creating the stage table on the destination partition filegroup, create it on another filegroup that resides on a separate set of disks.

2.       Insert the data into the staging table.

3.       Create the clustered index for the stage table on the filegroup of the destination partition using a MAXDOP = 1.  This will ensure sequential  processing of the data, and moves the data to the destination filegroup.

4.      Perform the SWITCH into the partition, which will be a META data move.

When using the new Fast Track Warehouse methodology in SQL Server R2 minimizing fragmentation is vital to successful implementation. 

Talk to you soon,

Patrick LeBlanc

Founder www.TSQLScripts.com and www.SQLLunch.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.