投稿日：2024/05/30

はじめに

　前回のブログでは、クエリストアの基本と概要について詳しく説明しました。
　今回はSQL Server自習書を参考に、想定シナリオと実際の操作画面を交えて、クエリストアの使い方を具体的にご紹介します。

想定シナリオ

　お客様からデータベース[work]のテーブル[QueryStore_test]に対して、検索スピードが遅いという報告を受けました。

　この問題を解決するために、SQL Serverのクエリストアを活用して、クエリのパフォーマンス分析と対応を依頼されました。

準備

　それでは早速クエリストアを有効化してみましょう。

USE [work]
GO
ALTER DATABASE [work] SET QUERY_STORE = ON;

　これでworkデータベースのクエリストアが有効化されました。

　次にクエリストアの状態を確認します。

SELECT
 DB_NAME() as dbname
,desired_state_desc
,actual_state_desc
,flush_interval_seconds
,max_storage_size_mb
,size_based_cleanup_mode
,stale_query_threshold_days
,current_storage_size_mb
FROM sys.database_query_store_options;

　desired_state_desc、actual_state_descがREAD_WRITEになっていれば有効化されています。
　なお、以下に各カラムで得られる情報についての解説を記載します。
　■クエリストア有効化前

　■クエリストア有効化後

　各カラムの詳細情報はsys.database_query_store_optionsを参照ください。

　次にクエリストア検証用のテーブルを作成します。

CREATE TABLE QueryStore_test
( col1 int IDENTITY PRIMARY KEY
 ,col2 int
 ,col3 int )

　ループ処理にて1万件のテストデータを挿入していきます。

DECLARE @i int = 1
WHILE @i <= 10000
BEGIN
 INSERT INTO QueryStore_test VALUES(@i, 1)
 SET @i += 1
END

　現在のテーブルのデータは col3 の値が1のみです。

SELECT 
 col3
,COUNT(*) as cnt 
FROM QueryStore_test
GROUP BY col3

　PKカラムのcol1が 9998, 9999 の col3の値をUPDATEします。

UPDATE QueryStore_test SET col3 = 2 WHERE col1 = 9998
UPDATE QueryStore_test SET col3 = 3 WHERE col1 = 9999

　UPDATE後のテーブルのデータに 2,3 が増えましたね。

SELECT 
 col3
,COUNT(*) as cnt
FROM QueryStore_test
GROUP BY col3

　最後にインデックスを作成します。

CREATE INDEX idx_col3 ON QueryStore_test(col3)

対応

　それではQueryStore_testに対してパラメータクエリでSELECT文を実行していきます。
　クエリ実行後に実行計画を確認するために、「実際の実行プランを含める」をONにします。

　まずは、変数p1に2を代入した結果を先に実行します。

EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 2

 
QueryStore_testのcol3に格納されているデータに 2 は1件しかないので、Index Seekは適切な実行計画ですね。

次に下記を実行してみるとどうなるでしょうか。

EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 1

9998件に対しての検索にIndex Seekが採用されています。

全体が10000件なので、Index Scanの方が効率は良さそうですが、不適切な実行計画が使用されました。

メモリ上にキャッシュされた実行計画をクリアし、次は@p1 = 1を先に実行してみましょう。

/*** キャッシュクリアを実施 ***/
DBCC FREEPROCCACHE
/************************/
EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 1

今度はIndex Scanが採用されており、適切な実行計画がされました。

しかし、他の値でも同様にIndex Scanの実行計画が使用されます。

EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 2
EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 3

　この事象はSQL Serverとしては正常な動きとなります。
　パラメータークエリの場合、一度実行プランが選択されるとパラメーターに代入される値に関係なく、同じ実行プランが選択される動作になるからです。

　今回はIndex Scanでも@p = 2や3の実行速度に大きな影響はなく、頻繁に実行される環境でもないといった理由があると仮定し、Index Scanの実行計画に固定する対応を実施することにします。

　SSMSから クエリストア > リソースを消費するクエリの上位 から、「リソースを消費するクエリの上位の表示」をクリックします。

画面右側にはクエリidが表示されており、173は今回の検証で使用しているクエリとなります。

画面右側にはプランの概要が表示され、複数の実行計画がある場合はプランIDを複数確認することができます。
プラン3はIndex Scanの実行計画のようです。
プラン2も見てみると、Index Seekの実行計画が表示されます。

画面右下にプランの強制というボタンがあるので、今回はIndex Scanの実行計画を固定します。

それでは本当に実行計画が固定されているのか確認してみましょう。
初回実行のパラメータを2とし、先程はIndex Seekの実行計画となっていましたが結果はどうなるでしょうか。

/*** キャッシュクリアを実施 ***/
DBCC FREEPROCCACHE
/************************/
EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 2
EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 1
EXEC sp_executesql N'SELECT * FROM QueryStore_test WHERE col3 = @p1', N'@p1 int', @p1 = 3

想定通り、Index Scanの実行計画が固定されており、Index Seekは使用されていません。

まとめ

今回のブログでは、クエリストアの有効化から、想定されたシナリオに基づくクエリの実行、実行計画の分析、そして最適化までの一連のステップを詳細に検証しました。

SQL Serverのパフォーマンスに関する課題に直面している方は、ぜひ弊社にご相談ください！