Blog
Tuesday, 29. September 2020

Resumable Online Index Create and Rebuild Operations

Simon
IT-Consultant

Anders als beim SQL Server 2017, bei dem nur eine wiederaufnehmbare Online-Indexwiederherstellung durchgeführt werden kann, verfügt SQL Server 2019 über die Funktion Resumable Online Index Create. Doch wofür wird diese Funktion genau gebraucht?

Angenommen, wir müssen einen Index für eine sehr große Tabelle mit über einer Millionen Zeilen erstellen. Das Erstellen dieses Index würde einige Zeit in Anspruch nehmen. Während der Erstellung des Index könnte ein unerwarteter Fehler auftreten, es könnte zu Leistungsproblemen oder einem Failover kommen, und die Anweisung zum Erstellen des Indexes muss abgebrochen werden. Dies würde sehr viel Zeit, Ressourcen und Nerven kosten, da der Index anschließend noch einmal komplett neu erstellt werden müsste.

SQL Server 2019 bietet nun die Möglichkeit, den Online-Indexerstellungsprozess anzuhalten und wieder fortzusetzen. Weiterhin kann der Vorgang auch abgebrochen werden, ohne dabei bereits vorhandene Indizes zu beeinflussen. Es muss also nicht von vorne begonnen werden, wobei enorm viel Zeit gespart werden kann.

Es handelt sich hierbei also um ein Feature, das gerade bei den folgenden Themen sehr nützlich sein kann:

  • Datenbanken Failovern
  • Festplatten Problemen
  • Konflikten von Systemresourcen
  • Und dem Minimieren des Log-Wachstums

Um dieses Feature zu verwenden müssen wir vorerst

  • den Kompatibilitätsmode der Datenbank auf SQL Server vNext einstellen (150 – SQL Server 2019)
  • sowie die Variable Resumable=ON für das Online Index Create Kommando setzen.

Wir verwenden in diesem Beitrag eine SQL Server 2019 Instanz mit dem Namen Test-Instance, eine Datenbank mit dem Namen TestBase und das SQL Server Management Studio (SSMS) 18.4.

Beispiel für die Online Index Create Funktion
Wir wollen uns die Funktion jetzt in der Praxis anschauen. Hierfür werden wir uns zunächst mit unserer Instanz mit SSMS verbinden:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Anschließend setzen wir unsere Datenbank auf den richtigen Kompatibilitätsmode. Hierfür die Datenbank rechtsklicken und Properties (Eigenschaften) auswählen. Unter Optionen (Options) kann nun das Kompatibilitätslevel gewählt werden. Wir stellen sicher, dass hier SQL Server 2019 (150) ausgewählt ist:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Nun wollen wir für unser Beispiel eine Tabelle mit ein paar Test-Daten erstellen. Hierfür verwenden wir folgendes Skript:

USE TestBase
GO
CREATE Table TestTable
(
Id int identity primary key,
Name nvarchar(50),
Address nvarchar(50)
)
Declare @Id int
Set @Id = 1
While @Id <= 10000000 --you can change the number of rows using this value
Begin 
Insert Into TestTable values (‘TestData - ’ + CAST(@Id as nvarchar(10)),
‘TestCountry - ’ + CAST(@Id as nvarchar(10)) + ' name’)
Print @Id
Set @Id = @Id + 1
End

Jetzt können wir für diese Tabelle einen Index erstellen und die Variable Resumable=ON setzen. Um allerdings Resumable=ON setzen zu können, muss auch Online=ON gesetzt sein. Wir tun dies alles mit folgendem Skript:

USE TestBase
GO
CREATE NONCLUSTERED INDEX NCI_TestTable
ON dbo.TestTable(Name)
WITH (ONLINE=ON,RESUMABLE = ON);

Wir wollen jetzt einen zweiten Index für diese Tabelle erstellen, die Erstellung des Index abbrechen und anschließend beobachten, wie sich der Index danach verhält.

Dafür erstellen wir mit dem folgenden Skript einen weiteren Index, Resumable=ON bleibt hierbei gesetzt:

USE TestBase
GO
CREATE NONCLUSTERED INDEX NCI_TestTable_1
ON dbo.TestTable(Name,Address)
WITH (Online = ON,RESUMABLE = ON);

Während diese Abfrage läuft und der Index erstellt wird, öffnen wir ein zweites Abfragefenster und führen folgendes Kommando aus:

ALTER INDEX [NCI_TestTable_1] ON [dbo].[TestTable] 
PAUSE;
GO

In unserem ersten Abfragefenster, in dem die Erstellung unseres Index stattfindet, erhalten wir nun folgende Fehlermeldung:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Unser zweites Abfragefenster allerdings, meldet einen erfolgreichen Abschluss des ausgeführten Kommandos, da das Pausieren der Indexerstellung erfolgreich war:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Mit dem folgenden Skript können wir nun den aktuellen Ausführungsstatus von wiederaufnehmbaren Indizes Überwachen und prüfen:

SELECT 
name, 
percent_complete,
state_desc,
last_pause_time,
page_count
FROM sys.index_resumable_operations;

Nach Ausführen dieses Skripts können wir sehen, dass sich der Index in einem pausierten Zustand befindet. Weiterhin können wir die Seitenzahl, die die Gesamtzahl der Indexseiten definiert, einsehen:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Um nun die Erstellung des Index fortzusetzen, können wir folgendes Skript ausführen:

ALTER INDEX [NCI_TestTable_1] ON [dbo].[TestTable] 
RESUME;
GO

Wenn wir nun das Skript zum Fortsetzen der Indexerstellung ausführen und direkt abbrechen, können wir bei anschließender Überprüfung des Ausführungsstatus unseres Index sehen, dass sich dieser immer noch in einem pausierten Zustand befindet, die Werte der Spalten percent_complete und page_count sich allerdings erhöht haben:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Wir lassen den Index weiterhin im pausierten Zustand und versuchen, einen weiteren wiederaufnehmbaren Index für die selbe Tabelle zu erstellen. Wir erhalten eine Fehlermeldung, die uns mitteilt, dass wir keinen weiteren wiederaufnehmbaren Index erstellen können, da sich der vorherige Index im Status der Wiederherstellung befindet:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Wir führen das Skript zum Fortsetzen der Indexerstellung erneut aus und brechen es diesmal nicht ab. Prüfen wir nun erneut den Ausführungsstatus, können wir sehen, dass sich der Status von Paused zu RUNNING geändert hat:

Mainzer Datenfabrik - Resumable Online Index Create and Rebuild Operations

Am Ende sollte noch erwähnt werden, dass es nicht möglich ist, einen solchen wiederaufnehmbaren Index für die TempDB zu erstellen.

Interesse geweckt?

Unsere Expert:innen stehen Ihnen bei allen Fragen rund um Ihre IT Infrastruktur zur Seite.

Kontaktieren Sie uns gerne über das Kontaktformular und vereinbaren ein unverbindliches Beratungsgespräch mit unseren Berater:innen zur Bedarfsevaluierung. Gemeinsam optimieren wir Ihre Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!

Taunusstraße 72
55118 Mainz
info@madafa.de
+49 6131 3331612
Bürozeiten
Montag bis Donnerstag:
9:00 - 17:00 Uhr MEZ

Freitags:
9:30 - 14:00 Uhr MEZ