Table partitioning for SQL Server database fact tables is one of the best ways to improve processing for fact data loads during the ETL process and it also can dramatically improve query response time as well. SQL Server Table partitioning should be used on all but the most trivial database fact tables (and even then I would still use it). However, despite its great advantages and the fact that it has been around since SQL Server 2005, I still find it under-utilized.
Database table partitioning compliments Kimball Style Transaction or Snapshot Fact tables very nicely (See Slowly Changing Facts). In SQL Server 2012 the number of possible partitions per table has even been increase to 15,000! This great news because that means if you partition your database fact table by days it could hold more than 40 years’ worth of data without changing the partition scheme !!! And if the database fact table is loaded nightly with a single day’s data then SQL Server database table partition switching becomes a breeze.
I won’t go into all the details but SQL Server database table partition switching allows inserts and deletes to a partitioned database fact tables, no matter how large, to occur almost instantly. Updates can occur orders of magnitude faster as well (but I don’t recommend updating fact tables).
I am not going to go through all the steps. See Script for details
1. Create two tables and populate them with data:
1 . dbo.TablePatitionDemoFact – holds historical fact data
2. dbo.TablePatitionDemoFact_switch – simulates new rows to be loaded into fact table from nightly ETL process
7. selectdistinct[OrderDateKey] asTablePartitionDemoFact_switch
60398 – rows
20040731 – max transaction date
60398 – rows
20040801 - max transaction date
Both tables have about 60,000 rows but the _switch table has rows that need to be inserted in the fact.
2. To switch the rows by simply using the Alter Table… switch command
TO[dbo].[TablePartitionDemoFact] PARTITION (2)
Rerun the queries from before and you will see that rows have now been switched to the fact table in < 1 Second!
120,796 – row count
The process can easy be reversed if the data needs to be modified or archive/deleted by simply reversing the tables is the switch command.
TO[dbo].[TablePartitionDemoFact_switch] PARTITION (2)
60398 – row count
60398 – row count
Note: Both database table schemas must be identical. This includes indexes as well! During the switch process no table or indexes updates are required. There is just a simple pointer/metadata operation and the magic happens almost instantly!
For the purpose of this demo I didn’t create any indexes just to make things simpler but now you might understand why SQL Server 2012 Columnstore Indexes were not updateable! Because the assumption was that any decently sized database fact table is already partitioned, so therefore no need to make columnstore indexes updateable.
The best practice is to bulk load the data, in parallel if possible, into the database switch/stage table, using minimal logged transactions. Create the required SQL Server indexes and then simply switch the partitions. It is that simple. This process will perform well on fact tables with billions or even trillions rows!
Bret Ozar has put together a wonderful list of the most import Table Partitioning Resources
I personally like using SQL Server Partition Management tool from Code Plex