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!