Jaki jest najlepszy sposób na zarchiwizowanie wszystkich oprócz bieżącego roku i jednoczesne podzielenie tabeli na partycje

Zadanie

Archiwizuj wszystkie oprócz 13-miesięcznego okresu z grupy dużych tabel. Zarchiwizowane dane muszą być przechowywane w innej bazie danych.

• Baza danych znajduje się w prostym trybie odzyskiwania
• Tabele mają od 50 milionów wierszy do kilku miliardów, a w niektórych przypadkach zajmują setki GB.
• Tabele nie są obecnie podzielone na partycje
• Każda tabela ma jeden indeks klastrowy w stale rosnącej kolumnie daty
• Każda tabela ma dodatkowo jeden indeks nieklastrowany
• Wszystkie zmiany danych w tabelach są wstawkami
• Celem jest zminimalizowanie przestojów podstawowej bazy danych.
• Serwer to 2008 R2 Enterprise

Tabela „archiwum” będzie miała około 1,1 miliarda wierszy, a tabela „na żywo” około 400 milionów. Oczywiście tabela archiwów wzrośnie z czasem, ale spodziewam się, że tabela na żywo wzrośnie również dość szybko. Powiedz 50% w ciągu najbliższych kilku lat.

Myślałem o bazach danych Azure stretch, ale niestety jesteśmy w 2008 R2 i prawdopodobnie pozostaniemy tam przez jakiś czas.

Obecny plan

• Utwórz nową bazę danych
• Utwórz nowe tabele podzielone na partycje według miesięcy (używając zmodyfikowanej daty) w nowej bazie danych.
• Przenieś ostatnie 12-13 miesięcy danych do tabel podzielonych na partycje.
• Wykonaj zamianę nazw dwóch baz danych
• Usuń przeniesione dane z obecnie „archiwizowanej” bazy danych.
• Podziel każdą tabelę na partycje w bazie danych „archiwum”.
• Użyj swapów partycji do archiwizacji danych w przyszłości.
  • Zdaję sobie sprawę, że będę musiał zamienić dane do zarchiwizowania, skopiować tę tabelę do bazy danych archiwum, a następnie zamienić ją do tabeli archiwum. To jest do przyjęcia.

Problem: Próbuję przenieść dane do początkowych partycjonowanych tabel (w rzeczywistości wciąż robię na tym dowód koncepcji). Próbuję użyć TF 610 (zgodnie z Przewodnikiem wydajności ładowania danych ) i INSERT...SELECTinstrukcji do przeniesienia danych, początkowo sądząc, że zostaną one minimalnie zarejestrowane. Niestety za każdym razem, gdy próbuję, jest w pełni zalogowany.

W tym momencie myślę, że moim najlepszym rozwiązaniem może być przeniesienie danych za pomocą pakietu SSIS. Staram się tego unikać, ponieważ pracuję z 200 tabelami i wszystko, co mogę zrobić za pomocą skryptu, mogę łatwo wygenerować i uruchomić.

Czy brakuje mi czegoś w moim ogólnym planie i czy SSIS jest moim najlepszym rozwiązaniem do szybkiego przenoszenia danych przy minimalnym wykorzystaniu dziennika (dotyczy miejsca)?

Kod demonstracyjny bez danych

-- Existing structure
USE [Audit]
GO

CREATE TABLE [dbo].[AuditTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
); 
-- ~1.4 bill rows, ~20% in the last year

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(   [Modified] ASC   )
GO


-- New DB & Code
USE Audit_New
GO

CREATE PARTITION FUNCTION ThirteenMonthPartFunction (datetime)
AS RANGE RIGHT FOR VALUES ('20150701', '20150801', '20150901', '20151001', '20151101', '20151201', 
                            '20160101', '20160201', '20160301', '20160401', '20160501', '20160601', 
                            '20160701') 

CREATE PARTITION SCHEME ThirteenMonthPartScheme AS PARTITION ThirteenMonthPartFunction
ALL TO ( [PRIMARY] );

CREATE TABLE [dbo].[AuditTable](
    [Col1] [bigint] NULL,
    [Col2] [int] NULL,
    [Col3] [int] NULL,
    [Col4] [int] NULL,
    [Col5] [int] NULL,
    [Col6] [money] NULL,
    [Modified] [datetime] NULL,
    [ModifiedBy] [varchar](50) NULL,
    [ModifiedType] [char](1) NULL
) ON ThirteenMonthPartScheme (Modified)
GO

