何か目に違和感 ( 痛いわけではない ) を感じてしまい、朝早く起きたので....記事書きます(笑)。
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
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
WHILE (@state = 0)
BEGIN
IF (SELECT Count(state) FROM sys.index_resumable_operations WHERE state = 0) > 0
BEGIN
WAITFOR DELAY '00:00:00.500'
ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON Sales.SalesOrderDetail PAUSE;
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
WHILE (@state = 0)
BEGIN
IF (SELECT Count(state) FROM sys.index_resumable_operations WHERE state = 0) > 0
BEGIN
WAITFOR DELAY '00:00:00.500'
ALTER INDEX PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID
ON Sales.SalesOrderDetail PAUSE;
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 みたいな機能もほしいです...
アデノウィルスによる結膜炎はほぼ完治したようです。このような病気にならないように免疫力をあげないといけないですね。免疫力アップには...
などがいいようです。もういい歳だし、考えないとなぁ。