Dynamic Partitioning : Wishlist
Whilst I consider dynamic partitioning something that really doesn’t serve a valid purpose that I can find yet, I decided to use it as an exercise to program a basic form of it within T-SQL over the coming weeks.
Given a blank piece of paper and some realism, what are the aims for the design and T-SQL:
- Batch based rebalancing – real-time is not realistic so let’s start with an overnight job.
- Choice to Balance by different metrics (Rows vs Physical Storage)
- Balance across a setup-defined fixed number of partitions – so that they do not run out.
- Ability to migrate Filegroups into and out of the Partition Scheme – e.g. schedule them for removal over the coming nights.
- Ability to limit the processing to a window – this is not easy, but a log of earlier migrations would offer guidance on how much processing could be done within an allotted time span.
- Ability to choose the specify the balancing as an online operation – partitioning being enterprise only we can automatically rely on online index rebuilds being available.
That’s not a bad start although I bet it is harder than it sounds.
Let’s just consider the ‘balancing act’ itself regardless of the options. A partition schema is not a database table – which automatically complicates matters since multiple tables and indexes can use the same partition schema. This means that any change to a partition scheme / function will directly affect more than 1 table / index. Any calculations for the number of rows / size of data will have to take all the tables and indexes into account.
It might seem unusual to place more than one table on a partition schema, but it really isn’t. You would commonly place any NC indexes also on the same partition schema to keep them ‘aligned’, so having multiple tables for the same ‘alignment’ purpose shouldn’t seem weird. If you consider the multi-tenancy usage of the partitioned table, then you can see why you could have dozens of tables all on the same partition schema.
These requirements are the starting point for the T-SQL and as I come across issues I will write them up.
SQL Fascination 
I encountered a real word dynamic partitioning situation. We have a fact table in a data warehouse which stores huge amounts of fact data by month. The current month’s data changes frequently and accessed frequently, the previous months data never changes but accessed frequently and the remaining data also never changes and accessed infrequently.
We access the fact table from a partitioned SSAS cube. The Cube has 3 partitions: Current, Previous and Historic data. To be able to process the current data fast the fact table also have to be partitioned the same manner if possible. Current, previous and historic partitions would be nice to create and maintain shifting data from one to the other month by month.