SE の雑記

SQL Server 2016 CTP 2.0 / SQL Database v12 で使用できるクエリ ストア (Query Store) の情報について軽くまとめてみたいと思います。
詳細については Monitoring Performance By Using the Query Store からの一連の情報を確認いただければと思います。

■クエリ ストアで何ができるようになるか

クエリ ストアを使用する前に現状のクエリ情報の取得機能との差を認識しておく必要があるのかなと考えています。
SQL Server 2014 までは、

• プランキャッシュ
• パフォーマンスデータコレクション

の情報を元に各種クエリの情報を取得することができました。
プランキャッシュについてはメモリ上に格納されている情報となりますので、インスタンスの再起動やキャッシュが不足するとキャッシュがクリアされますので、すべてのクエリ情報をキャッシュしておくことはできませんでした。
過去のキャッシュの情報を永続化して情報を蓄積しておきたい場合は、パフォーマンスデータコレクションを使用することで、過去の情報についても確認することができます。
クエリ ストアでも各クエリの情報を確認することができますが、クエリ ストアとこれらの機能の最大の違いは、

• クエリ単位でどのように実行の状態が変わったかを確認することができる
• 特定のデータベースをターゲットとして情報を取得することができる

ということではないでしょうか。
インデックスの追加や統計情報が更新されることでクエリの実行プランは変わっていきます。
これらの要因によって、クエリの実行プランがどのように推移したかをクエリ ストアで確認することができます。
クエリ ストアは特定のデータベースについての情報のみに特化することができますので、プランキャッシュやデータコレクションのようなインスタンス全体のクエリ情報を取得することと比較して、必要な情報のみをすることができます。
 
上記の要因により、クエリには複数の実行プランが生成される可能性があります。
クエリ ストアを使うことで、

• 特定のクエリプランの使用を強制することができる

を実現することができます。
こちらはプランガイドに近いかもしれませんが、プランガイドより簡単にプランを固定化することができます。
クエリ ストアにはこのような特徴があるのではないでしょうか。
 

■クエリ ストアの設定

SQL Server 2016 CTP 2.0 の SSMS であれば、GUI ベースで設定することができます。
初期状態では、クエリストアは無効になっていますので、データベースのプロパティから有効にする必要があります。

毎回、クエリストアの情報をディスクに書き込んでいるとオーバーヘッドが高くなりますので、定期的にディスクにフラッシュすることで、データを永続化することができます。
この設定が「データのフラッシュ間隔」となります。
クエリストアの情報は一定期間で集約した情報で格納がされますので、その集約の期間が「統計の収集間隔」となります。
基本的な設定については Configuration Options から確認するとよいかと。
SQL Server 2014 までの SSMS で有効にしたい場合はクエリベースで設定する必要がありますので、その場合は以下のようなクエリで設定を行います。

ALTER DATABASE [TEST]
SET QUERY_STORE
(DATA_FLUSH_INTERVAL_SECONDS = 300, INTERVAL_LENGTH_MINUTES = 15)

 
これで、クエリ ストアが有効になり、クエリの情報が取得されます。
クエリストアのオプションについては、ALTER DATABASE SET Options (Transact-SQL) からも確認することができます。
また、クエリストアの情報については、

ALTER DATABASE [TEST] SET QUERY_STORE CLEAR ALL

 
でクリアすることができますので、テストの際に、情報を初期化したい場合はこちらを使用するとよいかと。
 

■クエリ ストアの構成を理解する

 
クエリストアの機能を理解するためにはクエリストアのテーブルの構成を理解しておくとよいかと思います。
基本的には以下の構成となっています。

Query Store Catalog Views

Seven catalog views present information about the Query Store.

• sys.database_query_store_options (Transact-SQL)
• sys.query_context_settings (Transact-SQL)
• sys.query_store_plan (Transact-SQL)
• sys.query_store_query (Transact-SQL)
• sys.query_store_query_text (Transact-SQL)
• sys.query_store_runtime_stats (Transact-SQL)
• sys.query_store_runtime_stats_interval (Transact-SQL)

Query Store Stored Procedures

Six stored procedures configure the Query Store.

• sp_query_store_flush_db (Transact-SQL)
• sp_query_store_reset_exec_stats (Transact-SQL)
• sp_query_store_force_plan (Transact-SQL)
• sp_query_store_unforce_plan (Transact-SQL)
• sp_query_store_remove_plan (Transct-SQL)
• sp_query_store_remove_query (Transact-SQL)

クエリ ストアのオプションは sys.database_query_store_options から確認することができます。
「統計の収集間隔」の設定は sys.query_store_runtime_stats / sys.query_store_runtime_stats_interval に依存した設定になるかと思います。
収集間隔によって、sys.query_store_runtime_stats の情報の集計間隔変わってきて、どのような間隔で収集されるかは、sys.query_store_runtime_stats_interval で確認することができます。
 
