SQL Server 2017 újdonság - Online Resumable Index

Ahogy az SQL Server 2016 esetén is, itt is megvan a kedvenc új funkcióm. A 2016-os verzió esetén a partíció szintű truncate table volt, itt most azt online resumable index lett. Miért is ez lett? Sok okom van:

• FULL recovery modelt használok éles környezetekben a legtöbb esetben. Ebből következik, hogy tranzakciós log mentést is csinálni kell.
• Előfordult már, hogy megtelt a log meghajtó (nem, nem akarok róla beszélni :) ), ami egy hosszan futó tranzakció miatt volt.
• Az index rebuild egy nagy tranzakció, sokáig tarthat, a leállítás inkább problémát generált, mintsem hasznot hozott volna.

Ezek mentén már érthető lehet, miért lett a kedvencem:

• megállítható az index rebuild,
• nincs az, mint a régebbi verzióknál, hogy meg kell várni az index rebuild visszavonását, rollback-jét,
• a tranzakciós log mentés is hasznos tud lenni, mert engedi az un. log truncate-et (ami ugye nem az, hogy visszaadjuk az OS-nek a lemezterületet, - hanem csak a VLF státuszát állítja 2-ről - aktív - 0-ra - inaktívra).

Azért ez az első verzió, szóval vannak benne furcsaságok, de lesz ez jobb is. Ezek után lássuk, hogyan is működik.

Demó környezet beállítása

A demóhoz szükségünk van a WideWorldImporters adatbázisra, amit a https://github.com/Microsoft/sql-server-samples/tree/master/samples/databases/wide-world-importers oldalról lehet letölteni. Itt elég jól dokumentált, hogyan is kell feltenni. Miután megvan az adatbázis át kellene váltani FULL recovery modelre, majd egy FULL és tranzakciós log mentést is kell csinálni. Ezt az alábbi kód segítségével el lehet végezni:

1USE [master]
2GO
3ALTER DATABASE [WideWorldImporters] SET RECOVERY FULL
4GO
5BACKUP DATABASE [WideWorldImporters] TO DISK = N'nul'
6GO
7BACKUP LOG [WideWorldImporters] TO DISK = N'nul'
8GO

A mentések nul device-ra mennek, azaz nem lesz a diszken mentés, szóval óvatosan!

Válasszunk indexet

Nem, ez nem autókereskedés és extra választás :), hanem egy index, amit újra fogunk építeni. Ez legyen a Sales.OrderLines tábla clustered indexe. Ennek nézzük meg néhány adatát: mennyi sor van és mennyi page-en van tárolva.

 1USE [WideWorldImporters];
 2GO
 3SELECT [o].[name] AS [table_name],
 4      [i].[name] AS [index_name],
 5      [p].[index_id],
 6      [au].[type_desc],
 7      [au].[used_pages],
 8      [p].[rows]
 9FROM   [sys].[allocation_units] AS [au]
10      JOIN
11      [sys].[partitions] AS [p] ON [au].[container_id] = [p].[hobt_id]
12      JOIN
13      [sys].[objects] AS [o] ON [o].object_id = [p].object_id
14      LEFT JOIN
15      [sys].[indexes] AS [i] ON [i].object_id = [p].object_id
16                           AND [i].[index_id] = [p].[index_id]
17WHERE  [p].object_id = OBJECT_ID('[Sales].[OrderLines]')
18      AND [i].[index_id] = 1;

Látható, hogy 5115 page és 231412 sor van az én esetemben. Ez elegendő lesz a demóhoz.

Tranzakciós log

Itt kezd érdekes lenni a dolog. Nézzük meg, hogy a tranzakciós logban a VLF-ek milyen állapotban vannak. Ehhez mindjárt három scriptet is adok: az első a régi módszer, ami SQL Server 2017 előtti verziókban is működik, a másik kettő új DMV.

1DBCC LOGINFO('WideWorldImporters')

Ez az alábbi eredményt adja vissza:

1SELECT * FROM sys.[dm_db_log_info](DB_ID('WideWorldImporters'));
2GO

Ennek az eredménye az alábbi lett:

 1SELECT [dm_db_log_stats].[database_id],
 2      [dm_db_log_stats].[recovery_model],
 3      [dm_db_log_stats].[total_vlf_count],
 4      [dm_db_log_stats].[total_log_size_mb],
 5      [dm_db_log_stats].[active_vlf_count],
 6      [dm_db_log_stats].[active_log_size_mb],
 7      [dm_db_log_stats].[log_truncation_holdup_reason],
 8      [dm_db_log_stats].[log_since_last_log_backup_mb],
 9      [dm_db_log_stats].[log_since_last_checkpoint_mb]
10FROM   [sys].[dm_db_log_stats](DB_ID('WideWorldImporters'));
11GO

Ennek az eredménye alább:

Index rebuild

Akkor most építsük újra az indexet, de menet közben állítsuk is meg. Amit fontos megemlítenem rögtön az elején, hogy a megállítás csak az online rebuild esetére igaz, ha ezt nem adjuk meg, akkor hibát kapunk, illetve látni fogjuk, hogy miért is érdekes ez a későbbiek során.

