Início / dba / Perguntas / 327047
Accepted
Fajela Tajkiya
Asked: 2023-05-13 04:13:12 +0800 CST

Reescrevendo uma instrução DELETE para reduzir esperas de pagelatch_ex

  • 772

Eu tenho a seguinte instrução TSQL, que geralmente é executada em várias sessões em paralelo e leva a sérias esperas de pagelatch_ex:

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

Capturei a carga de trabalho e descobri que muitas vezes o @c3_list passado não resulta em nenhuma linha a ser excluída. Considerando que DELETE sempre requer um bloqueio exclusivo, gostaria de saber se a reescrita a seguir poderia ajudar a reduzir as esperas de pagelatch_ex. Essencialmente, ele divide o DELETE em duas etapas: primeiro verificar se há alguma linha que precisa ser excluída e, em seguida, excluí-la.

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;

Minha lógica é que uma instrução select usa apenas a trava pagelatch_sh. Se nenhuma linha precisar ser excluída, nenhum pagelatch_ex desnecessário será gerado.

Essa reescrita ajudaria a reduzir as esperas de pagelatch_ex? Agradecemos antecipadamente por qualquer insights!

sql-server

1 respostas

  1. Best Answer

    Isso ajudaria. Também não há como o otimizador sql examinar a função string_split() para ver quantos itens existem, sem mencionar que não há estatísticas disponíveis.

    As instruções de exclusão são muito caras com bloqueios exclusivos, como você apontou, e há uma limitação em quantos itens você pode ter em uma cláusula 'IN' antes de começar a ter problemas de desempenho, uma vez que ela opera várias instruções OR simultâneas.

    A melhor maneira de evitá-los é determinar primeiro se há algum registro a ser excluído. Você pode fazer isso agrupando a instrução delete em um 'IF EXISTS' -

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

    Se não houver nada na lista, a instrução delete nunca criará o bloqueio exclusivo.

    Como sua tabela se chama enorme_table1, suponho que haja muitos registros nela. Já que você perguntou como poderia reescrever a instrução e não quais modificações esquemáticas você poderia fazer na tabela, como índices, esta é provavelmente a melhor maneira de reescrevê-la.

    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;

    Normalmente uso EXISTS quando não estou realmente preocupado com os dados da segunda tabela, mas sim com um operador bit a bit para saber se há uma correspondência ou não. Além disso, se c3 não for uma coluna exclusiva, uma junção interna poderá criar grandes tabelas com muitas duplicatas da tabela #temp. As tabelas temporárias são boas aqui porque podem existir estatísticas sobre elas quando são preenchidas e ajudam o otimizador de consulta a entender que tipo de tarefa ele está se preparando para estimar e depois executar.

    • 1

relate perguntas