Popraw szybkość odbudowywania indeksu na serwerze SQL

Importuję dużą ilość danych do pustej bazy danych i zanim zacznę, wyłączyłem wszystkie nieunikalne indeksy nieklastrowe, aby sprawdzić, czy mogę poprawić wydajność importu.

Teraz chcę ponownie włączyć indeksy i zastanawiam się, czy mogę coś zrobić, aby to zoptymalizować.

Do przebudowania jest ponad 100 tabel i prawie 2000 indeksów. Baza danych ma rozmiar 200 GB.

Kluczowa sekcja skryptu, który uruchamiam, jest następująca:

declare c_toggle_index cursor FORWARD_ONLY READ_ONLY for
    select  'alter index ' + QUOTENAME(i.name) + ' on ' + o.name + ' rebuild'
    from    sys.indexes as i
    Inner Join sys.objects o
    On o.object_id = i.object_id
    Where o.is_ms_shipped = 0
    And i.index_id >= 1
    and i.type > 1
    and i.is_disabled = 1

Rozważałem ustawienie ONLINE = OFF dla instrukcji alter index, ale ponieważ indeksy zaczynają się wyłączać, nie byłem pewien, czy to ustawienie będzie miało jakikolwiek wpływ. Zastanawiałem się również nad ustawieniem SORT_IN_TEMPDB = ON, ale ponieważ pliki tempdb znajdują się na tym samym dysku, co pliki .mdf baz danych, doszedłem do wniosku, że nie przyniosło to żadnej korzyści.

Podczas uruchamiania skryptu przebudowy zauważyłem, że mam wiele typów oczekiwania CXPACKET. Naprawdę nie rozumiem, dlaczego tak się dzieje lub czy jest to problem, który powinienem rozwiązać.

Jeden ostatni punkt, który może być istotny: cały mój serwer jest obecnie nieaktywny poza importem danych do bazy danych. Nie ma innej aktywności użytkownika do rozważenia lub zmartwienia; moim jedynym zmartwieniem jest import danych do bazy danych w jak najkrótszym czasie.

Osiągnięcie optymalnej wydajności importu w tym scenariuszu wymaga trzech rzeczy:

1. Minimalnie rejestrowane wstawki do stołu bazowego
2. Minimalnie rejestrowane kompilacje indeksów nieklastrowych
3. Unikanie fizycznych odczytów

Minimalne rejestrowanie

Osiągnięcie minimalnie zalogowanych wstawek do pustej tabeli klastrowanej bez indeksów nieklastrowanych wymaga:

1. Korzystanie z modeli odzyskiwania bazy danych SIMPLElubBULK_LOGGED
2. Określanie blokady tabeli i uporządkowanych danych wejściowych (np. TABLOCKI ORDERpodpowiedzi)

Dygresja:

Możliwe jest również uzyskanie minimalnie zalogowanych wstawek do tabeli klastrowanej, która ma indeksy nieklastrowane, pod warunkiem, że flaga śledzenia 610 jest włączona. To, czy wstawiane indeksy nieklastrowe są minimalnie rejestrowane, zależy od planu zapytań wybranego przez optymalizator zapytań.

Jeśli plan kwerendy używa osobnego iteratora dla indeksu nieklastrowanego, a iterator ma DMLRequestSortustawioną właściwość true, wstawiane indeksy nieklastrowane będą minimalnie rejestrowane, pod warunkiem spełnienia pozostałych warunków wymienionych wcześniej.

Budowanie indeksów nieklastrowych osobno

Korzyści z tego są następujące:

1. Klastrowe płytki indeksowe można minimalnie rejestrować bez włączania TF 610
2. CREATE INDEX jest minimalnie rejestrowany, jeśli model odzyskiwania nie jest FULL

Unikanie fizycznych odczytów

Idealnie, dane do zaimportowania będą przechowywane na osobnej maszynie lub przynajmniej na osobnej pamięci fizycznej niż ta, która służy do hostowania bazy danych.

Serwer bazy danych powinien mieć wystarczającą ilość pamięci, aby pomieścić największą tabelę podstawową w pamięci podręcznej, z wystarczającą ilością miejsca na operacje sortowania niezbędne podczas budowania indeksów nieklastrowanych.

Dobrym wzorcem jest szybkie ładowanie tabeli podstawowej (minimalnie rejestrowane ładowanie indeksów klastrowych), a następnie budowanie wszystkich indeksów nieklastrowanych dla tej tabeli, podczas gdy jej strony danych są nadal buforowane.

Pytanie określa proces, w którym najpierw ładowane są tabele podstawowe, a następnie budowane indeksy nieklastrowane. Definicja kursora nie używa ORDER BYklauzuli co najmniej do grupowania nieklastrowanych indeksów w tej samej tabeli razem.

Prawdopodobnym rezultatem jest to, że strony danych dla różnych tabel są wielokrotnie wczytywane do pamięci podręcznej, a następnie odrzucane, ponieważ indeksy nieklastrowane są budowane w niedeterministycznej kolejności.

Koszt powtarzanych odczytów fizycznych całkowicie dominuje w korzyściach z minimalnego rejestrowania uzyskanych dzięki osobnemu tworzeniu indeksów nieklastrowanych. To wyjaśnia, dlaczego odkryłeś, że ładowanie tabel z istniejącymi indeksami jest szybsze (ponieważ wszystkie indeksy nieklastrowane dla danej tabeli są zachowywane przed przejściem do następnej tabeli).

Podsumowanie

Proces importowania należy przerobić, aby ładować zbiorczo jedną tabelę na raz. Oznacza to załadowanie tabeli i zbudowanie wszystkich indeksów nieklastrowanych przed przejściem do następnego. Wystąpienie programu SQL Server powinno mieć wystarczającą ilość pamięci, aby pomieścić największą tabelę i jednocześnie wykonać największe sortowanie indeksów nieklastrowanych.

Państwo mogli również spróbować włączyć TF 610 przed załadowaniem danych do tabel z indeksów nieklastrowany już na miejscu. Zwykle nie jest to tak szybkie jak poprzednia metoda, ale może być wystarczająco szybkie.

Aby uzyskać więcej informacji, zobacz następujące informacje:

Przewodnik po wydajności ładowania danych

Operacje, które można minimalnie rejestrować