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:

DROP TABLE IF EXISTS [dbo].[IndexResume]
GO

SELECT 
	*
INTO [dbo].[IndexResume]
FROM
[dbo].[FactInternetSales]
GO

INSERT INTO [dbo].[IndexResume]
SELECT
	*
FROM
	[dbo].[FactInternetSales]
GO 500	

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:

CREATE CLUSTERED INDEX IX_TEST
ON [dbo].[IndexResume] 
(
	[SalesOrderNumber],[SalesOrderLineNumber]
)
GO

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:

ALTER INDEX IX_TEST 
ON [dbo].[IndexResume] 
REBUILD
WITH (
	 ONLINE=ON
	,RESUMABLE = ON
	,MAXDOP=1);
GO

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:

ALTER INDEX IX_TEST 
ON [dbo].[IndexResume] 
PAUSE

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:

ALTER INDEX IX_TEST 
ON [dbo].[IndexResume] 
ABORT

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

ALTER INDEX IX_TEST 
ON [dbo].[IndexResume] 
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:

Warning: An existing resumable operation with the same options was identified for the same index 
on 'dbo.IndexResume'. 
The existing operation will be resumed instead.
The statement has been terminated.
Query was canceled by user.

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:

ALTER INDEX IX_TEST 
ON [dbo].[IndexResume] 
REBUILD
WITH (
	 ONLINE=ON
	,RESUMABLE = ON
	,MAX_DURATION=2 MINUTES
	,MAXDOP=1);
GO

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:

Msg 10637, Level 16, State 3, Line 32
Cannot perform this operation on 'object' with ID 1074102867 
as one or more indexes are currently in resumable index rebuild state. 
Please refer to sys.index_resumable_operations for more details.

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:

CREATE CLUSTERED INDEX IX_TEST
ON [dbo].[IndexResume] 
(
	[SalesOrderNumber],[SalesOrderLineNumber]
)
WITH (
	ONLINE=ON,
	RESUMABLE=ON,
	MAXDOP=1
)
GO

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:

select * from sys.indexes
where object_id=OBJECT_ID(N'dbo.IndexResume')

 

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 :

SELECT object_id,index_id, pages = COUNT(*)
FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.IndexResume'), NULL, NULL, 'LIMITED')
GROUP BY object_id,index_id;

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:

CREATE INDEX IX_TEST2
ON dbo.IndexResume (ProductKey)
GO

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:

DROP TABLE IF EXISTS [dbo].[IndexResume]
GO

SELECT 
	*
INTO [dbo].[IndexResume]
FROM
[dbo].[FactInternetSales]
GO

INSERT INTO [dbo].[IndexResume]
SELECT
	*
FROM
	[dbo].[FactInternetSales]
GO 500	

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

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

DELETE FROM dbo.IndexResume
WHERE DueDateKey>=20140101

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:

INSERT INTO [dbo].[IndexResume]
SELECT
	*
FROM
	[dbo].[FactInternetSales]
GO 50

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!