Automatic Partition Splitting

Marlon Ribunal has a script to split partitioned tables automatically:

So, let’s pretend it’s the month of April 2017 and this is the partition currently populated. Based on the query above, aside from the current partition bucket, we also have another available bucket month for May.

Say we want to maintain 3 available buckets at any given time. The next available bucket is May, so that means we need 2 more partitions to cover for June and July.

Read on for more, including some scripts that you can automate.

Related Posts

Cross-Server Partition Elimination

Derik Hammer shows an example of “old-style” partitioning across servers: SQL Server has a feature for partitioning tables and indexes. Partitioning can be implemented at many levels, however. You can create multiple tables for one logical data set, you can split the set into multiple databases, and you can even split it among different servers. Sharding is another […]

Read More

Locks And Partitioning

Erik Darling looks at the confusing mess that is SQL Server partitioning: In the Chicago perf class last month, we had a student ask if partition level locks would ever escalate to a table level lock. I wrote up a demo and everything, but we ran out of time before I could go over it. […]

Read More

Categories

January 2018
MTWTFSS
« Dec Feb »
1234567
891011121314
15161718192021
22232425262728
293031