クエリ ストアのヒント
適用対象: 
SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance Microsoft Fabric SQL Database
この記事では、クエリ ストアを使用してクエリ ヒントを適用する方法について説明します。 クエリ ストア ヒントは、アプリケーション コードを変更することなくクエリ プランを形成するための使いやすい手段となります。
クエリ ストア ヒントは、Azure SQL Database と Azure SQL Managed Instance で使用できます。 クエリ ストア ヒントは、SQL Server 2022 (16.x) の SQL Server に導入された機能でもあります。
- クエリ ストアを使用した構成と管理の詳細については、「クエリ ストアを使用したパフォーマンスの監視」を参照してください。
- アクションにつながる情報を検出し、クエリ ストアを使用してパフォーマンスを調整する方法については、「クエリ ストアを使用したパフォーマンスの調整」を参照してください。
- Azure SQL Database におけるクエリ ストアの運用の詳細については、「 Azure SQL Database でクエリ ストアを運用する」を参照してください。
注意事項
通常、クエリにとって最適な実行プランが SQL Server クエリ オプティマイザーによって選択されるため、ヒントは、経験を積んだ開発者やデータベース管理者が最後の手段としてのみ使用することをお勧めします。 詳細については、「クエリ ヒント」を参照してください。
クエリ ストア ヒントの概要については、こちらのビデオをご覧ください。
概要
クエリ オプティマイザーでは、クエリに最適な実行プランを選択するのが理想的です。
最適なプランが選択されていない場合は、開発者または DBA が特定の条件に合わせて手動で最適化することが必要になる場合があります。 クエリ ヒントは、OPTION 句を使用して指定し、クエリ実行の動作に影響を与えるために使用できます。 クエリ ヒントは、さまざまなパフォーマンス関連の問題にローカライズされたソリューションを提供するのに役立ちますが、元のクエリ テキストを書き直す必要があります。 データベースの管理者と開発者は、クエリ ヒントを挿入するために Transact-SQL コードを直接変更できない場合があります。 Transact-SQL はアプリケーションにハードコーディングされることも、アプリケーションによって自動的に生成されることもあります。 以前は、開発者は場合によってプラン ガイドに依存しなければなりませんでしたが、それは複雑で使いづらいことがありました。
どのクエリ ヒントを適用できるかについては、「サポートされているクエリ ヒント」を参照してください。
いつクエリ ストア ヒントを使用するか
名前が示すように、この機能はクエリ ストアを拡張し、ここれに依存しています。 クエリ ストアを使用すると、クエリ、実行プラン、関連するランタイム統計情報を取得できます。 クエリ ストアは、全体的なパフォーマンス チューニングのカスタマー エクスペリエンスを大幅に簡素化します。 クエリ ストアは SQL Server 2016 (13.x) で最初に導入され、現在、SQL Server 2022 (16.x)、Azure SQL Managed Instance、Azure SQL データベースでは既定で有効になっています。
最初にクエリが実行され、次にクエリ ストアによって取得されます。 次に、DBA がクエリに対してクエリ ストア ヒントを作成します。 その後、クエリ ストア ヒントを使用してクエリが実行されます。
クエリ ストア ヒントがクエリレベルのパフォーマンスの問題に役立つ例を次に示します。
- 実行ごとにクエリを再コンパイルする。
- 一括挿入操作のメモリ許可サイズに上限を設定する。
- 統計の更新操作の並列処理の最大限度を制限する。
- ネステッド ループ結合ではなく、ハッシュ結合を使用する。
- 互換性レベル 150 でデータベース内の他のすべてを維持したまま、特定のクエリに対して互換性レベル 110 を使用する。
- SELECT TOP クエリの行の目標の最適化を無効にする。
クエリ ストア ヒントを使用するには:
- 変更するクエリ ステートメントのクエリ ストア
query_idを特定します。 これは、次のさまざまな方法で実行できます。- クエリ ストア カタログ ビューにクエリを実行する。
- SQL Server Management Studio の組み込みクエリ ストア レポートを使用する。
- Azure portal で Azure SQL Database の Query Performance Insight を使用する。
query_idと、クエリに適用するクエリ ヒント文字列を使用してsys.sp_query_store_set_hintsを実行します。 この文字列には、1 つまたは複数のクエリ ヒントを含めることができます。 詳細については、「sys.sp_query_store_set_hints」を参照してください。
クエリ ストア ヒントは、作成されると永続化され、再起動およびフェールオーバー後も残ります。 クエリ ストア ヒントは、ハードコーディングされたステートメント レベルのヒントと既存のプラン ガイドのヒントをオーバーライドします。
クエリ ヒントがクエリ最適化に使用できる内容と矛盾する場合、ヒントによってクエリの実行がブロックされることはなく、ヒントは適用されません。 ヒントが原因でクエリが失敗する場合、ヒントは無視され、最新のエラーの詳細は sys.query_store_query_hints で表示できます。
クエリ ストア ヒントのシステム ストアド プロシージャ
ヒントを作成または更新するには、sys.sp_query_store_set_hints を使用します。 ヒントは、有効な文字列形式 N'OPTION (...)' で指定されます。
- クエリ ストア ヒントを作成するとき、特定の
query_idのクエリ ストア ヒントが存在しない場合は、新しいクエリ ストア ヒントが作成されます。 - クエリ ストア ヒントを作成または更新するときに、特定の
query_idに対してクエリ ストア ヒントが既に存在する場合、指定された最後の値は、関連付けられているクエリに対して以前に指定された値をオーバーライドします。 query_idが存在しない場合は、エラーが発生します。
Note
サポートされているヒントの完全な一覧については、「sys.sp_query_store_set_hints」を参照してください。
query_id に関連付けられているヒントを削除するには、sys.sp_query_store_clear_hints を使用します。
実行プランの XML 属性
ヒントが適用されると、 XML 形式 の 実行プラン のStmtSimple 要素に次の結果セットが表示されます。
| 属性 | 説明 |
|---|---|
QueryStoreStatementHintText |
クエリに適用された実際のクエリ ストア ヒント |
QueryStoreStatementHintId |
クエリ ヒントの一意識別子 |
QueryStoreStatementHintSource |
クエリ ストア ヒントのソース (例: "User") |
Note
これらの XML 要素は、Transact-SQL コマンド SET STATISTICS XML および SET SHOWPLAN XML の出力によってのみ使用できます。
クエリ ストア ヒントと機能の相互運用性
- クエリ ストア ヒントは、他のハードコーディングされたステートメント レベルのヒントとプラン ガイドをオーバーライドします。
- クエリは常に実行されます。 エラーの原因になる相反するクエリ ストア ヒントは無視されます。
- クエリ ストア ヒントが矛盾している場合、SQL Server によってクエリの実行がブロックされることはなく、クエリ ストア ヒントは適用されません。
- 簡易パラメーター化 - クエリ ストア ヒントは、簡易パラメーター化の対象となるステートメントではサポートされていません。
- 強制パラメーター化 - RECOMPILE ヒントは、データベース レベルで設定された強制パラメーター化と互換性がありません。 データベースに強制パラメーター化が設定されていて、RECOMPILE ヒントがクエリのクエリ ストアに設定されているヒント文字列の一部である場合、SQL Server で RECOMPILE ヒントは無視され、他のヒントが利用されていれば適用されます。
- さらに、SQL Server により、RECOMPILE ヒントが無視されたことを示す警告 (エラー コード 12461) が発行されます。
- 強制パラメーター化のユース ケースに関する考慮事項の詳細については、「強制パラメーター化使用のガイドライン」を参照してください。
- 手動で作成されたクエリ ストア ヒントは、クリーンアップから除外されます。 ヒントとクエリは、キャプチャ ポリシーの自動保持によってクエリ ストアからクリーンアップされません。
- クエリは ユーザーが手動で削除でき、関連付けられているクエリ ストア ヒントも削除されます。
- CE フィードバックによって自動的に生成されるクエリ ストア ヒントは、キャプチャ ポリシーの自動保持によってクリーンアップされる可能性があります。
- クエリ ストア ヒントを使用せずに、DOP フィードバック と メモリ許可フィードバック の形のクエリ動作。 キャプチャ ポリシーの自動保持によってクエリがクリーンアップされると、DOP フィードバックとメモリ許可フィードバック データもクリーンアップされます。
- CE フィードバックが実装されたのと同じクエリ ストア ヒントを手動で作成できます。その後、ヒントを含むクエリは、キャプチャ ポリシーの自動保持によってクリーンアップの対象ではなくなります。
クエリ ストアのヒントと可用性グループ
クエリ ストア ヒントは、セカンダリ レプリカのクエリ ストアが有効になっていない限り、セカンダリ レプリカには影響しません。 詳細については、「セカンダリ レプリカ のクエリ ストア」を参照してください。
- SQL Server 2022 (16.x) より前のバージョンでは、可用性グループのプライマリ レプリカに対してクエリ ストア ヒントを適用できます。
- SQL Server 2022 (16.x) 以降では、セカンダリ レプリカのクエリ ストアが有効になっている場合、クエリ ストア ヒントは可用性グループ内のセカンダリ レプリカに対してもレプリカ対応です。
- セカンダリ レプリカのクエリ ストアが有効になっている場合は、クエリ ストア ヒントを特定のレプリカまたはレプリカ セットに追加できます。 sys.sp_query_store_set_query_hintsでは、これは、SQL Server 2022 (16.x) で導入された
@query_hint_scopeパラメーターによって設定されます。 - sys.query_store_replicasクエリを実行して、使用可能なレプリカ セットを検索します。
- sys.query_store_plan_forcing_locationsを使用してセカンダリ レプリカで強制されるプランを検索します。
クエリ ストア ヒントのベスト プラクティス
- 潜在的な新しいクエリ ストア ヒントについてクエリを評価する前に、インデックスと統計のメンテナンスを完了します。
- クエリ ストア ヒントを利用する前に、アプリケーション データベースを最新の互換性レベルに関してテストします。
- たとえば、パラメーター依存プラン (PSP) 最適化は SQL Server 2022 (16.x) (互換性レベル 160) で導入され、クエリごとに複数のアクティブなプランを利用して、一様でないデータ分散に対処します。 最新の互換性レベルを使用できない環境の場合は、RECOMPILE ヒントを使用したクエリ ストア ヒントを、サポートされているあらゆる互換性レベルで利用できます。
- クエリ ストア ヒントは、SQL Server のクエリ プランの動作をオーバーライドします。 クエリ ストア ヒントは、パフォーマンス関連の問題に対処する必要がある場合にのみ利用することをお勧めします。
- データ分散に変更があったときはいつでも、またデータベース移行プロジェクト中には、クエリ ストア ヒント、ステートメント レベルのヒント、プラン ガイド、クエリ ストアの強制プランを再評価することをお勧めします。 データ分散に変更があった場合、クエリ ストア ヒントによって最適でない実行プランが生成される可能性があります。
例
A. クエリ ストア ヒントのデモ
Azure SQL Database のクエリ ストア ヒントの次のチュートリアルでは、BACPAC ファイル (.bacpac) を介してインポートされたデータベースを使用します。 Azure SQL Database サーバーに新しいデータベースをインポートする方法については、BACPAC ファイルをデータベースにインポートするクイックスタートを参照してください。
-- ************************************************************************ --
-- Query Store hints demo
-- Demo uses "PropertyMLS" database which can be imported from BACPAC here:
-- https://github.com/microsoft/sql-server-samples/tree/master/samples/features/query-store
-- Email QSHintsFeedback@microsoft.com for questions\feedback
-- ************************************************************************ --
/*
Demo prep, connect to the PropertyMLS database
*/
ALTER DATABASE [PropertyMLS] SET QUERY_STORE CLEAR;
ALTER DATABASE CURRENT SET QUERY_STORE = ON;
ALTER DATABASE CURRENT SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL);
GO
-- Should be READ_WRITE
SELECT actual_state_desc
FROM sys.database_query_store_options;
GO
/*
You can verify Query Store Hints in sys.query_store_query_hints.
Checking if any already exist (should be none).
*/
SELECT query_hint_id,
query_id,
query_hint_text,
last_query_hint_failure_reason,
last_query_hint_failure_reason_desc,
query_hint_failure_count,
source,
source_desc
FROM sys.query_store_query_hints;
GO
/*
The PropertySearchByAgent stored procedure has a parameter
used to filter AgentId. Looking at the statistics for AgentId,
you will see that there is a big skew for AgentId 101.
*/
SELECT hist.range_high_key AS [AgentId],
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.[name] = N'NCI_Property_AgentId';
-- Show actual query execution plan to see plan compiled.
-- Agent with many properties will have a scan with parallelism.
EXEC [dbo].[PropertySearchByAgent] 101;
-- Agents with few properties still re-use this plan (assuming no recent plan eviction).
EXEC [dbo].[PropertySearchByAgent] 4;
/*
Now let's find the query_id associated with this query.
*/
SELECT query_sql_text, q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%' and query_sql_text not like N'%query_store%';
GO
/*
We can set the hint associated with the query_id returned in the previous result set, as below.
Note, we can designate one or more query hints
*/
EXEC sp_query_store_set_hints @query_id=5, @value = N'OPTION(RECOMPILE)';
GO
/*
You can verify Query Store Hints in sys.query_store_query_hints
*/
SELECT query_hint_id,
query_id,
query_hint_text,
last_query_hint_failure_reason,
last_query_hint_failure_reason_desc,
query_hint_failure_count,
source,
source_desc
FROM sys.query_store_query_hints;
GO
-- Execute both at the same time and show actual query execution plan.
-- You should see two different plans, one for AgentId 101 and one for AgentId 4.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO
SELECT query_hint_id,
query_id,
query_hint_text,
last_query_hint_failure_reason,
last_query_hint_failure_reason_desc,
query_hint_failure_count,
source,
source_desc
FROM sys.query_store_query_hints;
GO
/*
We can remove the hint using sp_query_store_clear_query_hints
*/
EXEC sp_query_store_clear_hints @query_id = 10;
GO
/*
That Query Store Hint is now removed
*/
SELECT query_hint_id,
query_id,
query_hint_text,
last_query_hint_failure_reason,
last_query_hint_failure_reason_desc,
query_hint_failure_count,
source,
source_desc
FROM sys.query_store_query_hints;
GO
-- Execute both at the same time and show actual query execution plan.
-- You should see one plan again.
EXEC [dbo].[PropertySearchByAgent] 101;
EXEC [dbo].[PropertySearchByAgent] 4;
GO
B. クエリ ストアでクエリを識別する
次の例では、sys.query_store_query_text と sys.query_store_query のクエリを実行し、実行されたクエリ テキスト フラグメントの query_id を返します。
このデモでは、チューニングしようとしているクエリが SalesLT サンプル データベースに含まれています。
SELECT * FROM SalesLT.Address as A
INNER JOIN SalesLT.CustomerAddress as CA
on A.AddressID = CA.AddressID
WHERE PostalCode = '98052' ORDER BY A.ModifiedDate DESC;
クエリ データはクエリ ストアでシステム ビューに直ちに反映されるわけではないことに注意してください。
クエリ ストア システム カタログ ビューでクエリを識別します。
SELECT q.query_id, qt.query_sql_text
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON
qt.query_text_id = q.query_text_id
WHERE query_sql_text like N'%PostalCode =%'
AND query_sql_text not like N'%query_store%';
GO
次のサンプルでは、SalesLT データベース内の前のクエリ例は query_id 39 として特定されます。
特定できたら、構成済みメモリ上限の最大メモリ付与サイズ (%) を強制するヒントを query_id に適用します。
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(MAX_GRANT_PERCENT=10)';
また、次の構文を使用してクエリ ヒントを適用することもできます。たとえば、レガシ カーディナリティ推定を強制するオプションがあります。
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
コンマ区切りの一覧を使用して、複数のクエリ ヒントを適用できます。
EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(RECOMPILE, MAXDOP 1, USE HINT(''QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110''))';
query_id 39 の代わりにクエリ ストア ヒントを確認します。
SELECT query_hint_id, query_id, query_hint_text, last_query_hint_failure_reason, last_query_hint_failure_reason_desc, query_hint_failure_count, source, source_desc
FROM sys.query_store_query_hints
WHERE query_id = 39;
最後に、sp_query_store_clear_hints を使用して、query_id 39 からヒントを削除 します。
EXEC sys.sp_query_store_clear_hints @query_id = 39;
関連するコンテンツ
- sys.query_store_query_hints (Transact-SQL)
- sys.sp_query_store_set_hints (Transact-SQL)
- sys.sp_query_store_clear_hints (Transact-SQL)
- XML 形式での実行プランの保存
- 実行プランの表示と保存
- ヒント (Transact-SQL) - Query
- クエリ ストアを使用してワークロードを監視するためのベスト プラクティス
- クエリ ストア ヒントのベスト プラクティス
- クエリ ストアを使用したパフォーマンスの監視
- Azure SQL Database での並列処理の最大限度 (MAXDOP) の構成