SQL Server 2022 CTP 2.0 時点の情報となり、今後の Preview / 一般提供開始時には変更されている可能性もあります。
SQL Server 2022 では、インテリジェントなクエリ処理 の機能強化が行われ、こちら で紹介したような、いくつかの機能が追加されます。
Ignite 2021 で SQL Server 2022 のアナウンスがあったタイミングから、頻繁に詳細されている機能として Parameter Sensitivity Plan (PSP) optimization (パラメーター センシティビティ プラン 最適化) があります。
CTP 2.0 が一般公開され、手元でも検証できるようになりましたので、本ブログでも触れておきたいと思います。
Contents
従来までのパラメーター化されているクエリの問題
SQL Server のクエリ実行の基本は「コンパイル (リコンパイル) 時に指定されたパラメーターによって実行プランが生成される」です。
そのため、クエリ実行の最適化は「コンパイル時に指定されているパラメーターの値」に大きく依存することになります。
同一のパラメーターを持つクエリ (パラメーター化クエリ / ストアドプロシージャ) で、
- コンパイル時には大量のデータを取得するためのパラメーターで実行
- 以降の実行は少量のデータを取得するためのパラメーターで実行
というような実行を行う場合、「1.」で設定したパラメーターで、実行プランが最適化されるため、
- コンパイル時には大量のデータを取得するためのパラメーターで実行 -> Scan
- 以降の実行は少量のデータを取得するためのパラメーターで実行 -> Scan
というような「1.」のパラメーターで最適化された実行プランで「2.」の実行も行われます。少量のデータ取得であれば、Seek のほうが効率的なのですが、コンパイル時のパラメーターの実行プランが「2.」の実行にも引き継がれますので、「2.」でも Scan が採用されることになります。
このような「コンパイル時に指定されたパラメーターの値でクエリが最適化される」動作については、「パラメーター スニッフィング」と呼ばれます。
実行タイミングによって、パラメーターに指定された値のデータ分布に大きな差が発生し、コンパイル時のパラメーターの影響を大きく受けるようなクエリでは、
- リコンパイル (RECOMPILE) を誘発させることで、実行プランを再度生成することで回避
- OPTIMIZE FOR を使用して、パラメーターには特定の値が指定された想定で実行プランを生成
- プランガイド / クエリヒント / プランの強制を使用して実行プランを補正
というようなことが行われていたのではないでしょうか。
PSP optimization
上述のように SQL Server の実行プランは「コンパイル時に指定されているパラメーターの値」に大きく依存して生成が行われます。
SQL Server 2022 では、パラメータースニッフィングによる性能劣化に対してのアプローチとして PSP optimization という機能が追加されました。
先ほどの例であれば、
- コンパイル時には大量のデータを取得するためのパラメーターで実行 -> Scan
- 以降の実行は少量のデータを取得するためのパラメーターで実行 -> Scan
では、効率が悪いですので、
- コンパイル時には大量のデータを取得するためのパラメーターで実行 -> Scan
- 以降の実行は少量のデータを取得するためのパラメーターで実行 -> Seek
というような動作に、明示的なリコンパイルを発生させることなく、実行プランの補正が行われるのが望ましい動作となります。
これを実現する機能が PSP optimization です。
動作のための前提条件
PSP optimization を使用するためには前提条件があります。
- データベース互換性レベルが 160 になっている
- 互換性レベルが 150 以下の場合は、クエリに、「OPTION (USE HINT(‘QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160’))」を指定することでも対応はできるようです。
- データベーススコープの構成 で「PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON」になっている
最低限の条件としては、これらを満たしておく必要があります。
SQL Server 2022 を最初から使用するのであれば、これらの条件は満たされています。しかし、SQL Server 2019 以前からバージョンアップした場合 / データベースバックアップを移行した場合等は、条件を満たしていませんので、2022 に移行をした後に、互換性レベルとデータベーススコープの構成を適切な設定に変更する必要があります。
PSP optimization については、「クエリストアの有効化」は必須ではありません。クエリストアを有効にしておくことで、PSP optimization 向けの情報もクエリストア内 (sys.query_store_query_variant) に格納されるようになりあます。
この追加情報は動作を分析するのには有益な情報となりますが、動作させるための必須条件ではありません。
クエリストアの他に、拡張イベントに次のイベントが追加されていますので、動作の詳細を確認したい場合は、拡張イベントを活用することもできます。
- parameter_sensitive_plan_optimization
- parameter_sensitive_plan_optimization_skipped_reason
- select * from sys.dm_xe_map_values where name = ‘psp_skipped_reason_enum’ order by map_key でどのような理由があるかを確認できます。
- parameter_sensitive_plan_testing
動作を確認するためのサンプルデータ
動作を確認するためのサンプルデータとしては、次のようなデータを使用することができます。
DROP TABLE IF EXISTS PSP_Test
GO
CREATE TABLE PSP_Test(
C1 int identity,
C2 int,
C3 varchar(36),
C4 datetime2(3),
CONSTRAINT PK_PSP_test PRIMARY KEY CLUSTERED (C1),
INDEX NCIX_PSP_Test_C3(C3),
INDEX NCIX_PSP_Test_C3_C4(C3, C4),
INDEX NCIX_PSP_Test_C4(C4)
)
GO
SET NOCOUNT ON;
DECLARE @cnt int = 1, @id varchar(36) = NEWID(), @date datetime2(3) = '1900-01-01';
BEGIN TRAN
WHILE(@cnt <= 299999)
BEGIN
INSERT INTO PSP_Test VALUES(RAND() * 100, @id, DATEADD(hour, @cnt, @date));
SET @cnt += 1;
END
COMMIT TRAN
GO
INSERT INTO PSP_Test VALUES(RAND() * 100,NEWID(),GETDATE());
GO 10
SELECT C3, COUNT(*) FROM PSP_Test GROUP BY C3 ORDER BY COUNT(*)
GO
データの分布としては次のようになります。
特定の ID のデータについては、大量にデータが格納されていますが、それ以外の ID のデータは 1 件のみ登録されている状態となります。
従来までの動作
最初に従来までの動作を確認するため、PSP optimization を無効にしておきます。
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF GO
この状態で次のクエリを実行してみます
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE GO DECLARE @param varchar(36) = (SELECT C3 FROM PSP_Test GROUP BY C3 HAVING COUNT(*) = 299999) EXEC sp_executesql N'SELECT * from PSP_Test WHERE C3 = @p1', N'@p1 varchar(36)', @p1 = @param; GO DECLARE @param varchar(36) = (SELECT TOP 1 C3 FROM PSP_Test GROUP BY C3 HAVING COUNT(*) = 1) EXEC sp_executesql N'SELECT * from PSP_Test WHERE C3 = @p1', N'@p1 varchar(36)', @p1 = @param; GO
コンパイル時のパラメーターは「29,999 件」のデータを取得し、2 回目の実行では、「1 件」のデータを取得するクエリとなります。
クエリは 1 回目の実行に指定された値で最適化されます。
![image[6]](https://blog.engineer-memo.com/wp-content/2022/05/image6.png "image[6]")
2 回目の実行は 1 件の取得で、C3 には、インデックスが設定されているのですが、Scan のプランが再利用されますので、全件 Scan が行われます。
従来までの実行では 1 回目の実行のパラメーターの値で実行プランは最適化されているため、2 回目以降とパラメーターに指定している値の分布が大きく異なっていると、実行効率が悪いクエリが再利用されている可能性があります。
PSP optimization による最適化
それでは PSP optimization による動作の変化を確認するため、OFF にした設定を ON にします。
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON GO
その後、先ほどと同じクエリを実行します。
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE GO DECLARE @param varchar(36) = (SELECT C3 FROM PSP_Test GROUP BY C3 HAVING COUNT(*) = 299999) EXEC sp_executesql N'SELECT * from PSP_Test WHERE C3 = @p1', N'@p1 varchar(36)', @p1 = @param; GO DECLARE @param varchar(36) = (SELECT TOP 1 C3 FROM PSP_Test GROUP BY C3 HAVING COUNT(*) = 1) EXEC sp_executesql N'SELECT * from PSP_Test WHERE C3 = @p1', N'@p1 varchar(36)', @p1 = @param; GO
1 回目のクエリについては先ほどと同様で Scan が選択されています。
2 回目の実行については、Seek & Lookup が選択されています。
1 回目の実行と 2 回目の実行の間にはクエリのコンパイル / リコンパイルは発生させていませんが、パラメーターに指定した値によって、同一のクエリテキストにも関わらず、実行プランが変化しています。
実行されているクエリのテキストを確認すると次のようになっています。
SELECT * from PSP_Test WHERE C3 = @p1 option (PLAN PER VALUE(QueryVariantID = 3, predicate_range([PSP_Test].[dbo].[PSP_Test].[C3] = @p1, 100.0, 100000.0))) SELECT * from PSP_Test WHERE C3 = @p1 option (PLAN PER VALUE(QueryVariantID = 1, predicate_range([PSP_Test].[dbo].[PSP_Test].[C3] = @p1, 100.0, 100000.0)))
パラメーター化されたクエリが使用されているのですが、「OPTION 句」が指定されており、指定したパラメーターの値に応じて「QueryVariantID」が異なっていることが確認できます。
この設定を使用することで、パラメーターの値に応じて、使用する実行プランをクエリのリコンパイルを発生させることなく変化させています。(現状 = による比較については PSP optimization は動作しているようですがそれ以外の比較では動作していなさそうでした)
PSP optimization の基本的な動作になり、従来から DBA を悩ませていたパラメータースニッフィングによる実行効率の低下に対して、SQL Server 2022 でとられたアプローチとなります。