Início / dba / Perguntas / 7205
Accepted
tsells
Asked: 2011-10-14 09:58:42 +0800 CST

Exclusão lenta de registros quando um gatilho está ativado

  • 772

Achei que isso foi resolvido com o link abaixo - a solução alternativa funciona - mas o patch não. Trabalhando com o suporte da Microsoft para resolver.

http://support.microsoft.com/kb/2606883

Ok, tenho um problema que gostaria de enviar ao StackOverflow para ver se alguém tem uma ideia.

Observe que isso é com SQL Server 2008 R2

Problema: excluir 3.000 registros de uma tabela com 15.000 registros leva de 3 a 4 minutos quando um acionador está ativado e apenas 3 a 5 segundos quando o acionador está desativado.

Configuração da mesa

Duas tabelas chamaremos de Principal e Secundária. Secundário contém registros de itens que desejo excluir, portanto, quando executo a exclusão, ingresso na tabela Secundária. Um processo é executado antes da instrução de exclusão para preencher a tabela secundária com os registros a serem excluídos.

Excluir Declaração:

DELETE FROM MAIN 
WHERE ID IN (
   SELECT Secondary.ValueInt1 
   FROM Secondary 
   WHERE SECONDARY.GUID = '9FFD2C8DD3864EA7B78DA22B2ED572D7'
);

Esta tabela tem muitas colunas e cerca de 14 índices NC diferentes. Eu tentei um monte de coisas diferentes antes de determinar que o gatilho era o problema.

  • Ative o bloqueio de página (desativamos por padrão)
  • Estatísticas coletadas manualmente
  • Coleta automática de estatísticas desativada
  • Integridade e fragmentação do índice verificado
  • Descartou o índice clusterizado da tabela
  • Examinou o plano de execução (nada aparecendo como índices ausentes e o custo foi de 70 por cento para a exclusão real com cerca de 28 por cento para a junção / mesclagem dos registros

Gatilhos

A tabela tem 3 gatilhos (um para operações de inserção, atualização e exclusão). Modifiquei o código para que a trigger delete apenas retorne, depois selecione uma para ver quantas vezes ela é disparada. Ele dispara apenas uma vez durante toda a operação (como esperado). 

ALTER TRIGGER [dbo].[TR_MAIN_RD] ON [dbo].[MAIN]
            AFTER DELETE
            AS  
                SELECT 1
                RETURN

Para recapitular

  • Com o Trigger ativado - a instrução leva de 3 a 4 minutos para ser concluída
  • Com o gatilho desligado - a instrução leva de 3 a 5 segundos para ser concluída

Alguém tem alguma ideia do porquê?

Observe também - não procurando alterar essa arquitetura, adicionar índices de remoção, etc. como uma solução. Esta tabela é a peça central para algumas das principais operações de dados e tivemos que ajustá-la e ajustá-la (índices, bloqueio de página etc.)

Aqui está o xml do plano de execução (os nomes foram alterados para proteger os inocentes)

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1790.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  <BatchSequence>
    <Batch>
      <Statements>
        <StmtSimple StatementCompId="1" StatementEstRows="185.624" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.42706" StatementText="DELETE FROM MAIN WHERE ID IN (SELECT Secondary.ValueInt1 FROM Secondary WHERE Secondary.SetTMGUID = '9DDD2C8DD3864EA7B78DA22B2ED572D7')" StatementType="DELETE" QueryHash="0xAEA68D887C4092A1" QueryPlanHash="0x78164F2EEF16B857">
          <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
          <QueryPlan CachedPlanSize="48" CompileTime="20" CompileCPU="20" CompileMemory="520">
            <RelOp AvgRowSize="9" EstimateCPU="0.00259874" EstimateIO="0.296614" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Delete" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Delete" EstimatedTotalSubtreeCost="0.42706">
              <OutputList />
              <Update WithUnorderedPrefetch="true" DMLRequestSort="false">
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_02]" IndexKind="Clustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[PK_MAIN_ID]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[UK_MAIN_01]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_03]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_04]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_05]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_06]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_07]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_08]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_09]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_10]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_11]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[UK_MAIN_12]" IndexKind="NonClustered" />
                <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[IX_MAIN_13]" IndexKind="NonClustered" />
                <RelOp AvgRowSize="15" EstimateCPU="1.85624E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.127848">
                  <OutputList>
                    <ColumnReference Column="Uniq1002" />
                    <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                  </OutputList>
                  <Top RowCount="true" IsPercent="false" WithTies="false">
                    <TopExpression>
                      <ScalarOperator ScalarString="(0)">
                        <Const ConstValue="(0)" />
                      </ScalarOperator>
                    </TopExpression>
                    <RelOp AvgRowSize="15" EstimateCPU="0.0458347" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="185.624" LogicalOp="Left Semi Join" NodeId="3" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.12783">
                      <OutputList>
                        <ColumnReference Column="Uniq1002" />
                        <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                      </OutputList>
                      <Merge ManyToMany="false">
                        <InnerSideJoinColumns>
                          <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                        </InnerSideJoinColumns>
                        <OuterSideJoinColumns>
                          <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                        </OuterSideJoinColumns>
                        <Residual>
                          <ScalarOperator ScalarString="[MyDatabase].[dbo].[MAIN].[ID]=[MyDatabase].[dbo].[Secondary].[ValueInt1]">
                            <Compare CompareOp="EQ">
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                                </Identifier>
                              </ScalarOperator>
                              <ScalarOperator>
                                <Identifier>
                                  <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                                </Identifier>
                              </ScalarOperator>
                            </Compare>
                          </ScalarOperator>
                        </Residual>
                        <RelOp AvgRowSize="19" EstimateCPU="0.0174567" EstimateIO="0.0305324" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="15727" LogicalOp="Index Scan" NodeId="4" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.0479891" TableCardinality="15727">
                          <OutputList>
                            <ColumnReference Column="Uniq1002" />
                            <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                            <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                          </OutputList>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Column="Uniq1002" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="ID" />
                              </DefinedValue>
                              <DefinedValue>
                                <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Column="RelationshipID" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[MyDatabase]" Schema="[dbo]" Table="[MAIN]" Index="[PK_MAIN_ID]" IndexKind="NonClustered" />
                          </IndexScan>
                        </RelOp>
                        <RelOp AvgRowSize="11" EstimateCPU="0.00392288" EstimateIO="0.03008" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3423.53" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0340029" TableCardinality="171775">
                          <OutputList>
                            <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                          </OutputList>
                          <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
                            <DefinedValues>
                              <DefinedValue>
                                <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="ValueInt1" />
                              </DefinedValue>
                            </DefinedValues>
                            <Object Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Index="[IX_Secondary_01]" IndexKind="NonClustered" />
                            <SeekPredicates>
                              <SeekPredicateNew>
                                <SeekKeys>
                                  <Prefix ScanType="EQ">
                                    <RangeColumns>
                                      <ColumnReference Database="[MyDatabase]" Schema="[dbo]" Table="[Secondary]" Column="SetTMGUID" />
                                    </RangeColumns>
                                    <RangeExpressions>
                                      <ScalarOperator ScalarString="'9DDD2C8DD3864EA7B78DA22B2ED572D7'">
                                        <Const ConstValue="'9DDD2C8DD3864EA7B78DA22B2ED572D7'" />
                                      </ScalarOperator>
                                    </RangeExpressions>
                                  </Prefix>
                                </SeekKeys>
                              </SeekPredicateNew>
                            </SeekPredicates>
                          </IndexScan>
                        </RelOp>
                      </Merge>
                    </RelOp>
                  </Top>
                </RelOp>
              </Update>
            </RelOp>
          </QueryPlan>
        </StmtSimple>
      </Statements>
    </Batch>
  </BatchSequence>
