都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系（たまに、OSS系などマイクロソフト以外の技術も...）の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

トップ > SQL Server > Resumable Online Index Rebuild is in public preview for SQL Server 2017 CTP 2.0 をお試してみた

Resumable Online Index Rebuild is in public preview for SQL Server 2017 CTP 2.0 をお試してみた

SQL Server SQL Server 2017

　何か目に違和感 ( 痛いわけではない ) を感じてしまい、朝早く起きたので....記事書きます(笑)。

　CTP2.0 ( SQL Server 2017 ) で Resumable Online Index Rebuild という大規模データベースを運用する上で非常に便利な機能が搭載されました。
blogs.technet.microsoft.com

　Resumable Online Index Rebuild は下記のシナリオで使われることを想定しています。

　索引の再構築が失敗した後で、索引の再作成操作を再開することができます。これにより、大きな表のインデックスをリビルドするときに時間を削減することが可能ですね。

  • Resume an index rebuild operation after an index rebuild failure, such as after a database failover or after running out of disk space. There is no need to restart the operation from the beginning. This can save a significant amount of time when rebuilding indexes for large tables.

　進行中のインデックスリビルドを一時停止し、後で再開できます。リソースを大きく使用するバッチなどと競合しないように一時停止および再開ができますね。

  • Pause an ongoing index rebuild operation and resume it later. For example, you may need to temporarily free up system resources to execute a high priority task or you may have a single maintenance window that is too short to complete the operation for a large index. Instead of aborting the index rebuild process, you can pause the index rebuild operation and resume it later without losing prior progress.

　大量のログスペースを使用することなく、大規模なインデックスを再構築し、他のメンテナンス作業をブロックするロングトランザクションを使用します ( これいいのかな？ )。これにより、ログの繰り越し、長時間実行されるインデックスリビルドでエラーを回避可能です。※うーん、このあたりはもっと調査する必要がありそうだなぁ。英語力がないだけなのか....

  • Rebuild large indexes without using a lot of log space and have a long-running transaction that blocks other maintenance activities. This helps log truncation and avoid out-of-log errors that are possible for long-running index rebuild operations.

　それでは、実際に試してみましょう。SQL Server 2017 向けの ALTER INDEX (Transact-SQL) | Microsoft Docs 構文を参照してみます。見慣れないオプションが増えてますね。

ALTER INDEX { index_name | ALL } ON <object>  
{  
      REBUILD {  
            [ PARTITION = ALL ] [ WITH ( <rebuild_index_option> [ ,...n ] ) ]   
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) [ ,...n ] ]  
      }  
    | DISABLE  
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]  
    | SET ( <set_index_option> [ ,...n ] )   
    | RESUME [WITH (<resumable_index_options>,[…n])]
    | PAUSE
    | ABORT
}  
[ ; ]  

<object> ::=   
{  
    [ database_name. [ schema_name ] . | schema_name. ]   
    table_or_view_name  
}  

<rebuild_index_option > ::=  
{  
      PAD_INDEX = { ON | OFF }  
    | FILLFACTOR = fillfactor   
    | SORT_IN_TEMPDB = { ON | OFF }  
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | STATISTICS_INCREMENTAL = { ON | OFF }  
    | ONLINE = {   
          ON [ ( <low_priority_lock_wait> ) ]   
        | OFF } 
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | COMPRESSION_DELAY = {0 | delay [Minutes]}  
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }   
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]  
}  

<single_partition_rebuild_index_option> ::=  
{  
      SORT_IN_TEMPDB = { ON | OFF }  
    | MAXDOP = max_degree_of_parallelism  
    | RESUMABLE = { ON | OFF } 
    | MAX_DURATION = <time> [MINUTES}     
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} }  
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }  
}  

<reorganize_option>::=  
{  
       LOB_COMPACTION = { ON | OFF }  
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF}  
}  

<set_index_option>::=  
{  
      ALLOW_ROW_LOCKS = { ON | OFF }  
    | ALLOW_PAGE_LOCKS = { ON | OFF }  
    | IGNORE_DUP_KEY = { ON | OFF }  
    | STATISTICS_NORECOMPUTE = { ON | OFF }  
    | COMPRESSION_DELAY= {0 | delay [Minutes]}  
}  

<resumable_index_option> ::=
 { 
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION =<time> [MINUTES]
    | <low_priority_lock_wait>  
 }

<low_priority_lock_wait>::=  
{  
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,   
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )  
}  
Copy
Code
-- Syntax for SQL Data Warehouse and Parallel Data Warehouse  

ALTER INDEX { index_name | ALL }  
    ON   [ schema_name. ] table_name  
{  
      REBUILD {  
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ] 
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> )] ] 
      }  
    | DISABLE  
    | REORGANIZE [ PARTITION = partition_number ]  
}  
[;]  

<rebuild_index_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( {<partition_number> [ TO <partition_number>] } [ , ...n ] ) ]   
}

<single_partition_rebuild_index_option > ::=   
{  
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }  
}

