Kasowanie wierszy przez DELETE a kursorem

Cześć!
Wszystkie zapytania pisałem z palca, gdyż zależy mi na ideowym opisaniu problemu, a nie na pokazywaniu całości kodu.
Mamy następującą sytuację: tabelka Customers i tabelka Positions. Chcemy skasować dane następującym zapytaniem

DELETE FROM Customers
WHERE Id IN (
SELECT C.Id
FROM Customers AS C
JOIN Positions AS P ON P.CustomerId = P.Id
WHERE
P.CreationDate >= 'jakas-data'
AND P.CreationDate <= 'jakas-data'
AND P.Type = 'jakis-typ'
AND P.Rodzaj NOT LIKE '%COS%'
)

Czyli chcemy skasować klientów, którzy mają pozycję odpowiedniego typu w jakimś tam okresie. Customers.Id to klucz główny (jest na nim indeks klastrowy), na tabelce Positions nie ma żadnych użytecznych dla tego zapytania indeksów.

W Customers jest 800 milionów rekordów, do skasowania jest około 75 milionów, czyli 10% tabeli. Odpalenie powyższego zapytania na raz nie wchodzi w grę, gdyż nie chcemy wyłączać aplikacji pracujących na tej bazie, więc aby uniknąć eskalacji blokad na poziom tabeli kasujemy dane w partiach po 4500 wierszy. Coś na kształt:

SET DEADLOCK_PRIORITY = -10
DECLARE @i INT;
SET @i = 1;
WHILE @i > 0
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
    BEGIN TRAN
    DELETE TOP(4500) FROM Customers
    WHERE Id IN (
        SELECT TOP(4500) C.Id
        FROM Customers AS C
        JOIN Positions AS P ON P.CustomerId = P.Id
        WHERE
        P.CreationDate >= 'jakas-data'
        AND P.CreationDate <= 'jakas-data'
        AND P.Type = 'jakis-typ'
        AND P.Rodzaj NOT LIKE '%COS%'
    )
    SET @i = @@ROWCOUNT
    COMMIT
    PRINT GetDate()
END

Czyli obniżamy priorytet na potrzeby deadlocków (abyśmy to my byli zabici w razie potrzeby), obniżamy poziom transakcji i kasujemy 4500 wierszy (eskalacja następuje po 5000 wierszy). W kodzie są jeszcze bloki try i catch obsługujące deadlock i błędy, ale nie są one przesadnie istotne.

Zapytanie wykonuje się poprawnie, kasuje dobre dane, niestety każda iteracja wykonuje się coraz wolniej: pierwsza iteracja trwała około 5 sekund, po paru godzinach iteracja trwała już 50 sekund. Intuicyjnie rozumuję to tak, że serwer za każdym razem wykonuje zapytanie SELECT od początku tabeli, więc wielokrotnie przeszukuje masę wierszy, których nie usuwa, a że za każdym razem wiersze do usunięcia są "dalej", to i iteracja trwa dłużej. Brzmi sensownie?

Bazując na tej intuicji zmieniamy zapytanie na używające kursora. Na początku konfigurujemy serwer tak, aby nie zamykał kursora po commicie, potem kasujemy 4500 wierszy w pętli. Coś takiego:

Priorytety, zmienne itp
DECLARE kursor CURSOR DYNAMIC FOR
SELECT C.Id
    FROM Customers AS C
    JOIN Positions AS P ON P.CustomerId = P.Id
    WHERE
    P.CreationDate >= 'jakas-data'
    AND P.CreationDate <= 'jakas-data'
    AND P.Type = 'jakis-typ'
    AND P.Rodzaj NOT LIKE '%COS%'
)
WHILE sa jakies dane do usuniecia
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
    BEGIN TRAN
    WHILE jest cos w kursorze i nie przekroczylismy 4500 wierszy
    BEGIN
        FETCH NEXT FROM kursor INTO @idToRemove
        DELETE FROM Customers WHERE CURRENT OF kursor
    END
    COMMIT
    ...
END

I teraz tak: ponieważ serwer nie zamyka kursora po commicie, teoretycznie przy każdym obrocie powinniśmy przeszukiwać wiersze "dalej" (a nie "od początku"), więc czas wykonywania iteracji powinien być mniej więcej stały (przy założeniu, że dane do usunięcia są rozlokowane mniej więcej w równych odstępach). Jednocześnie commit zwalnia blokady założone przez kursor, więc eskalacja nam nie grozi. Jednakże zapytanie z kursorem zwalnia o wiele bardziej: o ile pierwsza iteracja wykonała się w jakieś 5 sekund, to już po kilku minutach iteracja wykonuje się około 60 sekund i zwalnia dalej.

Teraz pytanie: czy to zapytanie z kursorem ma szansę zadziałać zgodnie z opisaną przeze mnie intuicją, czy jest kompletnie do bani?
Próbowałem weryfikować te przypuszczenia, więc na boku odpaliłem to zapytanie z poziomem SERIALIZABLE i w iteracji co 5 wierszy, i przy każdym wierszu wypisywałem exec sp_lock. Blokady są zwalniane po commicie, a locki w każdym batchu są zakładane na inne wiersze (widzę inne zestawy guidów), więc teoretycznie działa zgodnie z koncepcją, a jednak bardzo zwalnia. Czy ktoś jest w stanie wyjaśnić, czy to intuicja jest zła i gdzieś popełniłem błąd, czy może koncepcyjnie wszystko jest okej i zwolnienie jest spowodowane czymś innym?

Baza o MS SQL 2005 Standard, wszystko działa na Windows Server 2003.

Dzięki za zainteresowanie
Afish

jeśli chodzi o samą szybkość usunięcia tych rekordów to może się okazać, że znacznie szybciej będzie wszystkie id do usunięcia wrzucić do nowej tabeli z jednym polem:

SELECT  C.Id INTO do_usuniecia
        FROM Customers AS C
        JOIN Positions AS P ON P.CustomerId = P.Id
        WHERE
        P.CreationDate >= 'jakas-data'
        AND P.CreationDate <= 'jakas-data'
        AND P.TYPE = 'jakis-typ'
        AND P.Rodzaj NOT LIKE '%COS%'

potem założyć indeks na polu id w tabeli do_usuniecia i na końcu usuwać w ten sposób

SET DEADLOCK_PRIORITY = -10
DECLARE @i INT;
SET @i = 1;
WHILE @i > 0
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
    BEGIN TRAN
    DELETE FROM Customers
    WHERE Id IN (
        SELECT TOP(4500) Id
        FROM do_usuniecia 
        order by id
    )
    DELETE TOP(4500) FROM do_usuniecia ORDER BY id
    SET @i = @@ROWCOUNT
    COMMIT
    PRINT GetDate()
END

tworzenie tabeli powinno być relatywnie szybkie (w końcu nie masz indeksów i zapytanie i tak musi przelecieć całą tabelę ale tutaj tylko raz), a potem usuwanie już po indeksie.

BTW nie zna MSSQLa więc może nie odpalić od ręki

Tak, ten scenariusz również rozważałem i też podejrzewam, że będzie to szybsze (nie mogę jednak sprawdzić od ręki, polityka i te sprawy), jednak nurtuje mnie kwestia kursora i to o niego chodzi mi w tym temacie.

a gdzie masz w kursorze porcjowanie po 4500 wierszy? Bo tego zabrakło jak dla mnie aby coś powiedzieć

Pętla w pętli mi wypadła, miało być coś takiego:

WHILE sa jakies dane do usuniecia
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
    BEGIN TRAN
    WHILE jest cos w kursorze i nie przekroczylismy 4500 wierszy
    BEGIN
        FETCH NEXT FROM kursor INTO @idToRemove
        DELETE FROM Customers WHERE CURRENT OF kursor
    END
    COMMIT
    ...
END

Zewnętrzna pętla kręci się tak długo, jak długo będziemy mieli dane do usunięcia, a wewnętrzna ma dodatkowy warunek, że zakręci się maksymalnie 4500 razy (czyli jeden batch).

Usuwanie zwiększa fragmentację tabel i indeksów. Warto co pewien czas puścić defragmentację obu, być może połączoną ze shrink'iem. Zastanów się też nad resetowaniem statystyk.
Ponadto przy masowym usuwaniu masowo puchną logi transakcyjne. Trzeba je co pewien czas czyścić, ale żeby to zrobić, to trzeba zrobić backup, a to trwa. Możesz rozważyć zrobienie porządków w nocy, wyłączyć serwis, odpiąć bazę (przełączyć w single user mode) i wyłączyć logi transakcyjne (ALTER DATABASE MyDB SET RECOVERY SIMPLE). Oczywiście potem musisz je włączyć.

Zdaję sobie sprawę, niestety baza ma być cały czas dostępna, więc nie wszystko jestem w stanie zrobić jak bym chciał.
Zastanawiałem się też, czy to nie fragmentacja lub logi spowodowały taki spadek wydajności i wydaje mi się, że to nie to. Na początku batche w zapytaniu bez kursora wykonywały się w kilka sekund, potem stopniowo zwalniały, ale poziom około 50 sekund osiągnęły dopiero po kilku godzinach kasowania. Jednocześnie zapytanie z kursorem zaczęło zwalniać o wiele bardziej: o ile pierwszy batch wykonał się w kilka sekund, o tyle każdy następny zwalniał zauważalnie i już po minucie batch wykonywał się kilkadziesiąt sekund (a potem zwalniał jeszcze bardziej). Oczekiwałbym, że w podejściu z kursorem każdy batch wykonuje się wolniej od analogicznego batcha bez kursora (kursory zazwyczaj są wolniejsze), ale czas ten nie ulega znaczącej zmianie, a w rzeczywistości spadek wydajności był bardzo duży i bardzo szybki.

Możesz jeszcze skopiować tabelę, zrobić na niej wszystkie operacje usuwania, potem w blokującej transakcji uzupełnić brakujące dane (dodane do oryginalnej tabeli w trakcie usuwania z kopii) i podmienić tabele. Tylko AFAIK zmiana nazwy tabeli będzie wymagać zamknięcia połączeń, co jednak nie powinno zająć dłużej, niż kilkanaście sekund.

[edit]
Usuwanie to zwykłe zapytanie. Jako takie ma swój plan zapytania. Włącz go (np. z poziomu SSMS - guziczek Include Actual Execution Plan) i zobacz na planie, który klocek zajmuje najwięcej czasu, sprwdź też czy aby ilość danych przesyłanych pomiędzy klockami nie zaczyna sięgać jakichś absurdalnie dużych wartości. Potem przeanalizuj dlaczego tak się dzieje i podejmij odpowiednie kroki, ewentualnie wrzuć xml i screenshot z planem tutaj na forum.

Zastanawiałem się też, czy to nie fragmentacja lub logi spowodowały taki spadek wydajności i wydaje mi się, że to nie to
Na podstawie czego tak Ci się wydaje?

Gdyby to była fragmentacja, to zapytanie bez kursora też powinno zwolnić praktycznie od razu, a ono zwalniało bardzo powoli. Zapytania wykonałem na tym samym zestawie danych, na osobnych maszynach, starałem się mieć lustrzane środowiska.
Co do planów zapytań, to analizowałem tylko plan przed pierwszą iteracją i nic złowieszczego tam nie było. Po weekendzie sprawdzę, jak zmienia się plan zapytania przy każdym obrocie, dzięki za ten pomysł.