</ShowPlanXML>
sql-server performance

3 respostas

  1. A estrutura de controle de versão de linha introduzida no SQL Server 2005 é usada para oferecer suporte a vários recursos, incluindo os novos níveis de isolamento de transação READ_COMMITTED_SNAPSHOTe SNAPSHOT. Mesmo quando nenhum desses níveis de isolamento está ativado, o controle de versão de linha ainda é usado para AFTERacionadores (para facilitar a geração de insertede deletedpseudotabelas), MARS e (em um armazenamento de versão separado) indexação online.

    Conforme documentado , o mecanismo pode adicionar um postfix de 14 bytes a cada linha de uma tabela com versão para qualquer um desses propósitos. Esse comportamento é relativamente conhecido, assim como a adição dos dados de 14 bytes a cada linha de um índice reconstruído online com um nível de isolamento de controle de versão de linha habilitado. Mesmo onde os níveis de isolamento não estão ativados, um byte extra é adicionado aos índices não clusterizados somente quando reconstruídos ONLINE.

    Onde um gatilho AFTER está presente e o controle de versão adicionaria 14 bytes por linha, existe uma otimização dentro do mecanismo para evitar isso, mas onde uma alocação ROW_OVERFLOWou não pode ocorrer. LOBNa prática, isso significa que o tamanho máximo possível de uma linha deve ser inferior a 8.060 bytes. Ao calcular os tamanhos máximos de linhas possíveis, o mecanismo assume, por exemplo, que uma coluna VARCHAR(460) pode conter 460 caracteres.

    O comportamento é mais fácil de ver com um AFTER UPDATEgatilho, embora o mesmo princípio se aplique a AFTER DELETE. O script a seguir cria uma tabela com um comprimento máximo de linha de 8060 bytes. Os dados cabem em uma única página, com 13 bytes de espaço livre nessa página. Existe um gatilho no-op, então a página é dividida e as informações de versão adicionadas:

    USE Sandpit;