クエリの情報については、sys.query_store_query_text / sys.query_store_query / sys.query_store_plan から確認できます。
# クエリ実行時のコンテキストの設定については、 sys.query_context_settings から確認ができるようです。
 
それでは、実際に情報を見ていきたいと思います。
まずはテスト用のテーブルを作成します。

SET NOCOUNT ON
IF OBJECT_ID('Table_1') IS NOT NULL
	DROP TABLE Table_1
GO
CREATE TABLE dbo.Table_1
	(
	Col1 int NOT NULL IDENTITY (1, 1),
	Col2 uniqueidentifier NULL,
	Col3 uniqueidentifier NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
PK_Table_1 PRIMARY KEY CLUSTERED
(Col1)
GO
DECLARE @cnt int = 1
BEGIN TRAN
WHILE (@cnt <= 100000)
BEGIN
	INSERT INTO Table_1 (Col2, Col3) VALUES(NEWID(), NEWID())
	SET @cnt += 1
	IF (@@TRANCOUNT % 40000 = 0)
	BEGIN
		COMMIT TRAN
		BEGIN TRAN
	END
END
IF @@TRANCOUNT > 0
	COMMIT TRAN

 
今回はこのテーブルに対して、

SELECT * FROM Table_1 WHERE Col2 = 'AF70AC8F-27E6-4E10-A754-1F3DE64AD2DA'

 
を実行した際に取得されたクエリ ストアの情報をサンプルとしてテーブルから情報を追っていきたいと思います。
クエリストアでは「クエリ ID」を用いてクエリの識別を行っています。
そのため、上記のクエリのクエリ ID を確認する必要があります。
「クエリ ID」 については、「sys.query_store_query_text」 の情報から追うことが出きます。

 
実行の結果が上記の画像となるのですが、簡易パラメーター化クエリによって、パラメーター化されているのが確認できますね。
事前に対象のクエリが判別できている場合は、

DECLARE @sql_handle varbinary(64) =
(SELECT statement_sql_handle FROM sys.fn_stmt_sql_handle_from_sql_stmt('(@1 varchar(8000))SELECT * FROM [Table_1] WHERE [Col2]=@1', NULL))
SELECT * FROM sys.query_store_query_text where statement_sql_handle = @sql_handle

というような形で、sys.fn_stmt_sql_handle_from_sql_stmt を使用することで、ステートメントを「statement_sql_handle」に変換することが可能ですので、こちらで取得することもできます。
これで該当のクエリの「query_text_id」を取得することができます。
それでは、このクエリの「クエリ ID」を取得したいので、「sys.query_store_query」から情報を取得します。
今回は query_text_id は「1」ということがわかっていますのでこれで検索をします。

これで該当のクエリの「クエリ ID」が取得できました。
最後に、「sys.query_store_plan」からクエリの実行プランを取得します。

これで実行プランまで情報を取得することができました。
クエリ ストアでは、テキストのステートメント SQL ハンドルをベースとして、クエリの実行プランの情報が格納される形になります。
それでは、先ほどのクエリにをインデックスが使用されるようにしてクエリを実行してみたいと思います。

CREATE NONCLUSTERED INDEX [NCIX_Table_1_Col2] ON [dbo].[Table_1] (Col2) WITH(STATISTICS_NORECOMPUTE = ON)
GO
SELECT * FROM Table_1 WHERE Col2 = 'AF70AC8F-27E6-4E10-A754-1F3DE64AD2DA'

 
クエリストアでは対象のクエリの情報の実行プランの変更の推移を取得することができます。
そのため、以下の画像のような情報の状態となった場合には、同一の SQL のテキストで複数の実行プランが存在した状態となります。
# 実行プランが途中で変わったっと判断することができます。

ここからは、SSMS で確認をした方がわかりやすいので「追跡したクエリ」から該当のクエリ ID の情報を確認してみます。
「クエリ ID」が「1」のクエリは「プラン ID」が「1」「4」の複数の実行プランを持つことがここから確認できますね。

今回は、インデックスを設定していますので、インデックスの設定前後で以下のようなプランの変化を確認することができます。

クエリストアでは「プランの強制」というアクションをとることができますので、複数のプランがある場合には特定のプランの使用を強制するということも可能です。
ただし、プランによってはインデックスの状態によって、強制的にそのプランを使用するということができない可能性があります。
# 上記の場合は、非クラスター化インデックスを削除してしまうと、非クラスター化インデックスを強制したクエリの実行はできませんので。
このような場合は、sys.query_store_plan の「force_failure_count」がカウントアップされるのでこちらから確認をすることができます。

 
クエリ ストアを使用することで

• キャッシュされている最新の実行状態からクエリを解析

するのではなく、

• 実行された時の実行状態からクエリを解析

というアプローチをすることができます。
クエリの実行プランは統計情報 / クエリコンパイル時のパラメーターに影響を受けますので、キャッシュされている状態ではなく、問題があった際にどのような情報で実行されたかが重要となってきます。
このような解決アプローチをするときにはクエリ ストアはかなり強力な機能となるのではないでしょうか。