DaveWentzel.com            All Things Data

Partitioned Tables

When you are designing your ETL process try to use partitioned tables whenever possible.  Here is a little primer I wrote on Partitioning a long time ago.   A fact table can be loaded offline then added to the partition scheme in a matter of milliseconds with no performance penalty for users executing queries.   This involves SWITCH (requires one side of the swap to be empty), SPLIT, and MERGE.  

A switched out partition is just a regular table.  Ensure you first add the necessary constraint to match the target partition immediately after the switch out. SWITCH is metadata only so your old partition can still be active while your ETL process is running without fear of locking issues. This increases concurrency dramatically, which may someday be a requirement for you, even if it isn't today. 

You have to design everything correctly so that data does not move from one partition to another.  SWITCH won't cause data movement, you are really just updating the metadata.  SPLIT can cause data movement if new, empty partitions are not the result of the SPLIT.  Same with MERGE, use it with at least one empty partition.  Always use the same filegroup for the source and target partitions.  Again, we don't want data to move from another file.  A SWITCH does require a SCH-M lock on both the source and target, meaning that you can have blocking issues if you do this on a busy transactional table.  But again, this should occur quickly.  

After a SWITCH run UPDATE STATS since stats are calculated at the table level. 

REBUILD INDEX can be done while the parition is switched out. If you need to load data to multiple partitions, switch out each partition to its own table, making concurrent load operations much easier. 

One drawback...it seems like a sort always happens regardless of whether you presort. This could be because I'm using SQL 2005, or could be because I'm doing something incorrectly.  I only determined this because I always check my load performance.  

Add new comment