Blocking between two MERGE queries inserting into the same table

### Scenario I have a large table partitioned on an INT column. When I run two different MERGE statements on two different partitions of this table, they seem to be blocking each other. Sample code to recreate the scenario: #### 1. Preparation. Create tables and some dummy data

SET NOCOUNT ON
GO
--
--	Create parition function and partition scheme
--
	DROP FUNCTION IF EXISTS PF_Site_ID 
	GO
	CREATE PARTITION FUNCTION PF_Site_ID (INT)  
		AS RANGE RIGHT FOR VALUES 	(
										0,
										1, 2, 3, 4, 5, 6, 7, 8, 9, 10
									)
	GO  
	DROP PARTITION SCHEME PS_Site_ID
	GO
	CREATE PARTITION SCHEME PS_Site_ID
		AS PARTITION PF_Site_ID
		ALL TO ('PRIMARY')
	GO

--
-- Large table partitioned on Site_ID. Two STG tables. And some dummy data
--
	DROP TABLE IF EXISTS dbo.PartitionedLargeTable
	GO
	CREATE TABLE dbo.PartitionedLargeTable
	(
		  ID       INT          NOT NULL IDENTITY(1,1)
		, Site_ID  INT          NOT NULL
		, Name     VARCHAR(50)
	) ON PS_Site_ID (Site_ID)
	GO
	ALTER TABLE dbo.PartitionedLargeTable SET (LOCK_ESCALATION = AUTO)
	GO

--
--	STG tables
--
	DROP TABLE IF EXISTS dbo.STG_Test1
	GO
	CREATE TABLE dbo.STG_Test1
	(
		  ID       INT          NOT NULL IDENTITY(1,1)
		, Site_ID  INT          NOT NULL
		, Name     VARCHAR(50)
	) ON [PRIMARY]
	GO
	DROP TABLE IF EXISTS dbo.STG_Test2
	GO
	CREATE TABLE dbo.STG_Test2
	(
		  ID       INT          NOT NULL IDENTITY(1,1)
		, Site_ID  INT          NOT NULL
		, Name     VARCHAR(50)
	) ON [PRIMARY]
	GO

--
--	Dummy data
--
	INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name) SELECT 1, NEWID()
	INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name) SELECT 2, NEWID()
	GO 10000

	INSERT INTO dbo.PartitionedLargeTable (Site_ID, Name)
	SELECT Site_ID, Name FROM dbo.PartitionedLargeTable
	GO 5

	INSERT INTO dbo.STG_Test1(Site_ID, Name) SELECT 1, NEWID()
	GO 10000
	INSERT INTO dbo.STG_Test2(Site_ID, Name) SELECT 2, NEWID()
	GO 10000

	INSERT INTO dbo.STG_Test1 (Site_ID, Name)
	SELECT Site_ID, Name FROM dbo.STG_Test1
	GO 7

	INSERT INTO dbo.STG_Test2 (Site_ID, Name)
	SELECT Site_ID, Name FROM dbo.STG_Test2
	GO 7

#### 2. MERGE 1 In one SSMS window, run this MERGE statement:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ID, Site_ID, Name FROM dbo.STG_Test1) 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 THEN
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name);

#### 3. MERGE 2 In a second SSMS window, run this MERGE statement:

MERGE dbo.PartitionedLargeTable AS TGT

USING (SELECT ID, Site_ID, Name FROM dbo.STG_Test2) 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 THEN
    INSERT (Site_ID, Name)
    VALUES (SRC.Site_ID, SRC.Name);

--- The two MERGE statements run on different *Site_IDs* (so two different partitions). One of the performance benefits of partitioned tables is that we could manipulate partitions independently of each other (within reason). So, something like INSERT or UPDATE on one partition will not block similar operations on other partitions. Compare this to when the table is NOT partitioned, if we perform two large INSERT operations (or two large UPDATE operations), 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 with LOCK_ESCALATION = AUTO, which limits locks up to HOBT level (and not table). 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. The actual table I work with has a COLUMNSTORE CLUSTERED index on the partitioning scheme.