Bloqueio entre duas consultas MERGE inseridas na mesma tabela

Cenário

Eu tenho uma tabela grande particionada em uma INTcoluna. Quando executo duas MERGEinstruções diferentes em duas partições diferentes desta tabela, elas parecem estar bloqueando uma à outra.

Exemplo de código para recriar o cenário:

1. Preparação. Crie tabelas e alguns dados fictícios

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. MESCLAR 1

Em uma janela do SSMS, execute esta MERGEinstrução:

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. MESCLAR 2

Em uma segunda janela do SSMS, execute esta MERGEinstrução:

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);

As duas MERGEinstruções são executadas em Site_IDs diferentes (portanto, duas partições diferentes).

Um dos benefícios de desempenho das tabelas particionadas é que podemos manipular partições independentemente umas das outras (dentro do razoável). Portanto, algo como INSERTou UPDATEem uma partição não bloqueará operações semelhantes em outras partições.

Compare isso com quando a tabela NÃO está particionada, se realizarmos duas INSERToperações grandes (ou duas UPDATEoperações grandes), então uma bloqueia a outra quando o número de linhas manipuladas ultrapassa um certo número (algo como 3k ou 5k linhas), então o PAGElock é escalado para TABLOCK. Portanto, INSERTblocos INSERT(ou UPDATEblocos UPDATE)

Para evitar esse escalonamento de bloqueio para TABLOCK, esta tabela foi particionada com LOCK_ESCALATION = AUTO, que limita os bloqueios até o nível HOBT (e não a tabela). Mas com MERGEo bloqueio ainda acontece.

Alguma idéia de como evitar esse bloqueio? Temos 10 MERGEinstruções paralelas em execução, em 10 partições diferentes desta grande tabela (e elas estão bloqueando umas às outras).

A imagem abaixo mostra a natureza do bloqueio. Quando uma tabela é particionada, o escalonamento de bloqueio deve ir apenas até a partição (não até a tabela inteira). Quando essas MERGEinstruções estão em execução, vejo os IDs do HOBT que cada um MERGEestá consultando (bloqueando). E em alguns casos, o ID HOBT não corresponde aos IDs de partição desta tabela.

A tabela real com a qual trabalho possui um COLUMNSTORE CLUSTEREDíndice no esquema de particionamento.