Powolne usuwanie rekordów, gdy wyzwalacz jest włączony

Myślałem, że to zostało rozwiązane za pomocą linku poniżej - obejście działa - ale łatka nie. Praca z pomocą techniczną Microsoft w celu rozwiązania.

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

Ok, więc mam problem, który chciałem wyrzucić do StackOverflow, aby sprawdzić, czy ktoś ma pomysł.

Uwaga: dotyczy to SQL Server 2008 R2

Problem: Usunięcie 3000 rekordów z tabeli zawierającej 15000 rekordów zajmuje 3-4 minuty, gdy wyzwalacz jest włączony i tylko 3-5 sekund, gdy wyzwalacz jest wyłączony.

Konfiguracja stołu

Dwie stoły nazwiemy Main i Secondary. Secondary zawiera rekordy elementów, które chcę usunąć, więc kiedy wykonuję usuwanie, dołączam do tabeli Secondary. Przed instrukcją delete działa proces, który zapełnia tabelę pomocniczą rekordami do usunięcia.

Usuń instrukcję:

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

Ta tabela ma wiele kolumn i około 14 różnych indeksów NC. Próbowałem kilka różnych rzeczy, zanim ustaliłem, że problem jest wyzwalaczem.

• Włącz blokowanie strony (domyślnie wyłączyliśmy)
• Zebrane statystyki ręcznie
• Wyłączone automatyczne gromadzenie statystyk
• Zweryfikowane zdrowie indeksu i fragmentacja
• Usunięto indeks klastrowany z tabeli
• Zbadano plan wykonania (nic nie wskazuje na brakujące indeksy, a koszt wyniósł 70 procent w stosunku do faktycznego usunięcia, a około 28 procent za połączenie / scalenie rekordów

Wyzwalacze

Tabela ma 3 wyzwalacze (po jednym dla operacji wstawiania, aktualizacji i usuwania). Zmodyfikowałem kod wyzwalacza usuwania, aby po prostu powrócił, a następnie wybierz taki, aby zobaczyć, ile razy jest uruchamiany. Wystrzeliwuje tylko jeden raz podczas całej operacji (zgodnie z oczekiwaniami).

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

Przypomnę

• Przy włączonym wyzwalaczu wypełnienie instrukcji zajmuje 3-4 minuty
• Przy wyłączonym wyzwalaczu wykonanie instrukcji zajmuje 3-5 sekund

Czy ktoś ma jakieś pomysły, dlaczego?

Zauważ też - nie chcę zmieniać tej architektury, dodawać usuń indeksy itp. Jako rozwiązanie. Ta tabela jest centralnym elementem niektórych głównych operacji na danych i musieliśmy ją dostosować i dostroić (indeksy, blokowanie stron itp.), Aby umożliwić działanie dużych operacji na współbieżności bez zakleszczeń.

Oto plan wykonania xml (nazwy zostały zmienione, aby chronić niewinnych)

<?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>

Struktura wersjonowania wierszy wprowadzona w SQL Server 2005 służy do obsługi wielu funkcji, w tym nowych poziomów izolacji transakcji READ_COMMITTED_SNAPSHOTi SNAPSHOT. Nawet wtedy, gdy żadna z tych poziomów izolacji są włączone, wiersz wersjonowania jest nadal używany dla AFTERwyzwalaczy (aby ułatwić generowanie insertedi deletedpseudo-tabel), Mars, oraz (w oddzielnym sklepie wersja) indeksowania online.

Jak udokumentowano , silnik może dodać 14-bajtowy postfiks do każdego wiersza tabeli, która jest wersjonowana do dowolnego z tych celów. To zachowanie jest stosunkowo dobrze znane, podobnie jak dodawanie 14-bajtowych danych do każdego wiersza indeksu, który jest przebudowywany online z włączonym poziomem izolacji wersji wiersza. Nawet jeśli poziomy izolacji nie są włączone, jeden dodatkowy bajt jest dodawany do indeksów nieklastrowanych tylko po przebudowaniu ONLINE.

Jeżeli spust PO jest obecny, i wersjonowanie w przeciwnym razie dodać 14 bajtów na wiersz, optymalizacja istnieje wewnątrz silnika, aby uniknąć tego, ale gdzie ROW_OVERFLOWlub LOBprzydział nie może wystąpić. W praktyce oznacza to, że maksymalny możliwy rozmiar wiersza musi być mniejszy niż 8060 bajtów. Przy obliczaniu maksymalnych możliwych rozmiarów wierszy silnik zakłada na przykład, że kolumna VARCHAR (460) może zawierać 460 znaków.

Zachowanie jest najłatwiejsze do zauważenia za pomocą AFTER UPDATEwyzwalacza, chociaż ta sama zasada dotyczy AFTER DELETE. Poniższy skrypt tworzy tabelę o maksymalnej długości w wierszu 8060 bajtów. Dane mieszczą się na jednej stronie z 13 bajtami wolnego miejsca na tej stronie. Istnieje wyzwalacz braku operacji, więc strona jest podzielona i dodano informacje o wersji:

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;

Skrypt generuje dane wyjściowe pokazane poniżej. Tabela jednostronicowa jest podzielona na dwie strony, a maksymalna długość wiersza fizycznego wzrosła z 57 do 71 bajtów (= +14 bajtów dla informacji o wersji wiersza).

DBCC PAGEpokazuje, że ma jeden zaktualizowany wiersz Record Attributes = NULL_BITMAP VERSIONING_INFO Record Size = 71, podczas gdy wszystkie inne wiersze w tabeli mają Record Attributes = NULL_BITMAP; record Size = 57.

Ten sam skrypt, UPDATEzastąpiony jednym wierszem, DELETEwyświetla pokazane dane wyjściowe:

DELETE dbo.Example
WHERE ID = 1;

W sumie jest o jeden wiersz mniej (oczywiście!), Ale maksymalny rozmiar wiersza fizycznego nie wzrósł. Informacje o wersji wiersza są dodawane tylko do wierszy potrzebnych do pseudo-tabel wyzwalacza, a wiersz ten został ostatecznie usunięty. Podział strony pozostaje jednak. Ta czynność dzielenia strony jest odpowiedzialna za powolną wydajność obserwowaną, gdy występował wyzwalacz. Jeśli definicja Padding2kolumny zostanie zmieniona z varchar(8000)na varchar(7999), strona nie będzie się dzielić.

Zobacz także ten post na blogu autorstwa SQL Server MVP Dmitrija Korotkevitcha, w którym omówiono również wpływ na fragmentację.

Cóż, oto oficjalna odpowiedź Microsoftu ... która moim zdaniem jest poważną wadą projektową.

11/14/2011 - Oficjalna odpowiedź uległa zmianie. Nie używają dziennika transakcji, jak wcześniej wspomniano. Używają magazynu wewnętrznego (poziom wiersza) do kopiowania zmienionych danych. Nadal nie mogą ustalić, dlaczego zajęło to tak długo.

Zdecydowaliśmy się użyć wyzwalaczy zamiast Zamiast zamiast wyzwalaczy po usunięciu.

Część PO wyzwalaczu powoduje, że musimy czytać dziennik transakcji po zakończeniu usuwania i budować tabelę wstawiania / usuwania wyzwalacza. W tym miejscu spędzamy ogromną ilość czasu i jest to zgodne z projektem PO części wyzwalacza. INSTEAD OF wyzwalałby takie zachowanie podczas skanowania dziennika transakcji i budowania wstawionej / usuniętej tabeli. Ponadto, jak zauważono, że rzeczy są znacznie szybsze, jeśli upuszczamy wszystkie kolumny z nvarchar (max), co ma sens ze względu na fakt, że są uważane za dane LOB. Zapoznaj się z poniższym artykułem, aby uzyskać więcej informacji dotyczących danych w wierszu:

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

Podsumowanie: PO wyzwalaczu wymaga ponownego skanowania dziennika transakcji po zakończeniu usuwania, wówczas musimy zbudować i wstawić / usunąć tabelę, co wymaga większego wykorzystania dziennika transakcji i czasu.

Tak więc jako plan działania proponujemy obecnie:

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.

Zgodnie z planem wszystko idzie poprawnie. Możesz spróbować napisać delete jako DOŁĄCZ zamiast IN, co da ci inny plan.

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

Nie jestem jednak pewien, ile to pomoże. Kiedy usuwanie działa z wyzwalaczami w tabeli, jaki jest typ oczekiwania dla sesji wykonującej usuwanie?