I recently watched a great movie called “Sliding Doors” with Gwyneth Paltrow. When I say “with”, I mean she was in the movie in the starring role, not that I watched it actually with her. Do you think the likes of Ms Paltrow would hang out with your average DBA? Not likely. No, she hangs out with your average rock star. (Namely, Chris Martin of Coldplay). It’s a clever movie about what might happen if something fairly trivial like missing a train by seconds may affect one’s life and it follows the two parallel threads of life, one where she makes the train and one where she doesn’t. So what has this got to do with SQL Server you may ask? You might think I would harp on about parallel threads and multi-tasking but no. This reminded me about Data Partitioning using the “Sliding Window” scenario. Sad segue but true.
Data Partitioning is a new feature of SQL Server 2005. It enables you to store large tables across multiple devices giving performance and manageability benefits. It’s a feature that has been in Oracle and DB2 for a while, but now Microsoft is competing with the big guys in the VLDB (Very Large Database) space so this scalability feature is very important. Multi-Terrabyte databases are becoming more common especially in the Business Intelligence arena. The idea is to be able to store a table’s rows on different devices based on a column value within that row, for instance, Calendar Month, so that all similar rows within a table are stored in one place. So we may choose to store each month’s data on a separate “partition” which could be on a separate physical device. When we need data from one particular month, in this example, the system would only need to access one device. If we query data across many months we have multiple disk volumes working in parallel giving better performance too. SQL Server uses the Filegroup object in order to define partitions. A “partition function” defines the boundary values of the partitions, a “partition scheme” defines the Filegroups that represent partitions and the CREATE TABLE statement identifies the partition scheme (and its associated function) to be used. The system will then work out, based on a single column value, which partition a new row should be stored on.
But the manageability features, although not so obvious, may be just as important in the long run. Say, we have to store the last 12 months order data for a large multi-national retail company in a table. We may have millions or even billions of rows of data. We also want to archive data older than 12 months to an archive table. What we would be faced with, without partitioning, is to copy millions of rows at the end of each month to an archive table with the same structure. Well, with partitioning, we can just flick a switch (actually the ALTER TABLE SWITCH statement) and the partition instantly becomes part of the archive table with no data movement necessary. That could save a lot of time in our already busy 24×7 environment. To create a new empty partition for the new month’s data, we can use the SPLIT RANGE clause of the ALTER PARTITION FUNCTION statement. To remove unused empty partitions we can use the MERGE RANGE clause of the same statement. In this way, we can define a “Sliding Window” of data with no need to copy and delete huge amounts of data. Pretty clever, eh?
Maybe not as clever as “Sliding Doors” but still…
Cheers
Brian
Designing Partitions to Manage Subsets of Data:
https://msdn.microsoft.com/en-us/library/ms191174.aspx
Recent blog posts…