　それでは、インデックスリビルド中に一度処理を停止し、再開するまでの動作を確認してみましょう。下記のクエリは、インデックスリビルドが実行中であれば、停止するといったものです。Resumable Online Index Rebuild の状態を確認するには、sys.index_resumable_operations (Transact-SQL) | Microsoft Docsを使用します。

DECLARE @state INT
SET @state = 0

-- ステータスが 0 の間はループする。
WHILE (@state = 0) 
BEGIN
    -- 処理中のリビルド処理が存在したら、リビルド処理を PAUSE する。
    IF (SELECT Count(state) FROM sys.index_resumable_operations WHERE state = 0) > 0
    BEGIN
        -- 0.5 秒待機する。(すぐ PAUSE してしまうと、sys.index_resumable_operations 結果を見ても処理が進んでいる感を見ることができないため。)
        WAITFOR DELAY '00:00:00.500'
		-- インデックスリビルド処理を PAUSE する。
        ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
        ON  Sales.SalesOrderDetail PAUSE;
		-- ステータスを 1 にし、ループを終了させる。
        SET @state = 1
    END
END

f:id:koogucc11:20170428074257p:plain

　次に、下記の DDL 文を SQL Server Management Studio で実行しましょう。実行後すぐに DDL の処理が停止されます。

ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON  Sales.SalesOrderDetail REBUILD WITH(
        ONLINE = ON, MAXDOP = 1, RESUMABLE = ON, MAX_DURATION = 5
);

f:id:koogucc11:20170428074540p:plain

※RESUMABLE は REBUILD 処理を再開可能にするかのオプションです。ON の場合は、ONLINE = ON が必須となります。,　 MAX_DURATION は REBUILD 処理を最大何分間実行するかを指定します。0 分より大きく、1 週間以下（ 7 x 24 x 60 = 10080 分 ）とする必要があります。上記の DDL 文の場合は、最大で リビルド処理を 5 分間実行し、完了しない場合は自動的に途中の状態で待機します。

　インデックスリビルド処理がどのような状態か確認します。下記のクエリを SQL Server Management Studio で実行しましょう。

SELECT  
    * 
FROM 
    sys.index_resumable_operations

　先ほど実行した ALTER 文がサスペンドされているのが判断できます。
f:id:koogucc11:20170428074751p:plain

f:id:koogucc11:20170428074801p:plain

　再度、下記のクエリを実行させます。

DECLARE @state INT
SET @state = 0

-- ステータスが 0 の間はループする。
WHILE (@state = 0) 
BEGIN
    -- 処理中のリビルド処理が存在したら、リビルド処理を PAUSE する。
    IF (SELECT Count(state) FROM sys.index_resumable_operations WHERE state = 0) > 0
    BEGIN
        -- 0.5 秒待機する。(すぐ PAUSE してしまうと、sys.index_resumable_operations 結果を見ても処理が進んでいる感を見ることができないため。)
        WAITFOR DELAY '00:00:00.500'
		-- インデックスリビルド処理を PAUSE する。
        ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
        ON  Sales.SalesOrderDetail PAUSE;
		-- ステータスを 1 にし、ループを終了させる。
        SET @state = 1
    END
END

f:id:koogucc11:20170428074917p:plain

　リビルド処理を再開させる場合は、下記のクエリを SQL Server Management Studio で実行します。

ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] 
ON [Sales].[SalesOrderDetail] RESUME WITH(MAXDOP = 4);

f:id:koogucc11:20170428075027p:plain

　上記の DDL 文の処理が中断されたら、下記のクエリを実行してみましょう。リビルドが停止され、MAXDOP が 1 → 4 に変更されたのことが判断できます。RESUME 時にもオプションが変更できることがわかりますね。

SELECT  
    * 
FROM 
    sys.index_resumable_operations

f:id:koogucc11:20170428075124p:plain

　最後に下記のクエリを実行することで、リビルド処理を完了させることができます。

ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] 
ON [Sales].[SalesOrderDetail] RESUME;

f:id:koogucc11:20170428075221p:plain

　下記のクエリを SQL Server Management Studio で実行してみましょう。sys.index_resumable_operations に処理中のレコードも存在しないことが判断できますね。

SELECT  
    * 
FROM 
    sys.index_resumable_operations

f:id:koogucc11:20170428075354p:plain

　インデックスリビルドの中断→再開時にオプションの変更→再度中断→再開までの動作が理解できたかと思います。今まで難しかった巨大なインデックスのリビルド運用も、Resumable Online Index の機能 ( RESUME,PAUSE および sys.index_resumable_operations ) をうまく使用することで運用も無理なく行うこと ( 朝になってもリビルドが終わってないとか... ) ができそうですね。個人的には Resumable Statistics Update みたいな機能もほしいです...

　アデノウィルスによる結膜炎はほぼ完治したようです。このような病気にならないように免疫力をあげないといけないですね。免疫力アップには...

　などがいいようです。もういい歳だし、考えないとなぁ。