SE の雑記

SQL Server では フィルター選択されたインデックス (フィルター化インデックス) というインデックスを作成することができます。

このインデックスはインデックスの作成を行う際に、以下のクエリのように、インデックス作成時に WHERE 句を指定することで、特定の範囲のデータのみが含まれたインデックスを作成することができます。

CREATE NONCLUSTERED INDEX [NCIX_accesslog2_01] ON [frontdoor].[accesslog2]
(
	[time] ASC
)
WHERE ([time]>='2020-01-01' AND [time]<'2021-01-01')

フィルター選択されたインデックスは、特定のデータ範囲のみがインデックスに含まれますので次のようなメリットがあります。

• インデックスで使用されるストレージサイズの削減
• フィルター外のデータを追加 / 変更する場合のインデックス更新コストの削減
• 該当データ範囲で Index Scan が発生した場合の、Scan コストの削減

特定のデータ範囲の検索向けに作成するインデックスとして便利な機能となるのですが、作成したインデックスが使用されるかどうかについては考慮が必要な点がありますので、本投稿で触れておきたいと思います。

フィルター選択されたインデックスの詳細な情報については次のドキュメントが参考となります。

• フィルター選択されたインデックス
• フィルター選択されたインデックスの作成
• フィルター選択されたインデックスのデザイン ガイドライン

フィルター選択されたインデックスが使用されるかについて

本投稿では次のようなパターンでクエリを実行しています。

-- 1-1. アドホッククエリとして実行
SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= '2020-04-01' AND time < '2020-05-01'
GO

-- 1-2. アドホッククエリとして実行 (RECOMPILE)
SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= '2020-04-01' AND time < '2020-05-01'
OPTION(RECOMPILE)
GO

-- 2-1. パラメーター化クエリとして実行
EXEC sp_executesql N'SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= @p1 AND time < @p2', N'@p1 datetime2, @p2 datetime2', @p1 = '2020-04-01', @p2 = '2020-05-01'
GO

-- 2-2. パラメーター化クエリとして実行 (RECOMPILE)
EXEC sp_executesql N'SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= @p1 AND time < @p2 OPTION(RECOMPILE)', N'@p1 datetime2, @p2 datetime2', @p1 = '2020-04-01', @p2 = '2020-05-01'
GO


-- 3. ストアドプロシージャからの実行
CREATE OR ALTER PROCEDURE usp_filteredindex_test
	@p1 datetime2, @p2 datetime2
AS
BEGIN
	-- 3-1. パラメーターを述語に指定
	SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= @p1 AND time < @p2;

	-- 3-2. アドホッククエリとして実行
	SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time>= '2020-04-01' AND time < '2020-05-01';
END
GO
EXEC usp_filteredindex_test @p1 = '2020-04-01', @p2 = '2020-05-01' 
GO

このようなクエリを実行した場合に、どの実行方法がフィルター選択されたインデックスが使用されるかについて確認をしてみます。

フィルター選択されたインデックスが使用されるパターン

上述のクエリの実行結果が以下となります。

フィルター選択されたインデックスにより Index Seek が実行されていたのは次のケースとなります。

• 1-2. アドホッククエリとして実行 (RECOMPILE)
• 2-2. パラメーター化クエリとして実行 (RECOMPILE)
• 3-2. アドホッククエリとして実行

実行された SQL のテキストは類似の内容となっていますがすべてのパターンでフィルター選択されたインデックスは使用されておらず、期待したインデックスが使用されているのは一部の実行方法に限定されています。

フィルター選択されなかったケースでは、実行プランに「UnmatchedIndexes」の警告が出力されており、適用可能なインデックスが無かった状態となっています。

一部の実行方法でフィルター選択されたインデックスが使用されなかった理由

一部の実行方法ではフィルター選択されたインデックスが使用されませんでした。

SQL Server は実行時にクエリをコンパイルして実行プランを生成し、以降、同様のクエリが実行された場合はコンパイルを再度発生させることなく、キャッシュされた実行プランの再利用を行うことで実行効率を向上させています。

パラメーター化されたクエリでは、「コンパイル時に指定されたパラメーター」を基に実行プランの生成を行いますが、コンパイル以降に指定されたパラメーターに対しても適用できる実行プランを生成しておく必要があります。

コンパイル時に指定されているパラメーターがフィルター選択されたインデックスが使用できるパラメーターだったとしても、以降の実行ではフィルター外の値がパラメーターとして指定される可能性があります。

そのため、パラメーターがフィルター選択されたインデックスの対象となるデータ範囲を示していたとしても、以降のクエリで再利用できるプランとしておく必要があるため、フィルター選択されたインデックスを使用しない実行プランが使用されています。

