SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Database のクエリストアヒント (プレビュー) について

leave a comment

Query Store Hints Preview でアナウンスがありましたが、SQL Database でクエリストアヒントがプレビュー機能として使用できるようになりました。

今まで、クエリストアを使用した実行プランの補正としては、プランの強制という機能があり、同一のクエリで複数の実行プランがある場合、特定のプランを使用するようにプランを強制することができました。

image

この機能を実行プランの補正に使うことができたのですが、プランの強制は「強制したい実行プランの情報がクエリストアに格納されている」必要があり、使用したい実行プランの情報がクエリストア上に格納されている必要がありました。

今回使用できるようになった「クエリストアヒント」については、クエリストアに格納されている実行プランのクエリについて「クエリヒントを適用することができる」機能となっており、「強制したいプランがクエリストアに存在していない」状態でも、プランの補正を柔軟に実施することができます。

類似の機能としては「プランガイド」を使用して、クエリヒントをアタッチすることができましたが、クエリストアヒントはプランガイドより容易にクエリヒントを適用することが可能です。(プランガイド、設定するのに少し手間がかかるんですよね…。)

公式のドキュメントは次の内容を確認してください。

クエリストアヒントの利用

実際にクエリストアヒントを使用してみます。
今回は当ブログのアクセスログを格納したテーブルのデータで動作確認をしてみます。

最初に以下のクエリを実行して、検索に使用する「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」であることが確認できます。

image

それではデータを取得してみたいと思います。

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」のデータの取得を行っています。

このクエリを実行した場合、次のような実行プランが生成されます。

image

登録されているデータの大半は「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

 

image

先ほどのクエリで「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

image

今回、ヒントを適用したいクエリは「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」で情報を確認することができます。

image

クエリヒントを設定したら先ほど実行したクエリを再実行してみます。

image

同一のパラメーター化クエリを連続して実行していますが、クエリストアヒントとして「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」を指定していますので並列化が解けていますね。

image

特定のクエリで 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

 

残念ながら読み取り専用サーバーでは、クエリストアヒントの内容は考慮されないようです。

image

クエリストア回り、読み取り専用サーバーのサポートは以前としてシビアですね…。

Written by Masayuki.Ozawa

6月 24th, 2021 at 11:45 pm

Leave a Reply

Share via
Copy link
Powered by Social Snap