Plan wykonania sugeruje, że każda kolejna pętla wykona więcej pracy niż poprzednia. Zakładając, że wiersze do usunięcia są równomiernie rozmieszczone w całej tabeli, pierwsza pętla będzie musiała zeskanować około 4500 * 221000000/16000000 = 62156 wierszy, aby znaleźć 4500 wierszy do usunięcia. Wykona również tę samą liczbę wyszukiwań indeksu klastrowego względem vendortabeli. Jednak druga pętla będzie musiała odczytać te same wiersze 62156 - 4500 = 57656, które nie zostały usunięte za pierwszym razem. Możemy się spodziewać, że druga pętla przeskanuje 120000 wierszy zi MySourceTablewykona 120000 wyszukiwań względem vendortabeli. Ilość pracy potrzebnej na pętlę rośnie w tempie liniowym. W przybliżeniu możemy powiedzieć, że średnia pętla będzie musiała odczytać 102516868 wierszy zi MySourceTabledo 102516868 szuka względemvendorstół. Aby usunąć 16 milionów wierszy o wielkości partii 4500, kod musi wykonać 16000000/4500 = 3556 pętli, więc łączna ilość pracy do wykonania przez kod wynosi około 364,5 miliarda wierszy odczytanych MySourceTablei 364,5 miliarda indeksów.
Mniejszy problem polega na tym, że używasz zmiennej lokalnej @BATCHSIZEw wyrażeniu TOP bez żadnej RECOMPILEinnej wskazówki. Optymalizator zapytań nie pozna wartości tej zmiennej lokalnej podczas tworzenia planu. Zakłada się, że jest równa 100. W rzeczywistości usuwasz 4500 wierszy zamiast 100, i możesz ewentualnie skończyć z mniej wydajnym planem z powodu tej rozbieżności. Niska ocena liczności przy wstawianiu do tabeli może również spowodować obniżenie wydajności. SQL Server może wybrać inny wewnętrzny interfejs API do wstawiania, jeśli uzna, że musi wstawić 100 wierszy zamiast 4500 wierszy.
Jedną z możliwości jest po prostu wstawienie kluczy podstawowych / kluczy klastrowych wierszy, które chcesz usunąć, do tabeli tymczasowej. W zależności od wielkości twoich kluczowych kolumn może to łatwo zmieścić się w tempdb. W takim przypadku możesz uzyskać minimalne logowanie , co oznacza, że dziennik transakcji nie zostanie wysadzony. Możesz także uzyskać minimalne rejestrowanie w dowolnej bazie danych z modelem odzyskiwania SIMPLE. Zobacz link, aby uzyskać więcej informacji na temat wymagań.
Jeśli nie jest to opcja, należy zmienić kod, aby móc skorzystać z indeksu klastrowego MySourceTable. Kluczową sprawą jest napisanie kodu, aby wykonać mniej więcej tyle samo pracy na pętlę. Możesz to zrobić, wykorzystując indeks zamiast za każdym razem skanować tabelę od początku. Napisałem post na blogu, który omawia różne metody zapętlania. Przykłady w tym poście zamiast wstawiania wstawiają do tabeli, ale powinieneś być w stanie dostosować kod.
W przykładowym kodzie poniżej zakładam, że klucz podstawowy i klucz klastrowany twojego MySourceTable. Napisałem ten kod dość szybko i nie jestem w stanie go przetestować:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DECLARE @BATCHSIZE INT,
@ITERATION INT,
@TOTALROWS INT,
@MSG VARCHAR(500)
@STARTID BIGINT,
@NEXTID BIGINT;
SET DEADLOCK_PRIORITY LOW;
SET @BATCHSIZE = 4500;
SET @ITERATION = 0;
SET @TOTALROWS = 0;
SELECT @STARTID = ID
FROM MySourceTable
ORDER BY ID
OFFSET 0 ROWS
FETCH FIRST 1 ROW ONLY;
SELECT @NEXTID = ID
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
OFFSET (60000) ROWS
FETCH FIRST 1 ROW ONLY;
BEGIN TRY
BEGIN TRANSACTION;
WHILE @STARTID IS NOT NULL
BEGIN
WITH MySourceTable_DELCTE AS (
SELECT TOP (60000) *
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
)
DELETE FROM MySourceTable_DELCTE
OUTPUT DELETED.*
INTO MyBackupTable
WHERE NOT EXISTS (
SELECT NULL AS Empty
FROM dbo.vendor AS v
WHERE VendorId = v.Id
);
SET @BATCHSIZE = @@ROWCOUNT;
SET @ITERATION = @ITERATION + 1;
SET @TOTALROWS = @TOTALROWS + @BATCHSIZE;
SET @MSG = CAST(GETDATE() AS VARCHAR) + ' Iteration: ' + CAST(@ITERATION AS VARCHAR) + ' Total deletes:' + CAST(@TOTALROWS AS VARCHAR) + ' Next Batch size:' + CAST(@BATCHSIZE AS VARCHAR);
PRINT @MSG;
COMMIT TRANSACTION;
CHECKPOINT;
SET @STARTID = @NEXTID;
SET @NEXTID = NULL;
SELECT @NEXTID = ID
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
OFFSET (60000) ROWS
FETCH FIRST 1 ROW ONLY;
END;
END TRY
BEGIN CATCH
IF @@ERROR <> 0
AND @@TRANCOUNT > 0
BEGIN
PRINT 'There is an error occured. The database update failed.';
ROLLBACK TRANSACTION;
END;
END CATCH;
GO
Kluczowa część jest tutaj:
WITH MySourceTable_DELCTE AS (
SELECT TOP (60000) *
FROM MySourceTable
WHERE ID >= @STARTID
ORDER BY ID
)
Każda pętla odczyta tylko 60000 wierszy MySourceTable. Powinno to spowodować średni rozmiar usuwania wynoszący 4500 wierszy na transakcję i maksymalny rozmiar usuwania wynoszący 60000 wierszy na transakcję. Jeśli chcesz być bardziej konserwatywny z mniejszym rozmiarem partii, to też jest w porządku. Te @STARTIDzmienne przesunie się po każdej pętli, dzięki czemu można uniknąć czytając ten sam wiersz więcej niż raz z tabeli źródłowej.
