6

We have a table that is currently ~4TB in size. We wish to introduce partitioning on this table. The table is already clustered on the key we wish to partition on.

The options appear to be

  • Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition (zero cost). Then call ALTER PARTITION FUNCTION for each partition we wish to introduce (definitely not zero cost)
  • re-create the clustered index using DROP_EXISTING=ON

We're not using SQL Enterprise, so there's no ability to do this online.

Running these queries locks the entire table.

  • Even if we accepted the lock, we have no real way of estimating how long this would take (and whether we could complete it in time over a weekend)
  • Most of the data is archive data, there's only a small slice of data that's actually 'live' and would be a problem when it's locked. Trying to figure out if there's a strategy using ALTER PARTITION and ALTER TABLE SWITCH that allows us to do most of this with the data swapped out on a staging table.
  • Can anyone suggest a way we can do this progressively?

Thanks!

Improve this question
14
  • 1
    Create the partitioned table as a new table, slowly copy rows in small batches. Eventually it'll be just a few rows behind, take am extremely short downtime to copy the final rows and rename the tables.
    – Sean Gallardy
    Commented Nov 9, 2017 at 12:07
  • @SeanGallardy-Microsoft thanks. The downside is that requires large volumes of additional storage, and a load of manual work (we have many databases we actually need to do this on). But yes, that's the best fall back option we can think of too. Was wondering if we could potentially do a partitioned view across the two tables as we shift the data (so we can then delete on the other side) - but not sure how we could actually do this progressively.
    – James Crowley
    Commented Nov 9, 2017 at 12:25
  • Will the non-partition table be updated during the migration?
    – Dan Guzman
    Commented Nov 9, 2017 at 12:26
  • 1
    You are currently writing new rows to the table, are they inserted in the order of the existing clustered index (e.g. is this on something that always increases, like identity or date/time)? If all new rows will land in the "active" partition you could definitely just create the partitioning scheme on a new table, change your insert process to write to that one, then you have all the time in the world to leisurely move the archive data over. Since that table is no longer written to, who cares about locking? A view could facilitate queries that need to span active and archived "partitions"...
    – Aaron Bertrand
    Commented Nov 9, 2017 at 13:30
  • 1
    @JamesCrowley why not use a partitioned view? It skips the need to rebuild indexes on a partition scheme/function.
    – Erik Reasonable Rates Darling
    Commented Nov 9, 2017 at 14:00

1 Answer 1

Reset to default
4

the table is append-only (new rows, no updates to existing ones)

Use ALTER TABLE SWITCH to switch the data of the table to a range-partitioned table that has only one partition for all the existing data, but for which new inserts will land in new partitions. Eg all row up to today land in one big partition, but new rows will fall into monthly partitions in the future.

Then perhaps later you have some time to split the big partition. If you use a RANGE RIGHT partition scheme, then you can split small partitions off of the RIGHT side of the large partition. If you use RANGE LEFT you can split small partitions off of the LEFT side.

Or, after you have switched to the new table you can INSERT the data from the old single partition into a new properly-partitioned staging table. Once that insert is done you can truncate the large partition, split the now-empty partition to match the staging table, and then switch the staging table in. Since the data in the old partition is read-only, you can perform the insert over time. Then the truncate, split, and switch-in are all metadata operations.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.