適用対象:
Sql Server 2022 (16.x) 以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceSQL データベース (Microsoft Fabric プレビュー)
この記事では、クエリ ストアを使用してクエリ ヒントを適用する方法について説明します。 クエリ ストア ヒントは、アプリケーション コードを変更することなくクエリ プランを形成するための使いやすい手段となります。
- クエリ ストアを使用した構成と管理の詳細については、「クエリ ストアを 使用したパフォーマンスの監視」を参照してください。
- 実行可能な情報を検出し、クエリ ストアでパフォーマンスを調整する方法については、「クエリ ストア を使用してパフォーマンスを調整する」を参照してください。
- Azure SQL Database におけるクエリ ストアの運用の詳細については、「 Azure SQL Database でクエリ ストアを運用する」を参照してください。
Caution
通常、クエリにとって最適な実行プランが SQL Server クエリ オプティマイザーによって選択されるため、ヒントは、経験を積んだ開発者やデータベース管理者が最後の手段としてのみ使用することをお勧めします。 詳細については、クエリ ヒントを参照してください。
クエリ ストア ヒントの概要については、こちらのビデオをご覧ください。
Overview
クエリ オプティマイザーでは、クエリに最適な実行プランを選択するのが理想的です。
最適なプランが選択されていない場合、開発者またはデータベース管理者 (DBA) は、特定の条件に合わせて手動で最適化することができます。 クエリ ヒントは、OPTION 句を使用して指定し、クエリ実行の動作に影響を与えるために使用できます。 クエリ ヒントは、さまざまなパフォーマンス関連の問題にローカライズされたソリューションを提供するのに役立ちますが、元のクエリ テキストを書き直す必要があります。 データベース管理者と開発者は、クエリ ヒントを追加するためにコード Transact-SQL 直接変更できるとは限りません。 Transact-SQL はアプリケーションにハードコーディングされることも、アプリケーションによって自動的に生成されることもあります。 以前は、開発者は場合によってプラン ガイドに依存しなければなりませんでしたが、それは複雑で使いづらいことがありました。
クエリ ストア ヒントを使用すると、クエリ Transact-SQL テキストを直接変更することなくクエリにクエリ ヒントを挿入できるため、この問題が解決されます。 どのクエリ ヒントを適用できるかについては、「サポートされているクエリ ヒント」を参照してください。
いつクエリ ストア ヒントを使用するか
名前が示すように、この機能はクエリ ストアを拡張し、ここれに依存しています。 クエリ ストアを使用すると、クエリ、実行プラン、関連するランタイム統計情報を取得できます。 クエリ ストアは、全体的なパフォーマンス チューニングのカスタマー エクスペリエンスを大幅に簡素化します。 SQL Server 2016 (13.x) では最初にクエリ ストアが導入され、Sql Server 2022 (16.x)、Azure SQL Managed Instance、Azure SQL Database、および Microsoft Fabric Preview の SQL データベースで既定で有効になりました。
最初にクエリが実行され、次にクエリ ストアによって取得されます。 次に、DBA がクエリに対してクエリ ストア ヒントを作成します。 その後、クエリ ストア ヒントを使用してクエリが実行されます。
クエリ ストア ヒントがクエリレベルのパフォーマンスの問題に役立つ例を次に示します。
- 実行ごとにクエリを再コンパイルする。
- 一括挿入操作のメモリ許可サイズに上限を設定する。
- 統計の更新操作の並列処理の最大限度を制限する。
- ネステッド ループ結合ではなく、ハッシュ結合を使用する。
- 互換性レベル 150 でデータベース内の他のすべてを維持したまま、特定のクエリに対して互換性レベル 110 を使用する。
-
SELECT TOPクエリの行目標の最適化を無効にします。
クエリ ストア ヒントを使用するには:
変更するクエリ ステートメントのクエリ ストア
query_idを特定します。 これはさまざまな方法で行うことができます。- クエリ ストア カタログ ビュー (Transact-SQL) のクエリを実行します。
- SQL Server Management Studio の組み込みクエリ ストア レポートを使用する。
- Azure portal で Azure SQL Database の Query Performance Insight を使用する。
sys.sp_query_store_set_hintsと、クエリに適用するクエリ ヒント文字列を使用してquery_idを実行します。 この文字列には、1 つまたは複数のクエリ ヒントを含めることができます。 詳細については、「sys.sp_query_store_set_hints」を参照してください。
クエリ ストア ヒントは、作成されると永続化され、再起動およびフェールオーバー後も残ります。 クエリ ストア ヒントは、ハードコーディングされたステートメント レベルのヒントと既存のプラン ガイドのヒントをオーバーライドします。
クエリ ヒントがクエリの最適化の可能性と矛盾している場合、クエリの実行はブロックされず、ヒントは適用されません。 ヒントによってクエリが失敗する場合、ヒントは無視され、最新のエラーの詳細を sys.query_store_query_hintsで表示できます。
クエリ ストア ヒントを使用する前に
クエリ ストア ヒントの使用を開始する前に、次の点を考慮してください。
- 潜在的な新しいクエリ ストア ヒントのクエリを評価する前に、 統計のメンテナンス と インデックスのメンテナンス (必要な場合) を完了します。 統計のメンテナンスを行い、インデックスのメンテナンスの程度を減らすと、クエリ ヒントを必要とする問題が解決される可能性があります。
- クエリ ストア ヒントを使用する前に、最新の 互換性レベル でアプリケーション データベースをテストし、クエリ ヒントが必要な問題が解決するかどうかを確認します。
- たとえば、パラメーターセンシティブ プラン (PSP) の最適化は、互換性レベル 160 の SQL Server 2022 (16.x) で導入されました。 クエリごとに複数のアクティブなプランを使用して、一様でないデータ分散に対処します。 環境で最新の互換性レベルを使用できない場合は、
RECOMPILEヒントを使用したクエリ ストア ヒントをサポートする互換性レベルで使用できます。
- たとえば、パラメーターセンシティブ プラン (PSP) の最適化は、互換性レベル 160 の SQL Server 2022 (16.x) で導入されました。 クエリごとに複数のアクティブなプランを使用して、一様でないデータ分散に対処します。 環境で最新の互換性レベルを使用できない場合は、
- クエリ ストア ヒントは、データベース エンジンの既定のクエリ プランの動作をオーバーライドします。 クエリ ストア ヒントは、パフォーマンス関連の問題に対処する必要がある場合にのみ使用してください。
- データ ボリュームとディストリビューションの変更やデータベース移行プロジェクト中に、クエリ ストア のヒント、ステートメント レベルのヒント、プラン ガイド、クエリ ストアの強制プランを再評価する必要があります。 データ ボリュームと分散の変更により、クエリ ストア ヒントが最適でない実行プランを生成する可能性があります。
クエリ ストア ヒントのシステム ストアド プロシージャ
ヒントを作成または更新するには、sys.sp_query_store_set_hints を使用します。 ヒントは、有効な文字列形式 N'OPTION (...)' で指定されます。
- クエリ ストア ヒントを作成するとき、特定の
query_idのクエリ ストア ヒントが存在しない場合は、新しいクエリ ストア ヒントが作成されます。 - クエリ ストア ヒントを作成または更新するときに、特定の
query_idに対してクエリ ストア ヒントが既に存在する場合、指定された最後の値は、関連付けられているクエリに対して以前に指定された値をオーバーライドします。 -
query_idが存在しない場合は、エラーが発生します。
クエリ ストア ヒントとしてサポートされているヒントの完全な一覧については、 sys.sp_query_store_set_hintsを参照してください。
query_id に関連付けられているヒントを削除するには、sys.sp_query_store_clear_hints を使用します。
Tip
クエリ ハッシュに一致するすべての query_id 値のヒントを設定またはクリアする必要がある場合があります。
dbo.sp_query_store_modify_hints_by_query_hash は、ループ内で sys.sp_query_store_set_hints または sys.sp_query_store_clear_hints システム ストアド プロシージャを呼び出してこれを実現するサンプル ストアド プロシージャです。
実行プランの XML 属性
ヒントが適用されると、実行StmtSimpleの要素に XML 形式で次の結果セットが表示されます。
| Attribute | Description |
|---|---|
QueryStoreStatementHintText |
クエリに適用された実際のクエリ ストア ヒント |
QueryStoreStatementHintId |
クエリ ヒントの一意識別子 |
QueryStoreStatementHintSource |
クエリ ストア ヒントのソース (たとえば、 User) |
Note
これらの XML 要素は、 SET STATISTICS XML および SET SHOWPLAN_XML Transact-SQL コマンドの出力を介して使用できます。
クエリ ストア ヒントと機能の相互運用性
- クエリ ストア ヒントは、他のハードコーディングされたステートメント レベルのヒントとプラン ガイドをオーバーライドします。
-
ABORT_QUERY_EXECUTIONヒントを除き、クエリ ストア ヒントを含むクエリは常に実行されます。 エラーの原因になる相反するクエリ ストア ヒントは無視されます。 - クエリ ストア ヒントが矛盾する場合、データベース エンジンはクエリの実行をブロックせず、クエリ ストア ヒントは適用されません。
- クエリ ストア ヒントは、単純なパラメーター化の対象となるステートメントではサポートされていません。
-
RECOMPILEヒントは、データベース レベルで設定された強制パラメーター化と互換性がありません。 データベースに強制的なパラメーター化が設定されていて、RECOMPILEヒントがクエリのクエリ ストア ヒントの一部である場合、データベース エンジンはRECOMPILEヒントを無視し、指定されている場合は他のヒントを適用します。- データベース エンジンは、
RECOMPILEヒントが無視されたことを示す警告 (エラー コード 12461) を発行します。 - 強制パラメーター化のユース ケースに関する考慮事項の詳細については、「 強制パラメーター化の使用に関するガイドライン」を参照してください。
- データベース エンジンは、
- 手動で作成されたクエリ ストア ヒントは、クエリ ストアのクリーンアップから除外されます。 ヒントとクエリは、自動保持キャプチャ ポリシーによってクリーンアップされません。
- クエリは 、ユーザーが手動で削除できます。 これにより、関連付けられているクエリ ストア ヒントも削除されます。
- CE フィードバックによって自動的に生成されるクエリ ストア ヒントは、キャプチャ ポリシーの自動保持によってクリーンアップされる可能性があります。
- クエリ ストア ヒントを使用せずに、DOP フィードバック と メモリ許可フィードバック の形のクエリ動作。 自動保持キャプチャ ポリシーによってクエリがクリーンアップされると、DOP フィードバックとメモリ許可フィードバック データもクリーンアップされます。
- CE フィードバックが手動で実装されたのと同じクエリ ストア ヒントを作成した場合、ヒントを含むクエリは、自動保持キャプチャ ポリシーによってクリーンアップされなくなります。
クエリ ストア のヒントとセカンダリ レプリカ
クエリ ストア ヒントは、セカンダリ レプリカのクエリ ストアが有効になっていない限り、セカンダリ レプリカには影響しません。 詳細については、「 読み取り可能なセカンダリのクエリ ストア」を参照してください。
- SQL Server 2022 (16.x) 以前のバージョンでは、クエリ ストア ヒントはプライマリ レプリカにのみ適用できます。
- SQL Server 2022 (16.x) 以降のバージョンでは、セカンダリ レプリカのクエリ ストアを有効にすると、可用性グループのセカンダリ レプリカにクエリ ストア ヒントを適用できます。
- クエリ ストア ヒントは、セカンダリ レプリカのクエリ ストアが有効になっている場合にのみ、特定のレプリカ グループに対してのみ有効にできます。 これを行うには、sys.sp_query_store_set_query_hintsを呼び出すときに
@replica_group_idパラメーター を使用します。 逆に、 sys.sp_query_store_clear_query_hintsを使用して、特定のレプリカ グループからクエリ ストア ヒントを削除できます。 - sys.query_store_replicasクエリを実行して、使用可能なレプリカ グループを見つけます。
- sys.query_store_plan_forcing_locationsを使用してセカンダリ レプリカで強制されるプランを検索します。
Examples
A. クエリ ストア ヒントのデモ
Azure SQL Database のクエリ ストア ヒントの次のチュートリアルでは、BACPAC ファイル (.bacpac) を介してインポートされたデータベースを使用します。 Azure SQL Database サーバーに新しいデータベースをインポートする方法については、「 クイック スタート: Bacpac ファイルを Azure SQL Database または Azure SQL Managed Instance のデータベースにインポートする」を参照してください。
-- ************************************************************************ --
-- 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;
最後に、query_id を使用して、 39 からヒントを削除 します。
EXEC sys.sp_query_store_clear_hints @query_id = 39;
Related content
- 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)
- クエリ ストアを使用してワークロードを監視するためのベスト プラクティス
- クエリ ストアのヒント、ベスト プラクティス
- クエリ ストアを使用したパフォーマンスの監視
- Azure SQL Database での並列処理の最大限度 (MAXDOP) の構成