Resumable Index Rebuilds

Mit dem SQL Server 2017 ist es möglich Online Index Rebuilds zu unterbrechen und zu einem späteren Zeitpunkt fortzusetzen.Ein weiterer Vorteil ist, dass Logging Vorgänge im Transaktionslog hierbei minimiert werden. Bisher war es so, dass ein unterbrochener Index Rebuild immer dazu führt, dass der komplette Rebuild komplett neu durchgeführt werden musste.

Die so genannten Resumable Index Rebuilds bieten sich vor Allem für sehr große Indizes an, bei denen ein Rebuild recht lange dauert. So ist es möglich den Rebuild Vorgang zu pausieren, wenn eine wichtige Transaktion laufen muss und später den Vorgang fortzusetzen. Im Folgenden schauen wir uns an wie dies in der Praxis durchgeführt werden kann.

Um ein Resumable Index Rebuild anzustoßen, wird die Option RESUMABLE = ON verwendet:

ALTER INDEX IX_TestIndex ON [dbo].[TestTable] 
	REBUILD WITH 
		( RESUMABLE = ON, ONLINE = ON );

ALTER INDEX IX_TestIndex ON [dbo].[TestTable]

REBUILD WITH

( RESUMABLE = ON, ONLINE = ON );

Dabei ist zu beachten, dass diese Option nur im Zusammenhang mit ONLINE = ON unterstützt wird, ein Offline Rebuild lässt sich also nicht pausieren und zu einem späteren Zeitpunkt fortsetzen.

Um den Index Rebuild jetzt jederzeit anzuhalten, kann man die Option PAUSE verwenden:

ALTER INDEX IX_TestIndex ON [dbo].[TestTable]
    PAUSE;

1 2	ALTER INDEX IX_TestIndex ON [dbo].[TestTable] PAUSE;

Um zu sehen, welche Index Operationen fortgesetzt werden können, genügt ein Blick in die View sys.index_resumable_operations. Alle Einträge mit state=1 sind dabei pausiert und können fortgesetzt werden, alle Einträge mit einem state=0 sind derzeit laufende Index Operationen die unterbrochen werden können.

SELECT * FROM  sys.index_resumable_operations WHERE state=1;

1	SELECT * FROM sys.index_resumable_operations WHERE state=1;

Um jetzt die soeben angehaltene Index Operation fortzusetzen genügt es einfach den Index Rebuild per RESUME fortzusetzen:

ALTER INDEX IX_TestIndex ON [dbo].[TestTable] 
	RESUME;

1 2	ALTER INDEX IX_TestIndex ON [dbo].[TestTable] RESUME;

Der SQL Server setzt dann den Online Index Rebuild genau an der Stelle fort, an der er zuvor unterbrochen wurde.

Zusätzliche Optionen

Sehr interessant ist auch die Option MAX_DURATION mit der man direkt in der Lage ist, zu sagen wie lange ein Online Index Rebuild laufen soll, bevor er automatisch pausiert wird. Die Zeit wird dabei in Minuten angegeben:

ALTER INDEX IX_TestIndex on [dbo].[TestTable] 
    REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=60);

1 2	ALTER INDEX IX_TestIndex on [dbo].[TestTable] REBUILD WITH (ONLINE=ON, RESUMABLE=ON, MAX_DURATION=60);

In diesem Fall wird der Index Rebuild nach einer Stunde pausiert.

Will man einen laufenden oder pausierten Resumable Index Rebuild komplett abbrechen, geht dies über die Option ABORT:

ALTER INDEX IX_TestIndex ON [dbo.TestTable] ABORT;

1	ALTER INDEX IX_TestIndex ON [dbo.TestTable] ABORT;

Fazit

Resumable Index Rebuilds sind interessant, um sehr große Indizes neu aufzubauen und bei Bedarf zu unterbrechen, damit wichtige Aufgaben in der vollen Geschwindigkeit durchgeführt werden können. Auch interessant ist dies für Index Rebuilds bei denen vorher nicht klar ist wie lange sie benötigen und ob es möglich ist den Rebuild in der geforderten Wartungszeit durchzuführen.