Session blocks itself

Question

I'm running a procedure which performs MERGE. sp_who2 shows that session blocks itself. How is that possible? My sp looks like this

CREATE PROC [dbo].[sp] as  
BEGIN  
    MERGE dbo.table as target  
    USING (  
        select field1, field2
        from dbo.view
    ) as source (field1, field2)  
    ON target.field1=source.field1 and target.field2=source.field2
    WHEN NOT MATCHED BY SOURCE THEN  
    DELETE  
    WHEN NOT MATCHED BY TARGET THEN  
    INSERT (field1, field2) VALUES (source.field1, source.field2);  
END

dbo.table is massive huge table. Has IX_table(field1) and PK_table(field2,field1)

sp_who2 looks like this

Version -

Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)
Jul 12 2019 17:43:08 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

This is due to thread coordination (CXPACKET waits) the parallel query. — Dan Guzman

Brian Pressler Brian Pressler · Accepted Answer · 2019-10-03T18:33:29

You can try getting rid of the sub-query first. If that doesn't work try adding the OPTION (MAXDOP 1) below to remove parallelism from the query.

MERGE dbo.table as target  
USING dbo.view AS source
ON target.field1=source.field1 and target.field2=source.field2
WHEN NOT MATCHED BY SOURCE THEN  
DELETE  
WHEN NOT MATCHED BY TARGET THEN  
INSERT (field1, field2) VALUES (source.field1, source.field2)
OPTION (MAXDOP 1)

Session blocks itself

1 Answers