SQL Server のインデックスの設定状況について調査する機会があり、その中で「そういえば、そういう機能あったな」というものの一つに「仮定のインデックス」(仮想インデックス) がありました。
本ブログで今まで取り上げたことが無かったので、少しまとめてみたいと思います。
詳細については SQL Server Performance Tuning with Hypothetical Indexes の記事が参考になります。
仮定のインデックスについては、「データベースチューニングアドバイザー」が、インデックス設定の効果を見る際に使用しているものでもあります。
データベースチューニングアドバイザーでは、ワークロード (クエリ) をツールに与えることで、そのクエリに対して推奨されるインデックスを確認することができます。
「インデックスを使用することでどのような効果があるのか」をツールで確認しているのですが、この際に使用されているものが「仮定のインデックス」となります。
仮定のインデックスを使用することで、「既存のワークロードには影響を与えず、インデックス設定による効果を見る」ことができるようになります。
実際のインデックスの作成は行わないが、インデックスの作成による効果を核にすることができます。
それでは、インデックスを使用するための方法を順番に見ていきましょう。
1.is_hypothetical = 1 のインデックスを作成する
仮定のインデックスですが「sys.indexes」の「is_hypothetical」が「1」のインデックスが該当します。
is_hypothetical
bit
1 = インデックスは仮想的であり、データへのアクセス パスとして直接使用することはできません。 仮想インデックスは、列レベルの統計を保持しています。
0 = インデックスは仮想的ではありません。
通常のインデックスであれば、この項目は「0」となっているのですが、仮定のインデックスについては「1」となっています。
「is_hypothetical=1」のインデックスの作成方法ですが、次のようなクエリとなります。
CREATE INDEX [_hypothetical_index_01] ON [tpch].[dbo].[LINEITEM] ([L_RECEIPTDATE], [L_SHIPMODE]) WITH STATISTICS_ONLY = -1
インデックスを作成する際に「STATISTICS_ONLY」というオプションを指定することで「仮定のインデックス」として作成することができます。
このオプションには二つの設定をすることができるようになっています。
- STATISTICS_ONLY = -1
- 収集された統計情報を含む、仮定のインデックスを作成
- STATISTICS_ONLY = 0
- 統計情報を収集せず、仮定のインデックスを作成
「-1」を指定した場合、作成された統計情報に、データが含まれている状態となります。
「0」の場合、作成された統計情報に、データが含まれていない状態となります。
仮定のインデックスは SSMS の GUI からは確認することはできません。
仮定のインデックスが存在しているかについては、sys.indexes から直接確認する必要があります。
SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID('LINEITEM') AND is_hypothetical = 1
2.AUTOPILOT の有効化
作成した仮定のインデックスは、単純に使用することはできません。
先ほど、「L_RECEIPTDATE」に仮定のインデックスを作成しました。
この状態、インデックスに含めた列を使用したクエリを実行してみます。
「Clustered Index Scan」となっているため、インデックスが使用されていない状態ですね。
仮定のインデックスを使用するためには「AUTOPILOT」を使用する必要があります。
AUTOPILOT は「DBCC コマンド」と「SET 句」によって構成されています。
DBCC AUTOPILOT
はじめに、「DBCC AUTOPILOT」を使用して、実行プランを生成する際の情報として、仮定のインデックスを含めるように指定をする必要があります。
DBCC コマンドについては、「DBCC HELP」から確認することができます。
(通常の DBCC HELP では、AUTOPILOT についてのヘルプは表示されないため、「TF2588」を有効にする必要があります)
DBCC TRACEON(2588) DBCC HELP ('AUTOPILOT')
ヘルプとしては次のような内容が表示されます。
dbcc AUTOPILOT (typeid [, dbid [, {maxQueryCost | tabid [, indid [, pages [, flag [, rowcounts]]]]} ]])
非クラスター化インデックスを追加した場合のプランの補正状況については、次のようなクエリで、DBCC の引数を生成することができるかと。
SELECT name, 0 AS typeid, DB_ID() AS database_name, object_id, index_id FROM sys.indexes WHERE object_id = OBJECT_ID('LINEITEM') AND is_hypothetical = 1
DBCC AUTOPILOT には、様々なオプションがありますが、typeid / dbid / object_id / index_id までが設定できれば動作させることができるかと。
SET AUTOPILOT ON
DBCC AUTOPILOT で設定したプラン補正を有効にするためには、SET 句で「SET AUTOPILOT ON」を実行する必要があります。
SET AUTOPILOT ON を設定した場合、実際のクエリの実行は行われずに、推定実行プランの取得のような動作となります。
「SET AUTOPILOT ON」については、DBCC AUTOPILOT を実行した後に有効化する必要があるようなので、実行の順序は気を付けておく必要があるようです。
動作を確認してみる
最終的に使用したクエリがこちらです。
DROP INDEX IF EXISTS [_hypothetical_index_01] on [tpch].[dbo].[LINEITEM] GO CREATE INDEX [_hypothetical_index_01] ON [tpch].[dbo].[LINEITEM] ([L_RECEIPTDATE], [L_SHIPMODE]) WITH STATISTICS_ONLY = -1 GO DBCC AUTOPILOT(5, 0, 0 , 0) WITH NO_INFOMSGS DBCC AUTOPILOT(0, 8, 693577509, 7) WITH NO_INFOMSGS GO SET AUTOPILOT ON GO SELECT top 100 * FROM LINEITEM WHERE L_RECEIPTDATE = '1996-03-22' GO SET AUTOPILOT OFF GO DROP INDEX IF EXISTS [_hypothetical_index_01] on [tpch].[dbo].[LINEITEM] GO
実行をすると次のような結果を取得することができます。
XML のリンクをクリックすることで、実行プランを表示することができます。
「実際の実行プランを含める」の有効化でも、実行プランを表示することは可能です。
仮定のインデックスが使用されたクエリの実行プランとなっていますね。
AUTOPILOT が有効になっていない場合は、次にように仮定のインデックスが使用されていない状態となります。
AUTOPILOT の type は、色々とあるようなのですが、ドキュメントとして公開されているものがあるわけではないので、手探りで調査する必要がありますが、「インデックスによる処理性能の向上の効果を見る」ための手法として、このような方法があることを覚えておくと、役に立つこともあるのではないでしょうか。
[…] 参考: SQL Server の仮定のインデックスについてまとめてみる at SE の雑記 […]
週刊Railsウォッチ(20190730-2/2後編)Docker 19.03の新機能に注目、ngrokはスゴい、redis-namespaceほか
2 8月 19 at 13:59