Automating Sliding Partition Windows - Part 4

Recurring Tasks

In the previous posts of this series I both explained the concept of the sliding window partition as well as the way I have configured my system for an automated sliding window. This post will delve into the automated part of the process, the tasks that recur on a regular, in my case monthly, basis.

I have decided to use an SSIS package to execute the various steps of the sliding window, but it is just as valid to wrap the steps into cursors or loops (obviously, you only need a loop if you have more than one table, partition scheme and/or function in your database). Depending on how much you plan on automating there are some challenges in using SSIS. I will be explaining how I've constructed my SSIS package, but it's outside the scope of this post series to explain how to use SSIS. There are many resources on the web that can help you if needed.

Automating Sliding Partition Windows - Part 3

It's been a long while since I've worked on this - and believe me I want to as every month I'm spending too much time manually sliding my various partitions! Which is very prone to mistakes.

In the meantime I found a really good post describing an automated sliding partition window that achieves something a little different. It works on the following premise:

• 7 day partitions that, at the end of each week, are merged into
• 5 weekly partitions that are then merged into 
• 6 monthly partitions that are finally merged into 
• 1 large archive partition

I'm looking at implementing something like this in my company's OLAP database and combined with SQL Server 2012's columnstore indexes I am hoping to get some great performance improvement!

Here's the post:
http://blogs.msdn.com/b/menzos/archive/2008/06/30/table-partitioning-sliding-window-case.aspx

Automating Sliding Partition Windows - Part 2

Sliding Partition Window Concept

Before we continue, let’s remind ourselves of what the Sliding Partition Window is and what it gets us. The whole point of the sliding window is to create a sort of data conveyor belt. New data comes into the production table and is placed in the empty partitions (and files where relevant) and the old data is “switched” or moved off to a staging table for further processing and, crucially, removal. In this way the table slides forward along its partitions but remains essentially the same size. There will always be a set number of partitions worth of data in your production table keeping it lean and fit. In addition, once you’ve moved the data from the staging tables to an archive or reporting database the physical files can be removed and your production database can be maintained at a manageable, near constant, size.

Automating Sliding Partition Windows - Part 1

Introduction

Table partitioning, as it was introduced in SQL Server 2005 Enterprise Edition, is an incredibly useful feature. It enhances the potential for performance tuning as well as data, well, err, partitioning - or to use another word data separation.

In terms of performance, partitioning a table and its indexes allows queries (assuming they are written to exploit the partitioning column) to target specific sets of data to reduce table or index scans and therefore improve (reduce) locking on large tables. If you add to this the ability to place a table’s individual partitions on separate filegroups, I/O contention can be reduced by separating the data further, potentially providing even greater performance gains.