Nie wiedziałem o tym pytaniu, kiedy odpowiedziałem na powiązane pytanie ( Czy w tej pętli while potrzebne są jawne transakcje? ), Ale ze względu na kompletność zajmę się tym problemem tutaj, ponieważ nie było to częścią mojej sugestii w tej powiązanej odpowiedzi .
Ponieważ sugeruję zaplanować to za pomocą zadania agenta SQL (w końcu jest to 100 milionów wierszy), nie sądzę, aby jakakolwiek forma wysyłania komunikatów o stanie do klienta (tj. SSMS) była idealna (choć jeśli tak jest) kiedykolwiek potrzebuję innych projektów, zgadzam się z Vladimirem, że używanie RAISERROR('', 10, 1) WITH NOWAIT;jest właściwą drogą).
W tym konkretnym przypadku utworzyłbym tabelę statusu, która może być aktualizowana dla każdej pętli z liczbą zaktualizowanych do tej pory wierszy. I nie zaszkodzi rzucać w obecnym czasie, aby przyspieszyć proces.
Biorąc pod uwagę, że chcesz móc anulować i ponownie uruchomić proces, Jestem zmęczony pakowaniem UPDATE tabeli głównej UPDATE tabeli statusu w jawnej transakcji. Jeśli jednak wydaje Ci się, że tabela statusu nie jest zsynchronizowana z powodu anulowania, łatwo jest odświeżyć bieżącą wartością, po prostu aktualizując ją ręcznie za pomocą COUNT(*) FROM [huge-table] WHERE deleted IS NOT NULL AND deletedDate IS NOT NULL.i do zaktualizowania są dwie tabele (tj. główna tabela i tabela stanu), powinniśmy użyć jawnej transakcji, aby utrzymać synchronizację tych dwóch tabel, ale nie chcemy ryzykować osieroconej transakcji, jeśli anulujesz proces w punkt po rozpoczęciu transakcji, ale jej nie zatwierdził. Powinno to być bezpieczne, o ile nie zatrzymasz zadania agenta SQL.
Jak możesz zatrzymać proces, nie przerywając go? Prosząc o zatrzymanie :-). Tak. Wysyłając procesowi „sygnał” (podobnie jak kill -3w Uniksie), możesz poprosić o zatrzymanie go w najbliższym dogodnym momencie (tj. Gdy nie ma aktywnej transakcji!) I sprawić, by wszystko zostało uporządkowane.
Jak możesz komunikować się z uruchomionym procesem w innej sesji? Korzystając z tego samego mechanizmu, który stworzyliśmy, aby przekazać ci swój aktualny status: tabelę statusu. Musimy tylko dodać kolumnę, którą proces sprawdzi na początku każdej pętli, aby wiedział, czy kontynuować, czy przerwać. A ponieważ celem jest zaplanowanie tego jako zadania agenta SQL (uruchamianego co 10 lub 20 minut), powinniśmy również sprawdzić na samym początku, ponieważ nie ma sensu wypełniać tabeli tymczasowej 1 milionem wierszy, jeśli proces właśnie trwa wyjść chwilę później i nie używać żadnych z tych danych.
DECLARE @BatchRows INT = 1000000,
@UpdateRows INT = 4995;
IF (OBJECT_ID(N'dbo.HugeTable_TempStatus') IS NULL)
BEGIN
CREATE TABLE dbo.HugeTable_TempStatus
(
RowsUpdated INT NOT NULL, -- updated by the process
LastUpdatedOn DATETIME NOT NULL, -- updated by the process
PauseProcess BIT NOT NULL -- read by the process
);
INSERT INTO dbo.HugeTable_TempStatus (RowsUpdated, LastUpdatedOn, PauseProcess)
VALUES (0, GETDATE(), 0);
END;
-- First check to see if we should run. If no, don't waste time filling temp table
IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
BEGIN
PRINT 'Process is paused. No need to start.';
RETURN;
END;
CREATE TABLE #FullSet (KeyField1 DataType1, KeyField2 DataType2);
CREATE TABLE #CurrentSet (KeyField1 DataType1, KeyField2 DataType2);
INSERT INTO #FullSet (KeyField1, KeyField2)
SELECT TOP (@BatchRows) ht.KeyField1, ht.KeyField2
FROM dbo.HugeTable ht
WHERE ht.deleted IS NULL
OR ht.deletedDate IS NULL
WHILE (1 = 1)
BEGIN
-- Check if process is paused. If yes, just exit cleanly.
IF (EXISTS(SELECT * FROM dbo.HugeTable_TempStatus WHERE PauseProcess = 1))
BEGIN
PRINT 'Process is paused. Exiting.';
BREAK;
END;
-- grab a set of rows to update
DELETE TOP (@UpdateRows)
FROM #FullSet
OUTPUT Deleted.KeyField1, Deleted.KeyField2
INTO #CurrentSet (KeyField1, KeyField2);
IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR(N'All rows have been updated!!', 16, 1);
BREAK;
END;
BEGIN TRY
BEGIN TRAN;
-- do the update of the main table
UPDATE ht
SET ht.deleted = 0,
ht.deletedDate = '2000-01-01'
FROM dbo.HugeTable ht
INNER JOIN #CurrentSet cs
ON cs.KeyField1 = ht.KeyField1
AND cs.KeyField2 = ht.KeyField2;
-- update the current status
UPDATE ts
SET ts.RowsUpdated += @@ROWCOUNT,
ts.LastUpdatedOn = GETDATE()
FROM dbo.HugeTable_TempStatus ts;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF (@@TRANCOUNT > 0)
BEGIN
ROLLBACK TRAN;
END;
THROW; -- raise the error and terminate the process
END CATCH;
-- clear out rows to update for next iteration
TRUNCATE TABLE #CurrentSet;
WAITFOR DELAY '00:00:01'; -- 1 second delay for some breathing room
END;
-- clean up temp tables when testing
-- DROP TABLE #FullSet;
-- DROP TABLE #CurrentSet;
Możesz następnie sprawdzić status w dowolnym momencie za pomocą następującego zapytania:
SELECT sp.[rows] AS [TotalRowsInTable],
ts.RowsUpdated,
(sp.[rows] - ts.RowsUpdated) AS [RowsRemaining],
ts.LastUpdatedOn
FROM sys.partitions sp
CROSS JOIN dbo.HugeTable_TempStatus ts
WHERE sp.[object_id] = OBJECT_ID(N'ResizeTest')
AND sp.[index_id] < 2;
Chcesz wstrzymać proces, niezależnie od tego, czy jest on uruchamiany w zadaniu agenta SQL, czy nawet w SSMS na czyimś komputerze? Po prostu biegnij:
UPDATE ht
SET ht.PauseProcess = 1
FROM dbo.HugeTable_TempStatus ts;
Chcesz, aby proces mógł zacząć się od nowa? Po prostu biegnij:
UPDATE ht
SET ht.PauseProcess = 0
FROM dbo.HugeTable_TempStatus ts;
AKTUALIZACJA:
Oto kilka dodatkowych rzeczy do wypróbowania, które mogą poprawić wydajność tej operacji. Nikt nie gwarantuje pomocy, ale prawdopodobnie warto je przetestować. A przy 100 milionach wierszy do aktualizacji masz mnóstwo czasu / okazji na przetestowanie niektórych wariantów ;-).
- Dodaj
TOP (@UpdateRows)do zapytania UPDATE, aby górny wiersz wyglądał tak:
UPDATE TOP (@UpdateRows) ht
Czasami optymalizator wie, ile to może mieć wpływu na wiersze, więc nie marnuje czasu na szukanie więcej.
Dodaj KLUCZ PODSTAWOWY do #CurrentSettabeli tymczasowej. Chodzi o to, aby pomóc optymalizatorowi z JOIN do tabeli 100 milionów wierszy.
I żeby to powiedzieć, aby nie było dwuznaczne, nie powinno być żadnego powodu, aby dodawać PK do #FullSettabeli tymczasowej, ponieważ jest to zwykła tabela kolejek, w której kolejność jest nieistotna.
- W niektórych przypadkach pomocne jest dodanie indeksu filtrowanego, aby ułatwić
SELECTpobieranie danych do #FullSettabeli tymczasowej. Oto kilka uwag związanych z dodawaniem takiego indeksu:
- Zatem warunek WHERE powinien pasować do warunku WHERE zapytania
WHERE deleted is null or deletedDate is null
- Na początku procesu większość wierszy będzie pasować do warunku GDZIE, więc indeks nie jest zbyt pomocny. Przed dodaniem tego możesz poczekać do około 50%. Oczywiście, ile to pomaga i kiedy najlepiej dodać indeks, różnią się z powodu kilku czynników, więc jest to trochę prób i błędów.
- Może być konieczne ręczne UAKTUALNIANIE STATYSTÓW i / lub ODBUDOWANIE indeksu, aby był aktualny, ponieważ dane podstawowe zmieniają się dość często
- Pamiętaj, że indeks, pomagając
SELECT, zaszkodzi, UPDATEponieważ jest to kolejny obiekt, który należy zaktualizować podczas tej operacji, a więc więcej operacji we / wy. Odgrywa to zarówno zastosowanie indeksu filtrowanego (który zmniejsza się, gdy aktualizujesz wiersze, ponieważ mniej wierszy pasuje do filtra), jak i czekanie przez chwilę, aby dodać indeks (jeśli nie będzie to bardzo pomocne na początku, to nie ma powodu, aby go ponosić dodatkowe wejścia / wyjścia).