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.



The other major benefit partitioning brings is partition switching (which was introduced in SQL
Server 2008). This feature allows for the rapid and low overhead transfer of data from one table to
another. The process involves “switching”, i.e. moving, data from a live/production table, serving a busy website let’s say, to a staging table (on the same filegroup – there are limitations remember). Once the partitioned data is “switched” it is more easily available for resource intensive archiving or ETL processes with minimal negative impact on the production table. This is a huge improvement from previous versions of SQL Server.

This post will not go into the inner workings of partitioning. Nor will I delve beyond the basics of how to create partition functions, schemes or partition aligned indexes. There are plenty of resources on the web describing that. What will be covered is the Sliding Window concept that SQL Server 2008 and 2012 provide. Before you get too excited, I’m also not going to describe how the sliding window works. This is also pretty well covered by others:

• Intro to Sliding Partition Windows
• Brent Ozar's Partitioning Resources list

What I am going to deal with is automating the sliding window so that it is self-maintaining (well, as
much as can be expected!).

Sliding Partition Window Steps 

The sliding window requires the following steps to be performed:

• One-off tasks
• Create partition function
• Create initial filegroups and files
• Create partition scheme
• Create partitioned table or move existing table to new partitions
• Create a staging table
• Create filegroup-partition scheme mapping table
• Recurring tasks
• Create next filegroup and file(s) - if required
• Switch partition between production and staging tables
• Transfer data off of staging table and truncate staging table
• Set next filegroup for Partition Scheme
• Split last partition in the partition function
• Merge first partition in the partition function
• Remove empty file and filegroup from database and delete from server

Before I go any further, a good first step is to get an understanding of what and where your partitions are. The first link above is a very good article explaining the basics of the sliding partition window. In it there were some very useful sql scripts. One of which I now use all the time to view information about the partitions and partitioned data. I've made a small modification adding the actual file the partition uses.
My naming convention involves the prefix 'pf' for partition functions and 'ps' for partition schemes. Hence the where clause in my query filtering on partition scheme name like 'ps%'. 

My next post will delve more deeply into the individual steps outlined above.