何か目に違和感 ( 痛いわけではない ) を感じてしまい、朝早く起きたので....記事書きます(笑)。
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
次に、下記の 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 );
※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 文がサスペンドされているのが判断できます。
再度、下記のクエリを実行させます。
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
リビルド処理を再開させる場合は、下記のクエリを SQL Server Management Studio で実行します。
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] RESUME WITH(MAXDOP = 4);
上記の DDL 文の処理が中断されたら、下記のクエリを実行してみましょう。リビルドが停止され、MAXDOP が 1 → 4 に変更されたのことが判断できます。RESUME 時にもオプションが変更できることがわかりますね。
SELECT * FROM sys.index_resumable_operations
最後に下記のクエリを実行することで、リビルド処理を完了させることができます。
ALTER INDEX [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] ON [Sales].[SalesOrderDetail] RESUME;
下記のクエリを SQL Server Management Studio で実行してみましょう。sys.index_resumable_operations に処理中のレコードも存在しないことが判断できますね。
SELECT * FROM sys.index_resumable_operations
インデックスリビルドの中断→再開時にオプションの変更→再度中断→再開までの動作が理解できたかと思います。今まで難しかった巨大なインデックスのリビルド運用も、Resumable Online Index の機能 ( RESUME,PAUSE および sys.index_resumable_operations ) をうまく使用することで運用も無理なく行うこと ( 朝になってもリビルドが終わってないとか... ) ができそうですね。個人的には Resumable Statistics Update みたいな機能もほしいです...
アデノウィルスによる結膜炎はほぼ完治したようです。このような病気にならないように免疫力をあげないといけないですね。免疫力アップには...
- 乳酸菌・ビフィズス菌
森下仁丹 ヘルスエイド® ビフィーナS (スーパー) 30日分 ビフィズス菌 乳酸菌 オリゴ糖
- 出版社/メーカー: 森下仁丹
- メディア: ヘルスケア&ケア用品
- この商品を含むブログを見る
- フコイダン
- 出版社/メーカー: 金秀バイオ
- メディア: ヘルスケア&ケア用品
- この商品を含むブログを見る
- ラクトフェリン
- 出版社/メーカー: DHC(ディー・エイチ・シー)
- メディア: ヘルスケア&ケア用品
- 購入: 5人 クリック: 5回
- この商品を含むブログを見る
- スピルリナ
- 出版社/メーカー: DICライフテック
- メディア: ヘルスケア&ケア用品
- クリック: 8回
- この商品を含むブログを見る
- アルギニン
- 出版社/メーカー: バルクスポーツ
- メディア: ヘルスケア&ケア用品
- この商品を含むブログを見る
- アガリクス
- 出版社/メーカー: DHC(ディー・エイチ・シー)
- メディア: ヘルスケア&ケア用品
- この商品を含むブログを見る
- エキナセア
- 出版社/メーカー: 大塚製薬
- 発売日: 2003/04/14
- メディア: ヘルスケア&ケア用品
- 購入: 12人 クリック: 21回
- この商品を含むブログ (2件) を見る
- トランスファーファクター
- 出版社/メーカー: フォーライフリサーチジャパン LLC
- メディア: ヘルスケア&ケア用品
- この商品を含むブログを見る
などがいいようです。もういい歳だし、考えないとなぁ。