1ALTER INDEX [PK_Sales_OrderLines] ON [Sales].[OrderLines]
2REBUILD WITH(ONLINE = ON, RESUMABLE = ON);
3GO

Vegyük észre a RESUMABLE = ON kapcsolót. Ettől lesz megállítható és újraindítható az index rebuild. Elindítás után, egy másik query ablakban a következő kóddal álítsuk meg, függesszük fel az index rebuild műveletet:

1ALTER INDEX [PK_Sales_OrderLines] ON [Sales].[OrderLines] PAUSE;
2GO

Ennek hatására az index rebuild leállt, ráadásul kaptunk egy gyönyörű hibaüzenetet. Ez nem bug, hanem feature :)

1Msg 1219, Level 16, State 1, Line 49
2Your session has been disconnected because of a high priority DDL operation.
3Msg 1219, Level 16, State 1, Line 49
4Your session has been disconnected because of a high priority DDL operation.
5Msg 596, Level 21, State 1, Line 48
6Cannot continue the execution because the session is in the kill state.
7Msg 0, Level 20, State 0, Line 48
8A severe error occurred on the current command.  The results, if any, should be discarded.

Ez csak annyit jelent, hogy fel van függesztve az index rebuild, semmi komoly :). Remélhetőleg ezen lesz még faragva, mert így eléggé ijesztő azért.

Felfüggesztett index rebuild állapota

Most, hogy megállítottuk az index újraépítést, nézzük meg ennek az állapotát egy másik DMV-ben:

1SELECT *
2FROM   [sys].[index_resumable_operations];
3GO

Már megint a tranzakciós log

Nézzük meg ismét a tranzakciós logot, hogy mit látunk:

 1SELECT [dm_db_log_stats].[database_id],
 2      [dm_db_log_stats].[recovery_model],
 3      [dm_db_log_stats].[total_vlf_count],
 4      [dm_db_log_stats].[total_log_size_mb],
 5      [dm_db_log_stats].[active_vlf_count],
 6      [dm_db_log_stats].[active_log_size_mb],
 7      [dm_db_log_stats].[log_truncation_holdup_reason],
 8      [dm_db_log_stats].[log_since_last_log_backup_mb],
 9      [dm_db_log_stats].[log_since_last_checkpoint_mb]
10FROM   [sys].[dm_db_log_stats](DB_ID('WideWorldImporters'));
11GO

1BACKUP LOG [WideWorldImporters] TO DISK = N'nul'
2GO
3/*
4Processed 10664 pages for database 'WideWorldImporters', file 'WWI_Log' on file 1.
5BACKUP LOG successfully processed 10664 pages in 0.072 seconds (1157.070 MB/sec).
6*/

Ennek eredménye, hogy 10664 page ki lett írba a logból, illetve a VLF-ek is inaktívvá váltak, ahol nem volt aktív tranzakció. Vajon igaz ez? Nézzük meg a mentés előtt használt script segítségével:

Hol van a "félkész" indexem?

Jogos a kérdés. Vajon hova rakja az SQL Server a "félkész" indexemet? Amikor megkérdeztem kollégákat erről, mindenki a tempdb-re szavazott első felindulásból, de amikor újraindítottam a szervert és úgy is befejeződött az újraindított index rebuild, akkor azért gondolkodóba estek. Ugye az elején írtam, hogy csak az ONLINE = ON kapcsolóval együtt működik a dolog. Az online index művelet esetén egy "másolatot" készít az eredeti index alapján, azaz a meglévő index mellé fogja tárolni. Valóban? Lássuk csak:

 1SELECT [o].[name] AS [table_name],
 2      [i].[name] AS [index_name],
 3      [p].[index_id],
 4      [au].[type_desc],
 5      [au].[used_pages],
 6      [p].[rows]
 7FROM   [sys].[allocation_units] AS [au]
 8      JOIN
 9      [sys].[partitions] AS [p] ON [au].[container_id] = [p].[hobt_id]
10      JOIN
11      [sys].[objects] AS [o] ON [o].object_id = [p].object_id
12      LEFT JOIN
13      [sys].[indexes] AS [i] ON [i].object_id = [p].object_id
14                           AND [i].[index_id] = [p].[index_id]
15WHERE  [p].object_id = OBJECT_ID('[Sales].[OrderLines]')
16      AND [i].[name] IS NULL;
17GO

Akkor most fejezzük be az index újraépítést

Ehhez nem kell mást tenni, csak az alábbi parancsot kell futtatni:

1ALTER INDEX [PK_Sales_OrderLines] ON [Sales].[OrderLines]  RESUME

Amint ezt lefutott, az index rebuild onnan folytatta, ahol abbahagyta és a tranzakciós logunk is kezelhető maradt.

Összegzés

Szerintem ez egy nagyon jó funkció, nagyon sok kellemetlen dolgot ki lehet kerülni vele, de legalább ugyan ennyit is lehet generálni :). Több kérdés is felmerülhet még ezzel kapcsolatban, hogy mi van akkor, ha sok update van a táblán, stb. Ezekbe most nem mennék bele, az már egy hosszabb történet. Remélem ettől függetlenül másoknak is hasznos lesz ez a funkció.