読者です 読者をやめる 読者になる 読者になる

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

 『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

f:id:koogucc11:20160512090218p:plain

 上図の青枠部分に 『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

f:id:koogucc11:20160514202005p:plain

 仮想インデックスが作成されたか確認をします。下記のクエリを 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'

f:id:koogucc11:20160514210708p:plain

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

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

f:id:koogucc11:20160514210909p:plain

 仮想インデックスを有効にするには、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

f:id:koogucc11:20160514211110p:plain

 以下のクエリを 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

f:id:koogucc11:20160514211751p:plain

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

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

sp_helpindex 'Sales.OrderTracking'

f:id:koogucc11:20160514212659p:plain

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

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

DBCC SHOW_STATISTICS ('Sales.OrderTracking', hypothetical_index)

f:id:koogucc11:20160514212844p:plain


 インデックスの配置状況を確認してみましょう。インデックスのデータページへの配置状況の確認方法に関しては下記の記事を参考にしてください。
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

f:id:koogucc11:20160514213615p:plain

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

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