以下の 2 パターンについては、明示的にパラメーター化を行っているので、この挙動になるのはわかりやすいかと思います。

-- 2-1. パラメーター化クエリとして実行
EXEC sp_executesql N'SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= @p1 AND time < @p2', N'@p1 datetime2, @p2 datetime2', @p1 = '2020-04-01', @p2 = '2020-05-01'
GO

-- 3-1. パラメーターを述語に指定
SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= @p1 AND time < @p2;

簡易パラメーター化による自動的なパラメーター化

「2-1.」「3-1.」については、明示的にパラメーター化を行っているため、パラメーター化により汎用的な実行プランを生成する必要があったために、フィルター選択されたインデックスが使用されていないことはわかりやすいかと思います。

-- 1-1. アドホッククエリとして実行 SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= '2020-04-01' AND time < '2020-05-01' GO

「1-1.」のクエリが上記となりますが、このクエリはアドホッククエリとして実行していますが、フィルター選択されたインデックスが使用されませんでした。

パラメーターに依存するプランの最適化 に記載されていますが、SQL Server では 簡易パラメーター化? (旧: 自動パラメーター化) という機能があり、明示的にパラメーター化していないクエリについても、SQL Server が自動的にパラメーター化をし再利用が可能な形で実行プランの生成を行うケースがあります。

先ほど記載したアドホッククエリは次のように簡易パラメーター化されて実行プランが生成されています。

SELECT COUNT(*) FROM frontdoor.accesslog2 WHERE time >= '2020-04-01' AND time < '2020-05-01'
↓簡易パラメーター化
select COUNT ( * ) from frontdoor . accesslog2 where time > = @0 and time < @1

このように自動的にパラメーター化が行われた場合、アドホッククエリとして実行をしていてもパラメータ化クエリとして実行プランが生成されるため、フィルター選択されたインデックス使用されません。

リコンパイルオプションによるパラメーターに設定される値の固定化

「3-2.」については、明示的なリコンパイルオプションを指定しなくても、パラメーター化されずアドホッククエリとして実行されていたので手を加えませんでした。

「1-2.」「2-2.」に関しては、「1-1.」「2-1.」と類似のクエリとなっていますが「OPTION (RECOMPILE)」を追加しており、これにより、フィルター選択されたインデックスが使用される実行プランとなっています。

「OPTION (RECOMPILE)」を指定してクエリを実行した場合、クエリは毎回リコンパイルされるため、実行プランのキャッシュは行われません。

指定されるパラメーターによって大きく実行効率が変化してしまうため、CPU 負荷は上昇してしまいますが、毎回クエリのコンパイルを誘発させることで、コンパイル時のパラメーターに依存することなく実行プランを生成させることができます。

それ以外にオプションを設定する際の効果として、クエリがキャッシュされなくなることで次のような効果を期待することができます。

1. アドホッククエリの簡易パラメーター化を抑制
2. パラメーターに指定した値が展開された状態で実行プランを生成

「1.」についての上述の実行プランでも確認できますが、リコンパイルオプションを指定することで簡易パラメーター化が行われず、アドホッククエリがそのままの状態で実行されています。

「2.」についても「1.」と同様の効果を期待したものとなります。

以下はリコンパイルオプションを指定したパラメーター化クエリの実行となり、クエリテキストもパラメータ化された状態となっています。

しかし、実際の検索の条件を確認してみると、スカラー操作として検索が実行されています。左がリコンパイルオプションを指定せずに実行したパラメーター化クエリ / 右がリコンパイルオプションを指定して実行したパラメータ化クエリとなります。

リコンパイルオプションを指定した場合は、パラメーターを使用した実行プランではなく、パラメーターに指定した値を使用して述語が生成されていることが確認でき、これによりフィルター選択されたインデックスが利用可能となっています。

このような挙動を期待してリコンパイルオプションを指定することがあります。

まとめ

フィルター選択されたインデックスはインデックスサイズや更新コストの削減として、特定範囲の検索を行う場合に効果が期待できます。

しかし、クエリの実行プランは「コンパイル発生時に指定されたパラメーターを使用して実行プランを生成 / キャッシュし、以降パラメーターに異なる値が指定されてもキャッシュされた実行プランを再利用できる」というのが基本的な動作となります。

そのため、汎用的な実行プランを生成するため、パラメーターでフィルター選択されたインデックスを期待した検索を実施しても期待したインデックスが使用されないことがあります。

通常のインデックス追加でも同様ですが、「追加したインデックスが想定したアプリケーションからのクエリ実行で使用されるか」が重要な考慮となりますので、フィルター選択されたインデックスを追加した場合でも、実際に使用されるかどうかを確認するのが重要となります。