主页 / dba / 问题 / 327047
Accepted
Fajela Tajkiya
Asked: 2023-05-13 04:13:12 +0800 CST

重写 DELETE 语句以减少 pagelatch_ex 等待

  • 772

我有以下 TSQL 语句，该语句通常在多个会话中并行执行，并导致严重的 pagelatch_ex 等待：

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

我捕获了工作负载，发现很多时候，传入的 @c3_list 导致没有行被删除。考虑到 DELETE 总是需要独占锁，我想知道以下重写是否可以帮助减少 pagelatch_ex 等待。它本质上将 DELETE 分为两个步骤：首先检查是否有需要删除的行，然后删除它们。

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;

我的逻辑是 select 语句仅采用 pagelatch_sh 锁存器。如果不需要删除任何行，则不会生成不必要的pagelatch_ex。

此重写有助于减少 pagelatch_ex 等待吗？预先感谢您的任何见解！

sql-server

1 个回答

  1. Best Answer

    那会有帮助的。sql 优化器也无法查看 string_split() 函数来查看有多少项，更不用说没有可用的统计信息。

    正如您所指出的，使用排它锁的删除语句的成本非常高，并且在开始遇到性能问题之前，“IN”子句中可以包含的项目数量是有限的，因为它操作一堆并发的 OR 语句。

    避免这些情况的最佳方法是首先确定是否有任何记录需要删除。您可以通过将删除语句包装在“IF EXISTS”中来做到这一点 -

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

    如果列表中没有任何内容，则删除语句将永远不会创建排他锁。

    由于你的表名为huge_table1，我猜其中有很多记录。由于您询问如何重写该语句，而不是询问可以对表进行哪些示意性修改（例如索引），因此这可能是重写它的最佳方法。

    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;

    当我并不真正关心第二个表中的数据，而是关心是否存在匹配的按位运算符时，我通常使用 EXISTS。另外，如果 c3 不是唯一列，则内部联接可以构建包含 #temp 表中的大量重复项的大表。临时表在这里很有用，因为在填充临时表时可以在其中存在统计信息，并帮助查询优化器了解它准备估计然后执行的任务类型。

    • 1

相关问题