Rewriting a DELETE statement to reduce pagelatch_ex waits

Question

I have the following TSQL statement, which is often executed in multiple sessions in parallel and leads to serious pagelatch_ex waits:

DELETE dbo.huge_table1  
WHERE c1 = 0  
      AND c3 IN (SELECT v FROM dbo.string_split(@c3_list));  

I have captured the workload and found that many times, the passed in @c3_list results in no rows to be deleted. Considering DELETE always requires an exclusive lock, I was wondering if the following rewrite could help reduce the pagelatch_ex waits. It essentially splits the DELETE into two steps: first checking if there are any rows that need to be deleted, and then deleting them.

INSERT INTO #tmp (c3)   
SELECT c3  
FROM dbo.huge_table1  
WHERE c1 = 0   
      AND c3 IN (SELECT v FROM dbo.string_split(@c3_list) );   

IF @@rowcount > 0
    DELETE t1 
    FROM dbo.huge_table1 t1 
        JOIN #tmp t2  
            ON t2.c3 = t1.c3; 

My logic is that a select statement only takes pagelatch_sh latch. If no rows need to be deleted, no unnecessary pagelatch_ex would be generated.

Would this rewrite help reduce the pagelatch_ex waits? Thanks in advance for any insights!

Answer 1

That would help. There is also no way for the sql optimizer to look into that string_split() function to see how many items there are, not to mention that there are no statistics available.

Delete statements are very costly with exclusive locks, as you have pointed out and there is a limitation in how many items you can have in an 'IN' clause before you start running into performance issues, since it operates a bunch of concurrent OR statements.

The best way to avoid these is to determine if there are any records to delete in the first place. You can do so by wrapping the delete statement in an 'IF EXISTS' -

IF EXISTS (SELECT 1 FROM dbo.string_split(@c3_list) AS ss )
BEGIN
-- Delete stuff
END;

If there is nothing in the list, the delete statement will never create the exclusive lock.

Since your table is called huge_table1, I'm guessing there are a lot of records in it. Since you asked how you could rewrite the statement and not what schematic modifications you could make on the table, like indexes, this is probably the best way you can rewrite it.

IF EXISTS (SELECT 1 FROM dbo.string_split(@c3_list) )
BEGIN
    SELECT v AS c3
    INTO #temp
    FROM dbo.string_split(@c3_list) AS ss;

    DELETE ht1
    FROM dbo.huge_table1 AS ht
    WHERE c1 = 0
          AND EXISTS
    (
        SELECT 1 FROM #temp WHERE c3 = ht.c3
    );
END;

I typically use EXISTS when I'm not really concerned with the data in the second table, but rather a bitwise operator as to whether there is a match or not. Also, if c3 is not a unique column, an inner join can build big tables with a lot of duplicates from the #temp table. Temp tables are good here because statistics can exist on them when they are populated and help the query optimizer get an understanding for what kind of task it's getting ready to estimate, and then execute.