1

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!

2
  • 3
    Perhaps. But I would load the list into a temp table with a clustered index and ensure you're getting an optimal query plan first. You may just be touching and latching too many pages. Commented May 12, 2023 at 21:06
  • 1
    What indexes exist on dbo.huge_table1? How do you know pagelatch_ex locks are causing issues on your server?
    – J.D.
    Commented May 12, 2023 at 21:26

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.