【SQL Server】OPTIMIZE FOR UNKNOWNを使用した場合の統計について

ストアドプロシージャのパラメータスニッフィングによるパフォーマンス低下対策としてRECOPILEやOPTIMIZE FOR UNKNOWNを使うことが多いかと思いますが、OPTIMIZE FOR UNKNOWNで使われる統計がどのように算出されているのか検証してみました。

パラメータスニッフィングについては以下をご参照ください。
http://blog.hatena.ne.jp/utiowa/memorandom-nishi.hatenablog.jp/entry/2016/06/15/013111

MSDNにはOPTIMIZE FOR UNKNOWNについて次のような説明がされています。

OPTIMIZE FOR UNKNOWN
クエリ オプティマイザーでクエリをコンパイルおよび最適化するときに、強制パラメーター化によって作成されたパラメーターも含め、すべてのローカル変数に対して初期値の代わりに統計データを使用することを指定します。
場合 OPTIMIZE FOR @variable_name = literal_constant OPTIMIZE FOR UNKNOWN、同じクエリ ヒントで使用されると、クエリ オプティマイザーを使用して、 literal_constant特定の値および"不明"の残りの変数の値に指定されています。 これらの値はクエリを最適化する過程でのみ使用され、クエリの実行時には使用されません。

クエリ ヒント (Transact-SQL)

まずは検証用にテーブルとデータ、インデックスを用意します。

USE Param_testDB
GO

/***********************************/
-- テストテーブル作成
/***********************************/

DROP TABLE IF EXISTS [dbo].[STATS_ROWTEST]
GO

CREATE TABLE [dbo].[STATS_ROWTEST](
	[col1] [int] IDENTITY(1,1) NOT NULL,
	[col2] [int] NULL,
	[col3] [int] NULL
PRIMARY KEY CLUSTERED 
(
	[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

/***********************************/
-- テストデータ作成
/***********************************/

SET NOCOUNT ON
DECLARE @i int = 1
WHILE @i <= 10000000
	BEGIN
		INSERT INTO [dbo].[STATS_ROWTEST]
		VALUES ((CONVERT(INT, RAND()*10)),(CONVERT(INT, RAND()*100)))
			SET @i += 1
	END
SET NOCOUNT OFF
GO

-- =========================
-- col3を偏りを発生させるため、一部の値で更新
-- =========================
UPDATE [dbo].[STATS_ROWTEST]
SET col3 = 50
WHERE col3 
	BETWEEN	0
	　　AND 20


/***********************************/
-- index作成
/***********************************/

CREATE NONCLUSTERED INDEX [NonClusteredIndex_col3] ON [dbo].[STATS_ROWTEST]
(
	[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

続いてインデックスの統計情報を更新し、統計情報を確認します。

/***********************************/
-- 統計情報を更新
/***********************************/

UPDATE STATISTICS [dbo].[STATS_ROWTEST] [NonClusteredIndex_col3]
GO

/***********************************/
-- 統計情報が更新されていることを確認
/***********************************/

DBCC SHOW_STATISTICS ([STATS_ROWTEST], [NonClusteredIndex_col3]) WITH NO_INFOMSGS
GO

通常のストアドプロシージャとOPTIMIZE FOR UNKNOWNを使用したストアドプロシージャを作成します。

/***********************************/
-- UNKNOWNなし
/***********************************/
CREATE PROCEDURE usp_select_test
	@Param1 int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT col3 FROM [dbo].[STATS_ROWTEST]
	WHERE col3 = @Param1
END
GO


/***********************************/
-- UNKNOWNあり
/***********************************/

CREATE PROCEDURE usp_select_test_unknown
	@Param1 int
AS
BEGIN
	SET NOCOUNT ON;
	SELECT col3 FROM [dbo].[STATS_ROWTEST]
	WHERE col3 = @Param1
	OPTION (OPTIMIZE FOR UNKNOWN)
END
GO

OPTIMIZE FOR UNKNOWNを使用していない場合

まずはUNKNOWNが無い場合のクエリを実行した場合の予測行数は次のとおりとなります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

この場合、パラメータスニッフィングが有効となっており実行プラン作成時のパラメータで最適化されているため、初回実行時のcol3=50の予測行数でプランが作成されていることが分かります。

実行プランを削除してからパラメータを変更して実行プランを確認するとプラン作成時のパラメータに応じて予測行数が変わっていることがわかります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test] 
   @Param1
GO

OPTIMIZE FOR UNKNOWNを使用した場合

UNKNOWNが有る場合のクエリを実行した場合の予測行数は次のとおりとなります。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

先ほどのパラメータを使用しているにも関わらず、予測行数が異なっています。先ほど同様に実行プランを削除してパラメータを変更して実行してみます。

DBCC FREEPROCCACHE
GO

DECLARE	@Param1 int
SET	@Param1 = 60
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

DECLARE	@Param1 int
SET	@Param1 = 50
EXECUTE [dbo].[usp_select_test_unknown] 
   @Param1
GO

UNKNOWNが無い場合とは異なり、初回実行時のパラメータに左右されることなく一定の予測行数となっていることがわかるかと思います。

ヒストグラムを確認

通常時の予測行数の算出は以前の記事に記載がありますので、詳しくはそちら(【SQL Server】統計情報のヒストグラムと実行プランの予測行数 - 小物SEのメモ帳)を見ていただければわかるかと思いますが、UNKNOWN時の場合は以下のように全てのユニークな値の分布（All density）から全体行（Rows）に対し何％含まれているか計算して予測行数としているようです。

予測行数 = All density(1 / distinct values) * Rows

/***********************************/
-- 統計情報を確認
/***********************************/

DBCC SHOW_STATISTICS ([STATS_ROWTEST], [NonClusteredIndex_col3]) WITH NO_INFOMSGS
GO

先ほど確認した実行プランの予測行数と一致することがわかります。

よってOPTIMIZE FOR UNKNOWNを使用する場合、統計情報から各データのユニークな値の分布と全体行数を考慮して平均的な行数を予測した実行プランを作るといえるかと思います。
ただし、値毎の出現頻度までは考慮していないように見えます。今回でいうとcol3=50は件数が多いはずですが、col3のユニークな値と全体行数からしか予測行数を算出していないため。念のためヒストグラムを可視化しておくと以下の通りとなります。

特定のパラメータの実行頻度が高い場合は、そのパラメータで最適になるようヒント句でパラメータを指定することもありかと思いますが、平均的な実行プランで処理させたい場合はOPTIMIZE FOR UNKNOWNを使う検討をするのもありかと思います。
#その分パラメータによっては最速なプランは作成されないためトレードオフではありますが。