Loading Partitions in Parallel

One big load is taking longer, so adding workers sounds attractive. Loading partitions in parallel helps when each worker owns a distinct range and publication has a planned boundary.

Decide Whether Partitioning Helps the Load

Partitioning can reduce the amount of data each worker handles and make old ranges easier to replace. It does not guarantee that every query or load becomes faster. Check the partition key, source extract shape, target indexes, and reporting pattern before adding parallel workers.

I begin by drawing the ranges each worker owns. They must not overlap. If two workers update the same keys or indexes, extra sessions can increase blocking rather than reduce elapsed time. A range that is skewed can leave one worker busy after the others finish.

Ask what the publish step must guarantee. Can readers see some new partitions and some old ones during the switch sequence? If not, design a publication gate or a different target handoff. Parallel staging and atomic publication are separate decisions.

Give Each Worker Its Own Stage Table When Loading Partitions in Parallel

A separate staging table per target partition keeps workers from competing for one heap or clustered index. Match the target schema, data types, nullability, computed columns, constraints, and index layout. A SWITCH requires compatible structures. A stage table that differs in a small detail can stop publication after the long load is complete.

Name stage tables by range or run ID, and record their ownership in the run log. Do not let two runs reuse the same stage table at once. A unique run identifier makes cleanup and retry clear. Truncate or drop a completed stage only after its data is safely published and verified.

I check row range constraints before allowing a worker to start. They should prove that every row in the stage belongs to its intended partition. Without that proof, SWITCH can fail, or a poorly planned load can send rows to the wrong range.

Validate Boundaries Before Switching

Find the partition number from the partition function rather than hard coding a number from an old deployment. Boundary changes shift partition numbers. Keep the exact boundary value with the run record. Verify that the stage contains only the intended range and that key checks pass.

The target partition must meet the switch requirements, including the state of the destination. For a replacement, switch the old partition out to an empty compatible table first, then switch the new stage in. Plan what happens if the second step fails. Keep the old data available for recovery.

I prefer a rehearsal with one empty or test partition before scaling out. It catches index, constraint, and filegroup mismatches early. Waiting until every worker finishes to discover a mismatch is an expensive way to read an error message.

SELECT $PARTITION.pfSalesDate(CONVERT(date, '2025-01-01')) AS partition_number;
SELECT MIN(SalesDate) AS first_date, MAX(SalesDate) AS last_date,
       COUNT_BIG(*) AS staged_rows
FROM dbo.StageSales_202501;

Limit Concurrency When Loading Partitions in Parallel

Start with a small number of workers and measure source, storage, CPU, log, and lock pressure. More workers can saturate the transaction log or source system. A queue table can assign one partition to each worker and prevent duplicate claims. Mark a partition complete only after its stage validates.

Use separate transactions for stage loads when the ranges are independent. This keeps a failure in one worker from rolling back completed stages. It also means the coordinator must know which stages are ready, failed, or pending. A parent run row and one row per partition make that state visible.

The log is a shared resource too. Loading partitions in parallel can make insert streams contend on hot pages and indexes. If the log grows unexpectedly, check recovery model, batching, and log throughput rather than assuming the partitioning logic is wrong.

Understand the Locks at Publication

ALTER TABLE SWITCH is metadata focused when all requirements are met, but it still needs schema modification locks on the involved tables. A long report holding conflicting locks can delay the switch. A worker that tries to switch during peak reporting can turn a quick operation into a queue.

Keep transactions around SWITCH short. Do validation before entering the publication transaction. Set a reasonable lock timeout in the coordinator, log the failure, and retry according to an agreed policy. Do not hold a transaction open while waiting for another worker to finish.

I check active requests and blockers when a switch stalls. The lock manager does not care that the staging load was fast. A small metadata operation still has to wait its turn. Schedule publication for the window the readers can tolerate.

SELECT r.session_id, r.status, r.wait_type, r.blocking_session_id,
       r.command
FROM sys.dm_exec_requests AS r
WHERE r.database_id = DB_ID()
  AND r.session_id <> @@SPID;

Switch a Ready Partition Deliberately

Use a controlled coordinator to switch one validated stage at a time. Confirm the destination partition and the stage constraint. If replacing data, keep the switched out table until validation of the new target completes. That gives you a rollback path that does not depend on reextracting the source.

The command below illustrates a single ready stage. The table and partition number are examples, not universal values. Generate the number from the current partition function in your deployment script and verify compatibility first. Run this during a planned window with a recovery table prepared.

After the switch, compare target partition counts and key totals with the stage control totals. A successful command proves a structural move, not the business correctness of the rows. The validation belongs before and after publication.

ALTER TABLE dbo.StageSales_202501
SWITCH TO dbo.FactSales PARTITION 14;

Handle Failures When Loading Partitions in Parallel

If one worker fails, leave its stage and partition status intact for review. Do not rerun every successful worker unless the source contract requires a single snapshot. If the source changes between retries, keep the original extract boundary or rebuild the entire coordinated set.

A serial publication phase simplifies recovery. Mark each partition as published only after its switch and post check complete. If publication stops halfway, the log identifies which ranges are new and which remain old. Reports can be held behind a published batch marker if a mixed state is unacceptable.

Parallelism should reduce independent work. It should not hide ownership of rows or locks. Start with clear range boundaries, prove each stage, and publish through a short controlled step. That is how extra workers help instead of becoming extra witnesses to the same blocking chain.

Related reading on this blog: Aligned and Non-Aligned Indexes for Partitioning and Script to Get Partition Info Using DMV.

Parallel loading is not a race for one table, it is coordinated work on separate ranges.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.