SQLServerResumableOnlineIndexRebuild_00

SQL Server – Resumable Online Index Rebuild & Creation

Utrzymanie bazy danych to ciągły proces który wymaga odpowiedniego zaplanowania. Zazwyczaj wygląda to tak, że administratorzy baz danych czy też ktokolwiek kto zajmuje się bazą ma odpowiednie okno czasowe gdzie jest możliwe wykonanie określonych operacji. Jedną z takich operacji jest przebudowa indeksów, która defragemtnuje indeksy. Operacja ta była o tyle problematyczna, że często zajmowała dużo czasu, a jej anulowanie powodowało, że następnym razem rozpocząć od początku.

Dlaczego napisałem powyższy akapit w czasie przeszłym? Ponieważ rozwiązanie na tego typu problemu pojawiło się już w SQL Server 2017 (oraz w analogicznym okresie w Azure SQL Database). Chodzi mianowicie o to, że wtedy pojawiła się możliwość pauzowania i wznawiania całej operacji czyli Resumable Online Index Rebuild. SQL Server 2019 (oraz oczywiście baza Azure SQL Database) rozszerzyły nieco tę funkcjonalność ale o tym za chwilę. Cały ten mechanizm chciałbym w ramach niniejszego artykułu przetestować i przedstawić, zapraszam do lektury.

Bez zbędnego wstępu przejdźmy do testów. Będzie się on opierał standardowo na FactInternetSales z AdventureworksDW2017. Pierwszy krok jaki wykonamy to zwielokrotnienie tabeli w taki sposób abyśmy mieli nieco większy zestaw danych:

Po wykonaniu powyższego skryptu powstała tabela dbo.IndexResume powinna zawierać 501 x ilość wierszy z dbo.FactInternetSales czyli około 30 milionów wierszy. Liczba ta dla naszych potrzeb jest zdecydowanie wystarczająca.

Na ten moment nasza tabela jest stertą, zmienimy to tworząc standardowy indeks klastrowany:

Proszę żebyście zwrócili uwagę, że powyższy skrypt jest standardowy tzn. że nie potrzeba absolutnie żadnych zmian w składni jeśli chodzi o zastosowanie omawianego mechanizmu. Możemy przejść do meritum i wywołać ALTER INDEX REBUILD:

Kluczowe w powyższej składni jest to, że użyłem przełącznika RESUMABLE ustawionego na ON, który włącza cały mechanizm. Aby wszystko działało musimy również wyspecyfikować ONLINE=ON, reszta jest wywoływana w zależności od potrzeb. Wskazówka MAXDOP=1 spowoduje, że całość będzie wykonywana na jednym wątku, a zrobiłem to tylko i wyłącznie po to aby cała operacja trwała odpowiednio długo.

W momencie jak powyższe zapytanie zostało uruchomione to możemy przejść do nowej sesji w nowym oknie i podejrzeć co się dzieje w jednym z nowych widoków systemowych sys.index_resumable_operations:

Zwróćcie uwagę na kolumnę percent_complete, która pokazuje ile procent całej przebudowy zostało wykonane. Oprócz tego widok ten zwraca następujące informacje:

  • object_id – identyfikator obiektu do którego należy indeks,
  • index_id – identyfikator indeksu,
  • name – nazwa indeksu,
  • sql_text – zapytanie wywołane do przebudowania indeksu,
  • last_max_dop – ostatnie ustawienie maxdop dla zapytania,
  • partition_number – numer partycji (jeśli przebudowujemy tylko daną partycję),
  • state – status czyli 0 = działa, 1 pauza
  • state_desc – opis do statusu,
  • start_time – czas kiedy operacja się rozpoczęła,
  • last_pause_time – czas kiedy operacja została zatrzymana,
  • total_execution_time – całkowity czas wykonania,
  • page_count – ilość stron zaalokowanych przez operację index rebuild.

Aby zatrzymać operacje wystarczy z poziomu nowej sesji uruchomić następujące zapytanie:

Całe zapytanie zostanie zastopowane i można standardowo używać zarówno indeksu jak i całej tabeli. Sesja która wykonywała ALTER INDEX REBUILD zostanie rozłączona. We wspomnianym wyżej widoku zobaczymy nowy status:

Jedyne czego nie możemy zrobić to usunąć indeksu w momencie jak operacja na nim została zatrzymana. W przeciwnym wypadku otrzymamy błąd:

Wcale to nie oznacza, że raz rozpoczęta operacja powoduje, że musimy ją bezwzględnie zakończyć. Możemy całkowicie anulować operację przebudowania:

Gdy chcemy uruchomić naszą operację na nowo to mamy analogiczną klauzulę RESUME:

Przy wznawianiu operacji warto zauważyć, że  nie jesteśmy zobligowani do tego aby użyc dokładnie tej samej konfiguracji co poprzednio.

Dosyć ciekawe zachowanie pojawi się w momencie gdy anulujemy sesję która przebudowuje indeks. Anulujemy ją w standardowy sposób, a nie przy pomocy komendy ABORT. Otrzymamy wtedy komunikat, że co prawda anulowaliśmy sesję ale SQL Server rozpoznał, że mamy tam operację, która może zostać zapauzowana i taką też operację wykona:

