Tuesday 24 September 2013

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.