Merging partitions – which direction should I merge?

When you work with large tables in SQL Server 2005 and 2008, you will inevitably start using partitioned tables in order to ease the maintenance of these tables, and improve query performance. I recently wanted to merge several partitions at the tail of an existing partition function, and I wanted to understand whether I need to merge the partitions in a sequential order from the last partition and work my way up the chain of partitions, or would it be better to start from the newest partition (that I can merge) and work my way down the chain of partitions. The following diagram probably explains this better:

I figured this has something to do with the way we have defined the partition function boundary as being a LEFT boundary or a RIGHT boundary, but I wanted to be sure, so I performed the following test. First, I created a new table, “FactInternetSales_Partitioned”, based on the “FactInternetSales” table from the AdventureWorksDW sample database. Next I created a partition function and a partition scheme which defines the boundary as a LEFT boundary:

I then created a clustered index on the “FactInternetSales_Partitioned” table, on the new partition scheme:

And then I started to merge partitions, first from 20010701, going up…

For each merge operation, I could tell by looking at the statistics information, how the logical reads increase by the size of the growing partition being moved:

Now, performing the merge operation from 20010705, going back in time, resulted in a different IO pattern, and you can see that the statistics information shows that each merge operation only moves data from a single partition:

So, there you have it: if you’re using a LEFT bounded partition function, you’d do better merging partitions from “new” to “old” and not the other way around. I also checked it with the RIGHT bounded partition function and, as expected, the results were the exact opposite: merging partitions from “old” to “new” incurred the least amount of IO operations.

-Yaniv

(This is post number 5 for week number 5 of my SQL blog challenge)