Query Store Hints Preview でアナウンスがありましたが、SQL Database でクエリストアヒントがプレビュー機能として使用できるようになりました。
今まで、クエリストアを使用した実行プランの補正としては、プランの強制という機能があり、同一のクエリで複数の実行プランがある場合、特定のプランを使用するようにプランを強制することができました。
この機能を実行プランの補正に使うことができたのですが、プランの強制は「強制したい実行プランの情報がクエリストアに格納されている」必要があり、使用したい実行プランの情報がクエリストア上に格納されている必要がありました。
今回使用できるようになった「クエリストアヒント」については、クエリストアに格納されている実行プランのクエリについて「クエリヒントを適用することができる」機能となっており、「強制したいプランがクエリストアに存在していない」状態でも、プランの補正を柔軟に実施することができます。
類似の機能としては「プランガイド」を使用して、クエリヒントをアタッチすることができましたが、クエリストアヒントはプランガイドより容易にクエリヒントを適用することが可能です。(プランガイド、設定するのに少し手間がかかるんですよね…。)
公式のドキュメントは次の内容を確認してください。
Contents
クエリストアヒントの利用
実際にクエリストアヒントを使用してみます。
今回は当ブログのアクセスログを格納したテーブルのデータで動作確認をしてみます。
最初に以下のクエリを実行して、検索に使用する「securityProtocol」のデータの分布を取得してみます。
SELECT
s.name,
hist.range_high_key,
hist.range_rows,
hist.equal_rows
FROM
sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.[object_id], s.stats_id) AS hist
WHERE
s.object_id = OBJECT_ID('frontdoor.accesslog')
AND s.name = 'NICX_accesslog_securityProtocol'
このテーブルのでーたぷんぷは次のようになっており、TLS が指定されているものについては大半が「TSL 1.2」であることが確認できます。
それではデータを取得してみたいと思います。
EXEC sp_executesql
N'SELECT TOP 100 * FROM frontdoor.accesslog WHERE securityProtocol = @p1 ORDER BY [httpMethod] ASC',
N'@p1 varchar(100)', @p1 = 'TLS 1.2'
GO
「securityProtocol」には非クラスター化インデックスが作成してあり、「TLS 1.2」のデータの取得を行っています。
このクエリを実行した場合、次のような実行プランが生成されます。
登録されているデータの大半は「TSL 1.2」ですので Scan が行われていますね。
それでは、次にパラメーターを変更して「TLS 1.0」の情報を取得してみます。
EXEC sp_executesql
N'SELECT TOP 100 * FROM frontdoor.accesslog WHERE securityProtocol = @p1 ORDER BY [httpMethod] ASC',
N'@p1 varchar(100)', @p1 = 'TLS 1.0'
GO
先ほどのクエリで「Clustered Index Scan」が使用され、実行プランがキャッシュされたため、データ件数が少ない「TLS 1.0」の検索でも Scan が行われています。
従来までのクエリストア活用する方法では、このような状況になった場合、Scan 以外の実行プランが使用され、クエリストアに情報が格納されれば、Scan 以外の実行プランを今日背うすることができました。
つまり、自分が使用したい実行プランが生成されるのを待つ必要がありました。
クエリストアヒントを使用すると次のようにクエリを補正することができます。
クエリストアヒントでは、「query_id」を使用する必要がありますので、該当のクエリの情報をクエリストアから取得します。
SELECT
q.query_id,
qt.query_text_id,
q.query_hash,
p.query_plan_hash,
q.last_execution_time AT TIME ZONE 'Tokyo Standard Time' AS query_last_execution_time,
p.last_execution_time AT TIME ZONE 'Tokyo Standard Time' AS plan_last_execution_time,
qt.query_sql_text,
CAST(p.query_plan AS xml)
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
WHERE
qt.query_sql_text LIKE '%SELECT TOP 100 * FROM frontdoor.accesslog%'
AND
qt.query_sql_text NOT LIKE '%query_store%'
GO
今回、ヒントを適用したいクエリは「1834769」ということがわかりましたので、「sp_query_store_clear_hints」を実行して、クエリストアヒントを適用してみます。
EXEC sp_query_store_set_hints @query_id=1834769, @value = N'OPTION(RECOMPILE, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160''))';
GO
今回はクエリストアヒントとして「RECOMPILE」と「互換性レベル160での実行」を指定しています。
指定可能なヒントについては次のドキュメントを参照してください。
設定したクエリヒントは「sys.query_store_query_hints」で情報を確認することができます。
クエリヒントを設定したら先ほど実行したクエリを再実行してみます。
同一のパラメーター化クエリを連続して実行していますが、クエリストアヒントとして「RECOMPILE」を設定していますので、キャッシュクリア等を行わなくても、クエリがリコンパイルされ、それぞれのデータ件数に応じた検索が行われていますね。
クエリストアではこのようにして、特定のクエリ ID のクエリに対して、ヒント句を容易に追加することができます。
上記のクエリでは RECOMPILE を指定していますが、MAXDOP を指定することで並列クエリの調整を行うこともできます。
クエリストアヒントのクエリは「sp_query_store_clear_hints」で実行することができますので、次のようなクエリを実行してみます。
EXEC sp_query_store_clear_hints @query_id=1834769
GO
EXEC sp_executesql
N'SELECT TOP 100 * FROM frontdoor.accesslog WHERE securityProtocol = @p1 ORDER BY [httpMethod] ASC',
N'@p1 varchar(100)', @p1 = 'TLS 1.2'
GO
EXEC sp_query_store_set_hints @query_id=1834769, @value = N'OPTION(MAXDOP 1)';
GO
EXEC sp_executesql
N'SELECT TOP 100 * FROM frontdoor.accesslog WHERE securityProtocol = @p1 ORDER BY [httpMethod] ASC',
N'@p1 varchar(100)', @p1 = 'TLS 1.2'
GO
2 回目の実行については、「MAXDOP 1」を指定していますので並列化が解けていますね。
特定のクエリで MAXDOP が高く CPU 使用率が多い場合の補正に活用することもできます。
読み取り専用サーバーで動作するか
SQL Database は読み取り専用サーバー (Read Only) を容易に作成することができます。
読み取り専用サーバーではクエリストアは「READ_ONLY」となり、クエリストアの情報は更新可能なプライマリの情報が格納される形になります。
プライマリで設定したクエリストアヒントはセカンダリでも有効に動作するのでしょうか？
プライマリで「RECOMPILE」のクエリストアヒントを指定した状態で、セカンダリで次のクエリを実行してみます。
EXEC sp_executesql
N'SELECT TOP 100 * FROM frontdoor.accesslog WHERE securityProtocol = @p1 ORDER BY [httpMethod] ASC',
N'@p1 varchar(100)', @p1 = 'TLS 1.2'
GO
EXEC sp_executesql
N'SELECT TOP 100 * FROM frontdoor.accesslog WHERE securityProtocol = @p1 ORDER BY [httpMethod] ASC',
N'@p1 varchar(100)', @p1 = 'TLS 1.0'
GO
残念ながら読み取り専用サーバーでは、クエリストアヒントの内容は考慮されないようです。
クエリストア回り、読み取り専用サーバーのサポートは以前としてシビアですね…。
[…] https://blog.engineer-memo.com/2021/06/24/sql-database-%e3%81%ae%e3%82%af%e3%82%a8%e3%83%aa%e3%82%b9… […]
【後で読みたい！】SQL Database のクエリストアヒント (プレビュー) について | Tak's Bar
27 6月 21 at 20:44