Wydajne przesyłanie dużej ilości (84 milionów wierszy) danych

Mam około 84 milionów wierszy. Wszystkie z nich należy przenieść do osobnej bazy danych na tym samym serwerze, a następnie usuwam, aby usunąć około 60 milionów wierszy ze źródłowej bazy danych.

84 miliony wierszy znajduje się w tej samej tabeli. Sama ta tabela stanowi 90% całej bazy danych.

Więc ... Źródło: 84 miliony wierszy -> 24 miliony wierszy Miejsce docelowe: 0 rzędów -> 84 miliony wierszy

Źródło działa w trybie pełnego odzyskiwania, miejsce docelowe będzie działać prosto.

Zastanawiam się, jaki byłby najbardziej efektywny sposób to zrobić?

Plan A:

1) INSERT INTO destination SELECT * FROM source

2) Źródło TRUNCATE

3) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan B:

1) Przywróć kopię zapasową źródłowej bazy danych jako docelowej bazy danych

2) Usuń wszystkie tabele oprócz potrzebnych w docelowej bazie danych

3) Źródło TRUNCATE

4) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan C:

1) INSERT INTO destination SELECT * FROM source

2) USUŃ źródło GDZIE keep_condition = 0

albo coś innego?

Dzięki

Dodam, że niezależnie od tego, jak zdecydujesz się do tego podejść, musisz pogrupować te transakcje . Ostatnio bardzo mi się powiodło z linkowanym artykułem i doceniam sposób, w jaki korzysta on z indeksów w przeciwieństwie do większości pakietowanych rozwiązań, jakie widzę.

Nawet minimalnie zarejestrowane, są to duże transakcje , i możesz spędzić dużo czasu na radzeniu sobie z konsekwencjami nieprawidłowego wzrostu logów (VLF, obcinanie, dopasowywanie do wielkości itp.).

Dzięki

„Wydajny” może dotyczyć użycia pliku dziennika, wydajności We / Wy, czasu procesora lub czasu wykonania.

Próbowałbym osiągnąć minimalnie zalogowaną operację, która byłaby dość wydajna z punktu widzenia rejestrowania. To powinno zaoszczędzić ci trochę czasu realizacji jako bonusu. Jeśli masz miejsce w tempdb, poniższe opcje mogą działać dla Ciebie.

CREATE TABLE #temp;
ALTER source -> BULK_LOGGED recovery model

BEGIN TRANSACTION;

    INSERT INTO dest SELECT FROM source;
    INSERT INTO #temp SELECT FROM source WHERE keep_condition=1;
    TRUNCATE TABLE source;
    INSERT INTO source SELECT FROM #temp;

COMMIT TRANSACTION;

ALTER source -> FULL recovery model
DROP TABLE #temp;

Aby minimalnie zarejestrowana operacja mogła się zdarzyć, musi być spełnionych kilka warunków, w tym brak uruchomionych kopii zapasowych, baza danych ustawiona na BULK_LOGGEDtryb odzyskiwania oraz, w zależności od indeksów, tabela docelowa może być pusta. Niektóre z tych zachowań również uległy zmianie (poprawie) z SQL Server 2005 do 2008.

Z drugiej strony, nie znając specyfiki tabeli i danych, każda inna opcja może lepiej działać. Spróbuj użyć

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

.. i zobacz, który działa najlepiej.

EDYCJA : Podczas wykonywania operacji rejestrowania zbiorczego upewnij się, że wykonujesz kopię zapasową (dziennik pełny lub transakcyjny) przed i po operacji, jeśli potrzebujesz możliwości przywracania w określonym momencie i podejrzewasz, że w bazie danych może się dziać inna aktywność w tym samym czasie, gdy uruchomione jest zadanie ETL.

Jakiś czas temu napisałem post na blogu o minimalnie zalogowanych operacjach, są tam linki do innych postów i dokumentacji.

Dlaczego nie BCP?

1. Utwórz kopię zapasową źródła
2. Zmień sourcedb na zbiorczo

3. Otwórz wiersz polecenia

4. bcp server.sourcedb.table out Filename.flt -T -c

5. bcp "SELECT * FROM sourcedb.table WHERE keep_condition = 1" queryout Filename2.flt -T -c

6. bcp Server.destinationdb.table in Filename.flt -T -c -b1000

7. sprawdź dane

8. Z SSMS Obetnij tabelę sourcedb
9. bcp server.sourcedb.table in Filename2.flt -T -c -b1000
10. Zmień sourcedb z powrotem na pełny

Nie sądzę, że powinieneś zalecać zmianę modelu odzyskiwania bez pełnej kopii zapasowej bazy danych lub kopii zapasowej t-log przed i po . Jedną z cech modelu odzyskiwania BULK_LOGGED jest to, że utracisz możliwość wykonywania odzyskiwania w określonym momencie dla t-logów zawierających operacje z logowaniem zbiorczym. Klasyczny scenariusz: całonocna pełna kopia zapasowa, cogodzinne kopie zapasowe dziennika T. Zmieniasz model odzyskiwania na masowo zalogowany i zaczynasz operację. Coś poszło nie tak i transakcja wycofuje się (lub nie korzystałeś z niej). Jednak nie jesteś pewien, co jeszcze działo się w bazie danych, więc chcesz przywrócić do znanego dobrego punktu.

Kiedy możesz przywrócić? Ostatnia cogodzinna kopia zapasowa t-log, która nie zawiera operacji rejestrowania zbiorczego, potencjalnie traci n minut transakcji. Pełna kopia zapasowa lub kopia zapasowa dziennika T przed zmianą modelu odzyskiwania utworzy punkt awaryjny. Który wybierzesz zależy od twojego RTO.

Usuwanie partycji ze stołu to naprawdę szybki i efektywny pod względem zasobów sposób usuwania dużych porcji danych ze stołu. Gdyby ta tabela została podzielona na partycje w sposób, który obsługuje podział źródłowy / docelowy, odpowiedzią byłoby przywrócenie kopii, usunięcie zbędnych tabel i nadmiarowych partycji z miejsca docelowego i usunięcie komplementarnych partycji ze źródła.

Koszt włączenia partycjonowania może jednak ogólnie spowodować, że będzie to droższa operacja.