Sliding Window Partition Strategy with Integrated Logging

This post outlines, details, and provides the scripts for a  "Sliding Window Partition" scheme in SQL Server.  I explored this solution because of an issue we had at work with read/write contention on high-volume (250M records/day) log tables.  This post focuses on the mechanics and best practices of the "Sliding Window".  My work is based on Brent Ozar's, which is GREAT, but I stratified his scripts into smaller pieces and augmented the functionality to include logging to support better troubleshooting.  This solution also includes some widgets that we use as a standard in our ETL to log and greatly enhance monitoring/reporting for the ETL process.  The solution also has a spin-off logging element that is specific to the Partitioning element.




This solution is also meant to be a design pattern that we are going to roll-out to a dozen different logging tables that we need to transform raw data into something that can be used by the BI team.

In the future, I will likely spin off some posts to detail the logging elements and the Tableau tools we use to keep an eye on everything and minimize our response time when things wiggle (spiral) out of control.

With that said, this post is all about creating a very clean Sliding Window Partition and creating the tools needed to monitor a daily process of "Swapping In" new data and "Swapping Out" old data while maximizing the availability of the data assets and minimizing/eliminating waits on the data structures.

The solution is stratified into several different realms:

• Global Setup: Create a schema for our partition objects, create views to query how the partition is structured and where data is being stored across the partitions.
• Process Setup: Define partition function and scheme for a particular Logging table.  
• Data Load (Optional for testing): Widget to load data into the partition and stage tables to help valid the switching process
• Process Execution: scripts to Switch In and Switch Out.
• Reset and Test Again: Scrap the non-global pieces and re-run for quicker testing
• Troubleshooting and Monitoring: Scripts to look at the partitions, stage tables, and other helper tools.

Partition Sliding Window SQL Scripts

NOTE: This post and supporting scripts were published when I was ready to migrate from my DEV environment into a staging/QA environment and test with real production data volume.  I made a handful of mods and enhancements before I actually went to production...but those changes are geared towards a real world implementation and don't match up with the dummy database used in the initial scripts shared with this post.   I'm more than happy to share the changes that we put in place as we matured the process, but they are not plug and play for a dev environment.

Testing is almost complete and we're are set to deploy into production later this week.

Get your partition on!


Marc