Partition Switching for Large Data Loads

This is the first in a four-part series of blog posts explaining my journey using SQL Server’s partition switching functionality to load a very large Fact tables.

Partition switching is a Microsoft best practice data loading technique as described in Microsoft’s Data Loading performance guide for large data warehouse loads. Microsoft also claimed the world record for the fastest data load time for a Data Warehouse a few years back and the Partition Switching formed part of this project.

I am involved in a project that requires we load +- 25 Million records each day into a Fact table. As a result we identified Partition Switching as a must use technique for the Fact table data load.

When implementing a partition switching strategy a Staging Fact table with exactly the same structure as the actual Fact table must exist on the same filegroup. The data inserts into a Staging Fact Table where a simple Alter Table …. Switch statement switches the data into the Fact table. This makes for a very fast loading technique because only the Metadata in the filegroup updates and no actual data rows inserted into the potentially very large Fact table.

Before doing the actual Alter Table … Switch a number of steps has to be followed to prepare the database schema and data for the Partition Switch and this requires some work. The following TechNet article describes all the schema requirements to enable partition switching.

The next blog will explain the first section of technical work we need to do for our Partition Switch strategy.

Please comment and share your learning’s around partition switching using SQL Server.

Advertisements
This entry was posted in SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014 and tagged , , , , , . Bookmark the permalink.

2 Responses to Partition Switching for Large Data Loads

  1. Pingback: Partition Switching for Big Data Loads, Partition Fact Table | Big Data and Analytics

  2. Pingback: Partition Switching for Big Data Loads – Partition Switch In | Big Data and Analytics

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s