試したいけど怖くてインデックスが作れないときに仮のインデックスを作ってクエリの動作をお試ししてみる

　『SQL の実行時間が遅く、調査したらインデックスが不足していた、運用環境にインデックスを追加せねば！けど...インデックスを追加したために他のクエリに影響がでるのが怖い！』ということは、SQL Server の開発に携わっていれば誰しも経験があると思います。(もちろん、私もその一人。) その恐怖から "少しだけ" 解放してくれるのが、今回紹介する仮想インデックスです。まずは sys.indexes の内容を参照するため、下記のクエリを SQL Server Management Studio から実行してみましょう。

SELECT 
    object_id,
    name,
    index_id,
    type_desc,
    fill_factor,
    is_hypothetical,
    allow_page_locks,
    allow_row_locks
FROM
    sys.indexes

　上図の青枠部分に 『is_hypothetical』という列があります。sys.indexes の is_hypothetical の説明を見てみると

1 = インデックスは仮想的であり、データへのアクセス パスとして直接使用することはできません。 仮想インデックスは、列レベルの統計を保持しています。
0 = インデックスは仮想的ではありません。

　となっています。is_hypothetical = 1 となれば、仮想インデックスという扱いです。どのような性質をもっているか試してみましょう。AdventureWorks2016CTP3 の Sales.OrderTracking テーブルに作成します。下記の DDL 文を SQL Server Management Studio で実行してみましょう。

USE AdventureWorks2016CTP3

CREATE NONCLUSTERED INDEX hypothetical_index ON Sales.OrderTracking
(
	EventDateTime ASC
) WITH STATISTICS_ONLY = -1

　仮想インデックスが作成されたか確認をします。下記のクエリを SQL Server Management Studio から実行してみましょう。先ほど作成したインデックスが、is_hypothetical = 1 となっています。

SELECT 
    object_id,
    name,
    index_id,
    type_desc,
    fill_factor,
    is_hypothetical,
    allow_page_locks,
    allow_row_locks
FROM
    sys.indexes
WHERE
    OBJECT_NAME(object_id) = 'OrderTracking'

　インデックスも作成したので、早速下記のクエリを実行してみましょう。あれ？インデックスが効いてません。

SELECT 
    *
FROM
    Sales.OrderTracking
WHERE 
    EventDateTime = '2013-10-11 02:00:00.0000000'

　仮想インデックスを有効にするには、DBCC AUTOPILOT と SET AUTOPILOT ON を実行する必要があります。DBCC AUTOPILOT には、DBID、テーブルの object_id、インデックスの ID が必要です。下記のクエリを実行して、必要な情報を抽出しましょう。

SELECT 
    db_id = DB_ID(),
    object_id,
    index_id
FROM
    sys.indexes
WHERE
    OBJECT_NAME(object_id) = 'OrderTracking' AND
    is_hypothetical  = 1

　以下のクエリを SQL Server Management Studio で実行しましょう。作成した仮想インデックスを使用していることが判断できます。

DBCC AUTOPILOT(0, 10, 1997250170, 5)
GO
SET AUTOPILOT ON
GO
SELECT 
    *
FROM
    Sales.OrderTracking
WHERE 
    EventDateTime = '2013-10-11 02:00:00.0000000'
GO
SET AUTOPILOT OFF
GO

　さて、作成した仮想インデックスを細かくみていきましょう。まず、SQL Server Management Studio 上からは見ることができません。

　参照するには、sp_helpindex を使用する必要があります。下記のストアドを SQL Server Management Studio で実行しましょう。

sp_helpindex 'Sales.OrderTracking'

　統計情報を確認してみましょう。統計については下記の記事を参照してください。
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com

　下記のコマンドを SQL Server Management Studio で実行しましょう。

DBCC SHOW_STATISTICS ('Sales.OrderTracking', hypothetical_index)

　インデックスの配置状況を確認してみましょう。インデックスのデータページへの配置状況の確認方法に関しては下記の記事を参考にしてください。
ryuchan.hatenablog.com
　
　下記のクエリを SQL Server Management Studio で実行しましょう。(今回作成した仮想インデックスの id は 5 です。)実行しても何もヒットしませんね。

SELECT 
    [ページID] = allocated_page_page_id,
    [ページタイプ] = page_type,
    [ページタイプ詳細] = page_type_desc,
    [ページレベル(Index_Level)] = page_level,
    [次ページのページID] = next_page_page_id,
    [前ページのページID] = previous_page_page_id
FROM 
    sys.dm_db_database_page_allocations( DB_ID(), 
                                         OBJECT_ID('Sales.OrderTracking'), 
                                         NULL, 
                                         NULL, 
                                         'DETAILED') 
WHERE
    index_id =5

　仮想インデックスは実データを持たず、統計情報などのデータだけを持った仮想的なインデックスなのですね。仮想インデックスを使用することで運用系でテストを実施し、テストが問題なければ、実インデックスを作成するというフローを考えても良いかもしれません。

こちらの方にも興味が...

ＶＲビジネスの衝撃　「仮想世界」が巨大マネーを生む (ＮＨＫ出版新書)