SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Minimizing Fragmentation when Moving data using a Switch

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.



Posted by Steve Jones on 18 March 2010

Nice tip, this is something that I can see biting people slowly over time, before they realize the issue is there.

Posted by Nadrek on 23 March 2010

Can we see some experimental evidence to back up the theory?  In particular, I'd like to see that MAXDOP=1 vs MAXDOP=(more) on a plan that actually gets a parallel execution plan makes a difference to fragmentation, as if any fragmentation does get introduced, it'd be in the combination phase (and doesn't have to introduce fragmentation at all, if combined properly, such as each thread generating a full, unfragmented extent and then handing those off to be written in order, round robin style).

Posted by dead_acid_head on 23 March 2010

Holy Moley Pat!  You could "fragment" that sentence in a couple of places! :)

Leave a Comment

Please register or log in to leave a comment.