I am working with a database with roughly around 500MM records. We currently use a static partitioning method that partitions the data by an int identifier such at < 150MM goes on partition 1, > 150 && < 350 goes on 2 etc.
What dynamic partitioning schemes are available to better evenly distribute the data over the available partitions? Is there a way to allow for additional partitions to a dynamic scheme with minimal maintenance overhead?
Hashing is a common partitioning mechanism for your use case.. It is NOT available in SQL 2008: http://www.sqlservercentral.com/articles/partition/64740/.
The problem with hash partitioning is that if you add partitions, you need to redistribute the data. Logically speaking, any partitioning scheme that dynamically determines which partition the data goes into the data would need to redistribute the data after you add partitions (other than perhaps using a hash on the insert time + keeping track of when each partition was added). This assumes partitioning (distributing data via a predefined scheme to reduce subsequent lookup time) vs sharding (randomly distributing data to multiple boxes reduce processing time of the entire data set).
You can probably simulate a hash partition by generating the hash yourself on insert/update, and using a range based partition over the hashed value. For instance, lets say you want 10 partitions. Modulus divide a hash of your key value by 10, and range partition on the remainder (with each range taking one of the 10 possible values).
I agree with Chris. SQL Server only supports static, range partitioning. So, that the only way to separate values is by range of a certain column.
Having said that, Chris eludes to a larger possibility of "dynamicizing" a static range. He mentions a column with a hash value, but a simple "Partition" column (tinyint) will suit the bill just fine. Then, you can just update that value to move the row between partitions.
CREATE TABLE ArchiveData
(
...
Partition TINYINT NOT NULL
...
)
And the partition function:
CREATE PARTITION FUNCTION MyPartFunc (TINYINT) AS
RANGE LEFT FOR VALUES (1, 2, 3, 4);
You could write queries that check the number of rows on each partition and "graduate" the rows from one partition to the next, using simple update statements. This could be done as a nightly job.
Also, if you wanted to add partitions, it would be a fairly trivial task to create the filegroup, modify the partition function, and migrate the data to the new partition using update statements.
Having said all of this, I feel I should warn you that this technique is slow. Moving data between partitions is ugly business and if you do go down this path, I would move things slowly to the new partition, due to the huge performance hit that you will take. (The performance is bad enough that Oracle prevents you from updating the partitioning key, by default.)
Also, if you try this, you will want to keep an eye on your indexes and check for fragmentation.