Ciekawe prawda? Jak dla mnie dosyć fajne udogodnienie. Dodatkowo jeśli po raz kolejny uruchomimy ALTER INDEX REBUILD zamiast operacji RESUME to operacja zostanie wznowiona.

No dobrze wiemy mniej więcej jak poruszać się po tym mechaniźmie jednak ktoś z was może zadać pytanie “co jeżeli wiemy, że mamy np. 5 minut na przebudowanie indeksu? Czy to oznacza, że musimy zaplanować przebudowę i ją pauzować po 5 minutach w ręczny sposób?” Odpowiedź brzmi “nie, nie musimy nic robić w ręczny sposób”. Oprócz standardowego podejścia mamy możliwość zdefiniowania maksymalnej ilości minut jaką mamy przeznaczoną na wykonanie tej operacji przy pomocy klauzuli MAX_DURATION:

Powyższa opcja jest szczególnie użyteczna w momencie gdy mamy z góry ustalony czas na wykonanie operacji i chcemy przebudować tyle indeksu na ile wystarczy czasu. Maksymalny limit jaki możemy przypisać to 10080 minut ale myślę, że spełnia to oczekiwania nawet najbardziej wybrednych użytkowników i największych indeksów.

Omawiany mechanizm ma kilka ograniczeń takich jak to, że odnosi się on tylko do indeksów rowstore, nie możemy użyć opcji SORT_IN_TEMPDB ani mieć wyłączonego (disabled) indeksu. To co mnie szczególnie ciekawiło to co się stanie gdy spróbuję stworzyć jeszcze jeden indeks na tabeli docelowej i również przebudować go z możliwością pauzy. Zgodnie z tym czego oczekiwałem otrzymałem błąd:

Jak wspominałem we wstępie wraz z premierą SQL Server 2019 mechanizm został nieco rozszerzony. Mamy dodatkową możliwość związaną nie tylko z przebudową indeksu ale również z samym tworzeniem indeksu:

W mojej głowie pojawiło się kilka pytań dotyczących tego mechanizmu. Co się dzieje w momencie gdy tworzymy indeks i całą operację zatrzymamy, czy jest możliwość podejrzenia w jakiś sposób tej struktury? Oczywiście, że tak! Najpierw podejrzyjmy sys.indexes:

 

Na powyższym zrzucie widzimy, że tabela jest widoczna jako sterta, a naszego tworzonego indeksu nie widać. Spróbujmy zejść nieco niżej  i zajrzeć do sys.dm_db_database_page_allocations :

I właśnie w tym miejscu jesteśmy w stanie zobaczyć nasz jeszcze nieukończony indeks. Po zakończeniu całej operacji oba widoki pokazują już wszystko w taki sam sposób.

 

Drążąc dalej usunąłem stworzony wyżej index zgrupowany i na powstałej stercie stworzyłem index nieklastrowany:

Następnie na nowo stworzyłem indeks klastrowany w sposób umożliwiający zatrzymanie tej operacji i jej późniejsze wznowienie. W trakcie trwania operacji zajrzałem do widoków systemowych raz jeszcze i otrzymałem następujące informacje:

W momencie gdy tworzymy indeks zgrupowany na tabeli która już ma indeksy niezgrupowane to pamiętajmy, że te indeksy niezgrupowane również wymagają przebudowania. Dzieje się tak ponieważ indeks niezgrupowany na stercie odwołuje się do głównej tabeli przez RID co jest całkiem inaczej rozwiązane w przypadku odwoływań do tabeli z indeksem klastrowanym.

Ostatnią rzeczą jaką chciałbym sprawdzić jest sytuacja gdzie zatrzymamy proces tworzenia lub przebudowy indeksu i dosyć mocno zmodyfikujemy dane w tabeli źródłowej. Dlatego też zainicjowałem tabelę od nowa używając tego samego skryptu co poprzednio:

Następnie zatrzymałem ten proces przy stanie około 44%:

W dalszej kolejności usunąłem trochę danych:

Po sprawdzeniu widoku sys.index_resumable_operations procent nieco się zmienił co wskazuje, że cały mechanizm jest niejako świadomy operacji, które są wykonywane na tabeli:

Wstawienie dodatkowych wierszy również wpłynęło na to wyliczenie:

to by było na tyle jeśli chodzi o omawianą funkcjonalność. Jest to naprawdę ciekawy mechanizm, który dosyć dobrze się sprawdza w praktyce i rozwiązuje niektóre problemy. Niezwykle użyteczne są zarówno operacje dostępne od SQL Server 2017 (ALTER INDEX REBUILD) jak i te dodane w najnowszym na tę chwilę SQL Server 2019 (CREATE INDEX), pamiętajmy również, że obie te funkcjonalności mamy dostępne w Azure SQL Database. Microsoft daje nam coraz więcej możliwości ułatwiających wiele aspektów pracy z bazą danych. Nam nie pozostaje nic innego jak cieszyć się z kierunku jaki obrała firma i usprawniać własne bazy danych. Pozdrawiam!

Adrian Chodkowski
Follow me

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *