推定クエリ プランと実際のクエリ プランについて説明する

  • 13 分

実際の実行プランと推定実行プランは、混乱しやすいトピックです。 違いは、実際のプランには、推定プランでキャプチャされない実行時の統計が含まれていることです。 使用される演算子と実行順序は、ほぼすべての場合で推定プランと同じになります。 もう 1 つの考慮事項は、実際の実行プランをキャプチャするためにクエリを実行する必要があることです。これは、時間がかかる場合もあれば、不可能な場合もあります。 たとえば、クエリが、1 回だけ実行できる UPDATE ステートメントである場合があります。 ただし、クエリ結果とプランを表示する必要がある場合は、実際のプランのオプションのいずれかを使用する必要があります。

Screenshot of an estimated execution plan generated in SQL Server Management Studio.

上に示したように、SSMS で推定プランを生成するには、推定クエリ プラン ボックスで示されているボタンをクリックします (または Ctrl + L キーボード コマンドを使用します)。 実際のプランを生成するには、表示されているアイコン (またはキーボード コマンド Control + M を使用) をクリックし、クエリを実行します。 2 つのオプション ボタンの動作は少し異なります。 [推定クエリ プランを含める] ボタンは、[実際のクエリ プランを含める] ボタンとは異なり、強調表示されている任意のクエリ (または、何も強調表示されていない場合はワークスペース全体) に直ちに応答します。

クエリの実行と推定実行プランの生成の両方にオーバーヘッドがあるため、運用環境では実行プランの表示を慎重に行う必要があります。

通常は、クエリの作成中に推定実行プランを使用して、そのパフォーマンス特性を理解したり、不足しているインデックスを識別したり、クエリの異常を検出したりできます。 実際の実行プランは、クエリの実行時のパフォーマンスに加え、最も重要なことである、クエリ オプティマイザーが使用可能なデータに基づいて最適でない選択を行う原因となる統計データのギャップを理解するのに最も適しています。

クエリ プランを読む

実行プランには、クエリを満たすために必要なデータを取得している間にデータベース エンジンが実行しているタスクが示されます。 それでは、プランを詳しく見ていきましょう。

まず、クエリ自体を次に示します。

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[QuantityonHand]

FROM [Warehouse].[StockItems] s
 JOIN [Warehouse].[StockItemHoldings] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox;

このクエリでは、StockItems テーブルを、StockItemID 列の値が等しい StockItemHoldings テーブルに結合しています。 データベース エンジンは、クエリの残りの部分を処理する前に、まずこれらの行を識別する必要があります。

Screenshot of a query execution plan.

プランの各アイコンは、実行プランを構成するさまざまなアクションと決定を表す特定の操作を示します。 SQL Server データベース エンジンには、実行プランを構成できる 100 を超えるクエリ演算子があります。 各演算子アイコンの下には、クエリの総コストに対するコストの割合が表示されます。 コストが 0% と表示されている操作でも、ある程度のコストが発生します。 実際、0% は、たいてい丸め処理によるものです。これは、グラフィカルなプラン コストが常に整数として表示されるのに対し、実際の割合は 0.5% 未満であるためです。

実行プラン内での実行のフローは、右から左、上から下の順になります。したがって、上記のプランでは、StockItemHoldings.PK_Warehouse_StockItemHoldings クラスター化インデックスに対するクラスター化インデックス スキャン操作が、クエリでの最初の操作です。 演算子を接続する線の幅は、次の演算子に送られるデータの推定行数に基づきます。 太い矢印は、演算子から演算子への大規模な転送を示すインジケーターであり、クエリをチューニングする機会を示している可能性があります。 また、マウスを演算子に合わせると、次に示すように追加情報がヒントに表示されます。

Screenshot of a tooltip for the Clustered Index Scan operation on the StockItems table.

ヒントには、推定プランのコストと推定値が強調表示されます。実際のプランの場合は、実際の行とコストとの比較が含まれます。 各演算子には、ヒントよりも多くの情報が表示されるプロパティが用意されています。 特定の演算子を右クリックし、コンテキスト メニューから [プロパティ] オプションを選択すると、プロパティの完全な一覧が表示されます。 このオプションを選択すると、SQL Server Management Studio に別個の [プロパティ] ペインが開きます。これは、既定で右側に表示されます。 [プロパティ] ペインが開いたら、任意の演算子をクリックすると、その演算子のプロパティがプロパティの一覧に設定されます。 [プロパティ] ペインは、SQL Server Management Studio のメイン メニューの [表示] をクリックし、[プロパティ] を選択して開くこともできます。

Screenshot of the properties for the operator.

[プロパティ] ペインには、いくつかの追加情報が含まれ、次の演算子に渡される列の詳細を含む出力リストが表示されます。 クラスター化インデックス スキャンを使用して分析された場合、これらの列は、クエリ パフォーマンスを向上させるために非クラスター化インデックスが必要であることを示している可能性があります。 クラスター化インデックス スキャン操作ではテーブル全体が読み取られるため、このシナリオでは、各テーブルの StockItemID 列の非クラスター化インデックスの方が効率的である可能性があります。

軽量クエリ プロファイリング

前述のように、実際の実行プランをキャプチャするには、SSMS を使用するか、拡張イベント監視インフラストラクチャを使用するかにかかわらず、大きなオーバーヘッドが発生する可能性があるため、通常は、ライブ サイトのトラブルシューティング作業でのみこれを実行します。 オブザーバーのオーバーヘッドは、知られているように、実行中のアプリケーションを監視するためのコストです。 シナリオによってはこのコストが CPU 使用率の数パーセント程度にしかならない場合もありますが、実際の実行プランをキャプチャする場合など、個々のクエリのパフォーマンスが大幅に低下する可能性があります。 SQL Server のエンジンのレガシ プロファイル インフラストラクチャでは、クエリ情報をキャプチャするために最大 75% のオーバーヘッドが生じる可能性があります。これに対し、軽量プロファイル インフラストラクチャでは、オーバーヘッドは最大で約 2% になります。

軽量プロファイルの最初のバージョンでは、行数と I/O 使用率情報 (特定のクエリを満たすためにデータベース エンジンによって実行された論理および物理読み取りと書き込みの数) が収集されました。 さらに、query_thread_profile と呼ばれる新しい拡張イベントが導入され、クエリ プランの各演算子からのデータを検査できるようになりました。 軽量プロファイルの初期バージョンでは、この機能を使用するためにトレース フラグ 7412 をグローバルに有効にする必要があります。

新しいリリース (SQL Server 2016 SP2 CU3、SQL Server 2017 CU11、および SQL Server 2019) では、軽量プロファイルがグローバルに有効になっていない場合は、USE HINT クエリ ヒントを QUERY_PLAN_PROFILE と共に使用して、クエリ レベルで軽量プロファイルを有効にすることができます。 このヒントを含むクエリが実行を完了すると、query_plan_profile 拡張イベントが生成され、実際の実行プランが提供されます。 このヒントを含むクエリの例を示します。

SELECT [stockItemName]
 ,[UnitPrice] * [QuantityPerOuter] AS CostPerOuterBox
 ,[ QuantityonHand]
FROM [Warehouse].[StockItems] s
    JOIN [Warehouse].[StockItems] sh ON s.StockItemID = sh.StockItemID
ORDER BY CostPerOuterBox 
OPTION(USE HINT ('QUERY_PLAN_PROFILE'));

最後のクエリ プランの統計

SQL Server 2019 と Azure SQL Database では、クエリ プロファイル インフラストラクチャに対する 2 つの拡張機能をさらにサポートしています。 最初に、SQL Server 2019 と Azure SQL Database およびマネージド インスタンスの両方で軽量プロファイルが既定で有効になっています。 軽量プロファイルは、LIGHTWEIGHT_QUERY_PROFILING と呼ばれるデータベース スコープ構成オプションとしても使用できます。 データベース スコープ オプションを使用すると、互いに独立している任意のユーザー データベースの機能を無効にできます。

2 番目に、特定のプラン ハンドルの最後の既知の実際のクエリ実行プランを表示する、sys.dm_exec_query_plan_stats という名前の新しい動的管理関数があります。 関数を介して最後の既知の実際のクエリ プランを表示するために、サーバー全体でトレース フラグ 2451 を有効にすることができます。 または、LAST_QUERY_PLAN_STATS というデータベース スコープ構成オプションを使用してこの機能を有効にすることもできます。

次に示すように、この関数を他のオブジェクトと組み合わせて、すべてのキャッシュされたクエリの最後の実行プランを取得できます。

SELECT *
FROM sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
    CROSS APPLY sys.dm_exec_query_plan_stats(plan_handle) AS qps; 
GO

この機能を使用すると、オーバーヘッドを最小限に抑えながら、システム内の任意のクエリを最後に実行したときの実行時の統計をすばやく識別できます。 次の画像は、プランを取得する方法を示しています。 結果の最初の列となる実行プラン XML を選択すると、下の 2 番目の画像に示されている実行プランが表示されます。

Screenshot of retrieving the actual execution plan for a query.

下の "列ストア インデックス スキャン" のプロパティからわかるように、キャッシュから取得されたプランには、クエリで取得された実際の行数が含まれます。

Screenshot of the execution plan retrieved showing the cache has actual number of rows retrieved in the query.