We have a large data warehouse table partitioned on an INT column. When multiple MERGE queries are executed on this partitioned table (each focusing on different partition of the same table) they seem to be blocking each other.

Example:

-- Large table partitioned on Site_ID. With each Partition containing millions of records.
CREATE TABLE dbo.PartitionedLargeTable
(
      ID       INT          NOT NULL IDENTITY(1,1)
    , Site_ID  INT          NOT NULL
    , Name     VARCHAR(50)
)
) ON PS_SiteID (Site_ID)

Merge statement looks like this:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ...) AS SRC
    ON  SRC.Site_ID = TGT.Site_ID
    AND SRC.ID      = TGT.ID

WHEN MATCHED THEN
    UPDATE 
        SET TGT.Name = SRC.Name

WHEN NOT MATCHED THEM
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name)

Two MERGE statements run on different Site_IDs (so two different partitions).

MSDN BoL says one of the performance benefits of partitioned tables is that we could manipulate partitions independent of each other (with in reason). So something like INSERT or UPDATE on one partition will not block similar operations on other partitions when LOCK_ESCALATION is set to AUTO.

Compare this to when the table is NOT partitioned, if we perform two large INSERT operations (or two large UPDATE operations) on the same table, then one blocks the other once the number of rows manipulated goes over a certain number (something like 3k or 5k rows), then the PAGE lock is escalated to TABLOCK. Hence INSERT blocks INSERT (or UPDATE blocks UPDATE)

To avoid such lock escalation to TABLOCK, this table was partitioned and LOCK_ESCALATION set to AUTO. But with MERGE, the blocking still happens

Any ideas on how to prevent this blocking? We have 10 parallel MERGE statements running, on 10 different partitions of this large table (and they are blocking each other).

The image below shows the nature of blocking. When a table is partitioned, the lock escalation is supposed to only go up to the partition (not to the whole table). When these MERGE statements are running, I see the HOBT id’s that each MERGE is querying (locking). And In some cases the HOBT ID does not match the partition IDs of this table.

See the image below.

Thanks!

List of things I tried:

• Tried with and without partitioning.
• Optimized the SELECT statement to only rely on CLUSTERED INTEGER columns
• Optimized MERGE statement to only rely on CLUSTERED INTEGER columns
• Checked the locks granted and they seem to be elevated
• Some HOBT level locks point to partitions that do not exist in the table, so this is confusing.
• LOCK_ESCALATION = AUTO
• COLUMNSTORE INDEX on partitioning key
• Reduced the total number of records for each iteration
• Monitored extended events to observe lock escalation events. It seems to happen
• and more

What I’d like to learn:

• How to reduce blocking between the MERGE statements