Stop defragmenting and start living: introducing auto index compaction というアナウンスがあり、機能のドキュメントも公開されました。
この機能は自動的にインデックス再構成 (REORGANIZE) 相当の動作を、明示的なジョブを作成 / 設定しなくても自動的に実行することができる機能となります。
基本的な動作については、上述のドキュメントを確認してもらえばよいと思いますが、この機能について検証してみて気づいた点をまとめておきたいと思います。
自動圧縮で対象となるデータ
自動圧縮で対象となるデータについては 利点と考慮事項 に次のように記載されています。
自動圧縮は、最近変更されたページにのみ機能します。 その結果、圧縮のオーバーヘッドは、すべてのページを処理するインデックスの再構築またはインデックスの再編成と比較して最小限になります。
自動圧縮は直近で変更されたページにのみ機能し、オーバーヘッドを抑えるという記載があります。
また、制限事項 にも次の記載があります。
リーフ レベルのページのみが考慮されます。 これには、次のページが含まれます。
- クラスター化インデックスと制約。
- 非クラスター化インデックスと制約。
- XML、フルテキスト、空間、列ストアの内部行セットなどの特殊なインデックス型を格納する内部テーブルの B ツリー インデックス。
次の種類のページは、インデックスの自動圧縮の対象ではありません。
- ヒープ テーブル内のページ。
ROW_OVERFLOW_DATAまたはLOB_DATAアロケーション ユニット内のページ。- 列ストア インデックス内の圧縮された行グループのページ。
- メモリ最適化テーブル内のページ。
テーブル構造については、一般的な行ストアテーブルはサポートされていますが、ヒープについてはサポートされないという特徴があるようです。
通常、インデックスのメンテナンスをする際にメンテナンス範囲を抑えるためにはパーティショニングと併用し、インデックスをメンテナンスするデータ範囲の局所化を検討する必要があります。
自動圧縮では、直近のデータを対象とするため、パーティショニングを使用しなくても、インデックスのメンテナンス範囲を局所化することができるようです。
新規のテーブルであれば最初から自動圧縮を有効にしておけば期待したデータのメンテナンスが行われ、既存のテーブルであれば、一度手動でインデックスのメンテナンスをすれば、以降のデータは自動圧縮の対象となるので、期待したデータのメンテナンスが行われる状態となると考えるとよさそうです。
自動圧縮の情報の基本的な取得方法
自動圧縮については、拡張イベントを使用して圧縮統計を監視する に記載されている「auto_index_compaction_stats」の拡張イベントを取得することで、機能が動作した際の情報を取得することができます。
SELECT
o.name AS event_name,
o.package_guid,
o.type_package_guid,
o.description
FROM sys.dm_xe_objects AS o
INNER JOIN sys.dm_xe_packages AS p
ON o.package_guid = p.guid
WHERE o.object_type = N'event'
AND o.name = 'auto_index_compaction_stats'
ORDER BY
o.name;
SELECT
*
FROM
sys.dm_xe_object_columns
WHERE
object_name = 'auto_index_compaction_stats' AND column_type = 'data';
上記のクエリであれば、拡張イベントの列の説明も取得することができます。
「実際に圧縮が行われているか？」の観点では次のような項目がポイントとなりそうです。
- compact_attempts: ページ圧縮を試行した回数
- compact_completed: 圧縮の試行により、あるページから別のページへ行が移動された回数
- pages_deallocated_compaction: すべての行を別のページへ移動した後に解放されたページ数
- rows_moved: ページ圧縮の一環として、あるページから別のページへ移動された行数
拡張イベントを取得して、これらの情報を確認することで自動圧縮でどの程度、データページの最適化が行われているのかを把握することができます。
自動圧縮が実行されるタイミング
自動圧縮が行われるタイミングについては、どのように機能するのか の次の記述が、実装面での参考になると思います。
インデックスの自動圧縮は、バックグラウンド永続 バージョン ストア (PVS) クリーナー プロセスの一部です。 このプロセスにより、古い行バージョンがデータ ページから定期的に削除されます。 データベースのインデックスの自動圧縮を有効にすると、PVS クリーナーによってインデックスも圧縮されます。
自動圧縮は、高速データベース復旧 (ADR) で使用されている PVS クリーナーのプロセスの一部として実行されているということが記載されています。
「auto_index_compaction_stats」の拡張イベントは 10～11 分程度の実行統計が表示されているようでした。
拡張イベントの情報は実行時に集約された統計のため、拡張イベントのログが出力されるタイミング以外でも自動圧縮は行われているようです。
実際に自動圧縮が行われているかの確認
上述のとおり、拡張イベントは自動圧縮の集約された統計情報となり、実際にどのテーブルに対して自動圧縮が行われているかまでは確認することができません。
現状、自動圧縮が行われているかを確認するためには「トランザクションログから情報を確認する」のがよさそうな雰囲気がありました。
トランザクションログの読み取りは負荷が高いので、運用環境ではなく、検証環境での実行を想定したものとなります。
使用するクエリは次の内容となります。
SELECT
*
FROM sys.fn_dblog(NULL, NULL)
WHERE
[Transaction ID] IN(
SELECT [Transaction ID]
FROM sys.fn_dblog(NULL, NULL)
WHERE [Transaction Name]
IN('CompactSpaceAllocator::AllocateA', 'cleanupPage', 'AutoIndexDefragCompactPages', 'MoveRowsLeft')
)
ORDER BY [Transaction ID];
GO
このクエリは、トランザクションログから自動圧縮に関連していると思われるトランザクションのログを取得するものとなり「AllocUnitName」から、どのテーブルが自動圧縮で処理されたかを確認することができます。
「自分が期待したテーブルが自動圧縮されるか？」については、検証環境で想定されるワークロードを実行して、自動圧縮が実行されるかが重要なポイントとなるのではないでしょうか。
自動圧縮が確認できなかったワークロード
現状、プレビュー機能であり、まだ情報が少ない機能ですので、私の検証方法が間違っている可能性もありますが。
先日、Stream Analytics で SQL Database にデータを書き込む際の使用領域についての考慮点 という投稿を書きました。
この中では「Bulk Insert 実行時の高速挿入」を使用した場合の未使用領域について触れているのですが、この領域が本機能によって使用領域の最適化が行われるかを確認してみました。
結果としては「高速挿入により確保された未使用領域は自動圧縮で最適化されていない雰囲気がある」となっていました。
クラスター化インデックスを設定したテーブルに対して、Bulk Insert (WriteToServer) を使用して、少量データを Insert したテーブルを作成して、自動圧縮の効果を確認していました。
次のように、未使用領域が多いテーブルを作成したのですが、このテーブルに対しては自動圧縮が行われてい挙動が確認できませんでした。
高速挿入時の自動圧縮について、動作していないように見えましたので、Enable auto index compaction during bulk loading with fast inserts としてフィードバックは上げてみました。
「自分が期待するワークロードで自動圧縮が行われているか？」をきちんと確認することは重要かと。