【SQL server】パラメータスニッフィングによる実行プランのパフォーマンス低下
SQL server では、ストアドプロシージャ実行時に実行プランをハードパースする場合に、受け取ったパラメータに最適化する形で実行プランが作成されます。
パラメータスニッフィングにより不適切なパラメータで実行プランがハードパースされると効率の悪い実行プランで実行されてしまい、急激なパフォーマンス低下を発生させることがあります。
※Oracleでいうバインドピーク有効時の不適切なパラメータによる実行計画の不安定化と同じ仕組み？
SQL server が実行プランをハードパースする条件は、以下となります。
・クエリ(ALTER TABLE および ALTER VIEW)によって参照されるテーブルまたはビューに変更を加えた場合
・実行プランで使用されるインデックスに加えた場合
・UPDATE STATISTICS などのステートメントを使用して明示的に生成した実行プラン、または自動的に生成された実行プランによって使用される統計を更新した場合
・実行プランで使用されるインデックスを削除した場合
・sp_recompile を明示的に呼び出した場合
・クエリによって参照されるテーブルを変更する他のユーザが、INSERT ステートメントまたはDELETE ステートメントを使用して大量の変更をキーに加えた場合
・トリガーを含むテーブルで、inserted テーブルまたは deleted テーブルの行数が大幅に増加する場合
・WITH RECOMPILE オプションを使用してストアドプロシージャを使用して実行する場合
・ハードパースされた実行プランがキャッシュアウトされた場合
対応策としてはストアドプロシージャのクエリにヒント句として、以下のいずれかを追加します。
・RECOMPILEヒント句 ⇒ 実行の度にハードパースする
・OPTMIZE FORヒント句(プラン) ⇒ 実行する実行プランを強制する
・OPTMIZE FORヒント句(UNKNOWN) ⇒ 実行する際に入力パラメータによる最適化ではなく、統計情報を元にコンパイルを実行する
・クエリストアによるプラン強制(SQL server 2016から)(※)
※クエリ自体の修正は必要なく、GUIで設定できますが、こちらの場合はクエリストアに強制したい最適化パラメータの実行プランが格納されている必要があります。
基本的には、推定の実行プランのアクセスパスに基づいて実行されるため、本来であればIndex Seek箇所がIndex Scan、もしくはその反対となりうるカーディナリティのカラムについては注意する必要があると思います。
以下が最適であるシナリオにおいて、パラメータスニッフィングにより不適切な実行プランとなってしまっている例です。
パラメータ「1」 ⇒ Clusterd Index Scan
パラメータ「2」 ⇒ Index Seek
※以下例は、準備不足のためストアドプロシージャに対し、CONVERT_IMPLICIT(暗黙型変換)が発生しているため、インデックスがうまくきいていないことは無視してください…
INT型のデータ型に対し、ストアドプロシージャの変数をVARCHAR型で作成してしまっていることが原因です…
◆パラメータ「2」で最適化された実行プランに対し、パラメータ「2」で実行した場合の、推定の実行プラン
◆ パラメータ「2」で最適化された実行プランに対し、パラメータ「2」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、1/1000000件を取得する処理を効率的にIndex Seekで取得しています。
◆パラメータ「2」で最適化された実行プランに対し、パラメータ「1」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、999998/1000000件を取得する処理を1/1000000件を取得する処理と同様にIndex Seekで取得しています。
◆パラメータ「1」で最適化された実行プランに対し、パラメータ「1」で実行した場合の、推定の実行プラン
◆パラメータ「1」で最適化された実行プランに対し、パラメータ「1」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、999998/1000000件を取得する処理をClusterd Index Scanで取得しています。
◆パラメータ「1」で最適化された実行プランに対し、パラメータ「2」で実行した場合の、実際の実行プラン
推定の実行プランと同じアクセスパスとなり、1/1000000件を取得する処理を999998/1000000件を取得する処理と同様にClusterd Index Scanで取得しています。
/***************************************************************************************************/
以下は検証に使用したクエリになります。
-- データベース作成
CREATE DATABASE [Param_testDB]
GO
-- これから作成するテーブルの存在確認
IF OBJECT_ID(N'Param_testDB..Param_test' , N'U') IS NOT NULL DROP TABLE Param_test;
USE [Param_testDB]
GO
-- 検証用テーブル作成
CREATE TABLE [Param_test](
[col1] INT IDENTITY PRIMARY KEY ,
[col2] INT ,
[col3] INT )
GO
-- 検証ストアドプロシージャ作成
CREATE PROCEDURE [sp_param]
@param_test VARCHAR(2)
AS
SET NOCOUNT ON;
SELECT * FROM [Param_testDB].[dbo].[Param_test]
WHERE [col3] = @param_test
GO
-- 100万件のデータを追加
-- col3 にはすべて「1」を INSERT
SET NOCOUNT ON
DECLARE @i int = 1
WHILE @i <= 1000000
BEGIN
INSERT INTO [Param_testDB].[dbo].[Param_test] VALUES(@i, 1)
SET @i += 1
END
SET NOCOUNT OFF
-- col3 列のデータのうち「2」「3」に1件ずつ変換
UPDATE [Param_testDB].[dbo].[Param_test] SET [col3] = 2 WHERE [col1] = 1
UPDATE [Param_testDB].[dbo].[Param_test] SET [col3] = 3 WHERE [col1] = 2
-- データの確認
SELECT * FROM [Param_testDB].[dbo].[Param_test]
-- データの個数の確認
SELECT
]col3],
COUNT(*) AS col_cnt
FROM [Param_testDB].[dbo].[Param_test]
GROUP BY col3
ORDER BY col3
-- col3 列にインデックスを作成
-- DROP INDEX idx_col3 ON Param_test
CREATE INDEX [idx_col3] ON [Param_testDB].[dbo].[Param_test](col3)
-- 最適化パラメータ「2」で実行プランを作成
EXEC sp_param 2;
-- 推定の実行プランを確認
SET SHOWPLAN_XML ON;
EXEC sp_param 2;
SET SHOWPLAN_XML OFF;
-- 実際の実行プランを確認
SET STATISTICS XML ON;
EXEC sp_param 2;
SET STATISTICS XML OFF;
-- 1 に変更
SET STATISTICS XML ON;
EXEC sp_param 1;
SET STATISTICS XML OFF;
-- 3 に変更
SET STATISTICS XML ON;
EXEC sp_param 3;
SET STATISTICS XML OFF;
-- =============================================
-- 実行プランを再作成するために プロシージャ キャッシュのクリア
-- =============================================
DBCC FREEPROCCACHE
-- 最適化パラメータ「1」で実行プランを再作成
EXEC sp_param 1;
-- 推定の実行プランを確認
SET SHOWPLAN_XML ON;
EXEC sp_param 1;
SET SHOWPLAN_XML OFF;
-- 実際の実行プランを確認
SET STATISTICS XML ON;
EXEC sp_param 1;
SET STATISTICS XML OFF;
-- 2 に変更
SET STATISTICS XML ON;
EXEC sp_param 2;
SET STATISTICS XML OFF;
-- 3 に変更
SET STATISTICS XML ON;
EXEC sp_param 3;
SET STATISTICS XML OFF;
