Sql-server – Partitioning a 4TB table while staying online (SQL Server Standard)

partitioningsql server

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!

Best Answer

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.