GO
CREATE TABLE dbo.Example
(
    ID          integer NOT NULL IDENTITY(1,1),
    Value       integer NOT NULL,
    Padding1    char(42) NULL,
    Padding2    varchar(8000) NULL,
    
    CONSTRAINT PK_Example_ID
    PRIMARY KEY CLUSTERED (ID)
);
GO
WITH
    N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
    N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
    N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R)
INSERT TOP (137) dbo.Example
    (Value)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM N4;
GO
ALTER INDEX PK_Example_ID 
ON dbo.Example 
REBUILD WITH (FILLFACTOR = 100);
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
CREATE TRIGGER ExampleTrigger
ON dbo.Example
AFTER DELETE, UPDATE
AS RETURN;
GO
UPDATE dbo.Example
SET Value = -Value
WHERE ID = 1;
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
DROP TABLE dbo.Example;

    O script produz a saída mostrada abaixo. A tabela de página única é dividida em duas páginas e o comprimento máximo da linha física aumentou de 57 para 71 bytes (= +14 bytes para as informações de controle de versão da linha).

    Exemplo de atualização

    DBCC PAGEmostra que a única linha atualizada tem Record Attributes = NULL_BITMAP VERSIONING_INFO Record Size = 71, enquanto todas as outras linhas na tabela têm Record Attributes = NULL_BITMAP; record Size = 57.

    O mesmo script, com o UPDATEsubstituído por uma única linha, DELETEproduz a saída mostrada:

    DELETE dbo.Example
WHERE ID = 1;

    Excluir exemplo

    Há uma linha a menos no total (é claro!), mas o tamanho físico máximo da linha não aumentou. As informações de versão de linha são adicionadas apenas às linhas necessárias para as pseudotabelas do acionador e essa linha foi excluída. A divisão de página permanece, no entanto. Essa atividade de divisão de página é responsável pelo desempenho lento observado quando o acionador estava presente. Se a definição da Padding2coluna for alterada de varchar(8000)para varchar(7999), a página não será mais dividida.

    Veja também esta postagem de blog do SQL Server MVP Dmitri Korotkevitch, que também discute o impacto na fragmentação.

    • 13
  2. Best Answer

    Bem, aqui está a resposta oficial da Microsoft...que eu acho que é uma grande falha de design.

    14/11/2011 - Resposta oficial alterada. Eles não estão usando o log de transações conforme declarado anteriormente. Eles estão usando o armazenamento interno (nível de linha) para copiar os dados alterados. Eles ainda não conseguem determinar por que demorou tanto.

    Decidimos usar gatilhos Em vez de depois de excluir.

    A parte AFTER do gatilho faz com que tenhamos que ler o log de transações após a conclusão das exclusões e criar a tabela inserida/excluída do gatilho. É aqui que gastamos a maior parte do tempo e é projetado para a parte DEPOIS do gatilho. O gatilho INSTEAD OF impediria esse comportamento de verificar o log de transações e criar uma tabela inserida/excluída. Além disso, como foi observado, as coisas ficam muito mais rápidas se descartarmos todas as colunas com nvarchar(max), o que faz sentido pelo fato de serem considerados dados LOB. Por favor, dê uma olhada no artigo abaixo para mais informações sobre dados In-Row:

    http://msdn.microsoft.com/en-us/library/ms189087.aspx

    Resumo: o gatilho AFTER requer a verificação do log de transações após a conclusão da exclusão, então temos que criar e inserir/excluir a tabela, o que requer mais uso e tempo do log de transações.

    Então, como plano de ação, é o que sugerimos neste momento:

    A) Limit the number of rows deleted in each transaction or
B) Increase timeout settings or
C) Don't use AFTER trigger or trigger at all or
D) Limit usage of nvarchar(max) datatypes.
    • 4

  3. De acordo com o plano, tudo está indo corretamente. Você pode tentar escrever a exclusão como um JOIN em vez de um IN, o que fornecerá um plano diferente.

    DELETE m
FROM MAIN m
JOIN Secondary s ON m.ID = s.ValueInt1
AND s.SetTMGUID = '9DDD2C8DD3864EA7B78DA22B2ED572D7'

    No entanto, não tenho certeza de quanto isso ajudará. Quando a exclusão está sendo executada com os gatilhos na tabela, qual é o tipo de espera da sessão que está fazendo a exclusão?

    • 1

relate perguntas