クエリ ストアのヒント

適用対象: SQL Server 2022 (16.x) Preview Azure SQL Database Azure SQL Managed Instance

この記事では、クエリ ストアを使用してクエリ ヒントを適用する方法について説明します。 クエリ ストア ヒントは、アプリケーション コードを変更することなくクエリ プランを形成するための使いやすい手段となります。

クエリ ストア ヒントは、Azure SQL Database と Azure SQL Managed Instance で使用できます。 クエリ ストア ヒントは、SQL Server 2022 (16.x) プレビューでSQL Serverに導入された機能でもあります。

注意事項

通常、クエリにとって最適な実行プランが SQL Server クエリ オプティマイザーによって選択されるため、ヒントは、経験を積んだ開発者やデータベース管理者が最後の手段としてのみ使用することをお勧めします。 詳細については、「クエリ ヒント」を参照してください。

概要

クエリ オプティマイザーでは、クエリに最適な実行プランを選択するのが理想的です。 これが行われない場合、開発者や DBA は、特定の条件に合わせて手動で最適化する必要があります。 クエリ ヒントは、OPTION 句を使用して指定し、クエリ実行の動作に影響を与えるために使用できます。 クエリ ヒントは、さまざまなパフォーマンス関連の問題にローカライズされたソリューションを提供するのに役立ちますが、元のクエリ テキストを書き直す必要があります。 データベースの管理者と開発者は、クエリ ヒントを挿入するために Transact-SQL コードを直接変更できない場合があります。 Transact-SQL はアプリケーションにハードコーディングされることも、アプリケーションによって自動的に生成されることもあります。 以前は、開発者は場合によってプラン ガイドに依存しなければなりませんでしたが、それは複雑で使いづらいことがありました。

どのクエリ ヒントを適用できるかについては、「サポートされているクエリ ヒント」を参照してください。

いつクエリ ストア ヒントを使用するか

名前が示すように、この機能はクエリ ストアを拡張し、ここれに依存しています。 クエリ ストアを使用すると、クエリ、実行プラン、関連するランタイム統計情報を取得できます。 クエリ ストアは SQL Server 2016 (13.x) で導入され、Azure SQL Database では既定でオンになっており、全体的なパフォーマンス チューニングのカスタマー エクスペリエンスを大幅に簡素化しています。

クエリ ストア ヒントのワークフロー。

最初にクエリが実行され、次にクエリ ストアによって取得されます。 次に、DBA がクエリに対してクエリ ストア ヒントを作成します。 その後、クエリ ストア ヒントを使用してクエリが実行されます。

クエリ ストア ヒントがクエリレベルのパフォーマンスの問題に役立つ例を次に示します。

  • 実行ごとにクエリを再コンパイルする。
  • 一括挿入操作のメモリ許可サイズに上限を設定する。
  • 統計の更新操作の並列処理の最大限度を制限する。
  • ネステッド ループ結合ではなく、ハッシュ結合を使用する。
  • 互換性レベル 150 でデータベース内の他のすべてを維持したまま、特定のクエリに対して互換性レベル 110 を使用する。
  • SELECT TOP クエリの行の目標の最適化を無効にする。

クエリ ストア ヒントを使用するには:

  1. 変更するクエリ ステートメントのクエリ ストア query_id を特定します。 これは、さまざまな方法で実行できます: 1.1. クエリ ストア カタログ ビューにクエリを実行する。 1.2. SQL Server Management Studio の組み込みクエリ ストア レポートを使用する。 1.3. Azure portal で Azure SQL Database の Query Performance Insight を使用する。
  2. 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 が存在しない場合は、エラーが発生します。

注意

サポートされているヒントの完全な一覧については、「sys.sp_query_store_set_hints」を参照してください。

query_id に関連付けられているヒントを削除するには、sys.sp_query_store_clear_hints を使用します。

実行プランの XML 属性

ヒントが適用されると、次の結果セットが実行プランの要素に XML 形式で表示されますStmtSimple

属性 説明
QueryStoreStatementHintText クエリに適用された実際のクエリ ストア ヒント
QueryStoreStatementHintId クエリ ヒントの一意識別子
QueryStoreStatementHintSource クエリ ストア ヒントのソース (例: "User")

注意

これらの XML 要素は、Transact-SQL コマンド SET STATISTICS XMLSET SHOWPLAN XML の出力を介して使用できます。

クエリ ストア ヒントと機能の相互運用性

  • クエリ ストア ヒントは、他のハードコーディングされたステートメント レベルのヒントとプラン ガイドをオーバーライドします。
  • クエリは常に実行され、エラーの原因になる相反するクエリ ストア ヒントは無視されます。
  • クエリ ストア ヒントが矛盾している場合、SQL Server によってクエリの実行がブロックされることはなく、クエリ ストア ヒントは適用されません。
  • 簡易パラメーター化 - クエリ ストア ヒントは、簡易パラメーター化の対象となるステートメントではサポートされていません。
  • 強制パラメーター化 - RECOMPILE ヒントは、データベース レベルで設定された強制パラメーター化と互換性がありません。 データベースに強制パラメーター化が設定されていて、RECOMPILE ヒントがクエリのクエリ ストアに設定されているヒント文字列の一部である場合、SQL Server で RECOMPILE ヒントは無視され、他のヒントが利用されていれば適用されます。
    • さらに、SQL Serverは、RECOMPILE ヒントが無視されたことを示す警告 (エラー コード 12461) を発行します。
    • 強制パラメーター化のユース ケースに関する考慮事項の詳細については、「強制パラメーター化使用のガイドライン」を参照してください。
  • 現在、クエリ ストア ヒントは、Always On 可用性グループのプライマリ レプリカに対して適用できます。

クエリ ストア ヒントのベスト プラクティス

  • 潜在的な新しいクエリ ストア ヒントについてクエリを評価する前に、インデックスと統計のメンテナンスを完了します。
  • クエリ ストア ヒントを利用する前に、アプリケーション データベースを最新の互換性レベルに関してテストします。
    • たとえば、パラメーター機密性の高いプラン (PSP) の最適化は、2022 SQL Server (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_textsys.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;

関連項目

次の手順