CREATE CLUSTERED INDEX [AuditTable_Modified] ON [dbo].[AuditTable]
(
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

CREATE NONCLUSTERED INDEX [AuditTable_Col1_Col2_Col3_Col4_Modified] ON [dbo].[AuditTable]
(
    [Col1] ASC,
    [Col2] ASC,
    [Col3] ASC,
    [Col4] ASC,
    [Modified] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ThirteenMonthPartScheme (Modified)
GO

Przenieś kod

USE Audit_New
GO
DBCC TRACEON(610);

INSERT INTO AuditTable
SELECT * FROM Audit.dbo.AuditTable
WHERE Modified >= '6/1/2015'
ORDER BY Modified

Dlaczego nie otrzymujesz minimalnego logowania?

Uważam, że Przewodnik wydajności ładowania danych , do którego się odwołujesz, jest niezwykle cennym zasobem. Jednak nie jest również w 100% kompleksowe i podejrzewam, że siatka jest już wystarczająco złożona, że ​​autor nie dodał kolumny, Table Partitioningaby rozróżnić różnice w zachowaniu w zależności od tego, czy tabela otrzymująca wstawki jest podzielona na partycje. Jak zobaczymy później, fakt, że tabela jest już podzielona na partycje, wydaje się hamować minimalne rejestrowanie.

Zalecane podejście

W oparciu o zalecenia zawarte w Przewodniku wydajności ładowania danych (w tym sekcja „Ładowanie zbiorcze tabeli podzielonej na partycje”) oraz duże doświadczenie w ładowaniu podzielonych tabel z dziesiątkami miliardów wierszy, oto podejście, które zaleciłbym:

• Utwórz nową bazę danych.
• Utwórz nowe tabele podzielone na partycje według miesięcy w nowej bazie danych.
• Przenieś najnowszy rok danych w następujący sposób:
  • Dla każdego miesiąca utwórz nową tabelę sterty;
  • Wstaw ten miesiąc danych do stosu, korzystając ze wskazówki TABLOCK;
  • Dodaj indeks klastrowy do sterty zawierającej ten miesiąc danych;
  • Dodaj ograniczenie sprawdzające wymuszające, że tabela zawiera tylko dane z tego miesiąca;
  • Przełącz tabelę na odpowiednią partycję nowej ogólnej tabeli podzielonej na partycje.
• Wykonaj zamianę nazw dwóch baz danych.
• Obetnij dane w obecnie „archiwizowanej” bazie danych.
• Podziel każdą tabelę na partycje w bazie danych „archiwum”.
• Użyj swapów partycji do archiwizacji danych w przyszłości.

Różnice w porównaniu do twojego oryginalnego podejścia:

• Metodologia przenoszenia ostatnich 12-13 miesięcy danych będzie znacznie bardziej wydajna, jeśli załadujesz do stosu po TABLOCKjednym miesiącu, używając przełączania partycji, aby umieścić dane w podzielonej na partycje tabeli.
• DELETESprzątać stary stół będzie w pełni rejestrowane. Być może możesz albo TRUNCATEupuścić tabelę i utworzyć nową tabelę archiwum.

Porównanie metod przenoszenia danych z ostatniego roku

Aby porównać podejścia w rozsądnym czasie na moim komputerze, użyłem 100MM rowzestawu danych testowych, które wygenerowałem i które są zgodne z twoim schematem.

Jak widać z poniższych wyników, istnieje znaczny wzrost wydajności i zmniejszenie liczby zapisów dziennika poprzez ładowanie danych do sterty za pomocą TABLOCKpodpowiedzi. Dodatkową zaletą jest to, że odbywa się to jedna partycja na raz. Warto również zauważyć, że metodę z jedną partycją na raz można łatwo zrównoleglać dalej, jeśli uruchomisz wiele partycji na raz. W zależności od sprzętu może to przynieść niezły wzrost; zwykle ładujemy co najmniej cztery partycje jednocześnie na sprzęt klasy serwerowej.

Oto pełny skrypt testowy .

Uwagi końcowe

Wszystkie te wyniki w pewnym stopniu zależą od sprzętu. Jednak moje testy zostały przeprowadzone na standardowym czterordzeniowym laptopie z obracającym się dyskiem. Jest prawdopodobne, że ładowanie danych powinno być znacznie szybsze, jeśli używasz porządnego serwera, który nie ma wiele innych obciążeń w momencie przeprowadzania tego procesu.

Na przykład uruchomiłem zalecane podejście na rzeczywistym serwerze deweloperskim (Dell R720) i zauważyłem redukcję do 76 seconds(z 156 secondsmojego laptopa). Co ciekawe, oryginalne podejście do wstawiania do partycjonowanej tabeli nie uległo takiej samej poprawie i nadal przejęło 12 minutesserwer dev. Prawdopodobnie dzieje się tak, ponieważ ten wzorzec daje szeregowy plan wykonania, a pojedynczy procesor na moim laptopie może pasować do jednego procesora na serwerze deweloperskim.

To może być dobry kandydat na Bimla. Jednym podejściem byłoby utworzenie szablonu wielokrotnego użytku, który migrowałby dane dla pojedynczej tabeli w małych zakresach dat za pomocą kontenera For Each. Biml będzie przechodził przez Twoją kolekcję tabel, tworząc identyczne pakiety dla każdej kwalifikującej się tabeli. Andy Leonard ma wstęp do swojej serii Stairway .

Może zamiast tworzyć nową bazę danych, przywróć prawdziwą bazę danych do nowej bazy danych i usuń najnowsze dane z 12–13 miesięcy. Następnie w swojej prawdziwej bazie danych usuń dane, które nie są zawarte w właśnie utworzonym obszarze archiwum. Jeśli duże usuwanie jest problemem, być może możesz po prostu usunąć zestawy 10K lub większe za pomocą skryptu, aby to zrobić.

Twoje zadania partycjonowania nie wydają się być zakłócane i wydają się mieć zastosowanie do każdej bazy danych po usunięciu.