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

  • 13 分

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

SQL Server Management Studio で生成された推定実行プランのスクリーンショット。

次に示すように、SSMS で推定プランを生成するには、推定クエリ プラン ボックスで示されているボタンを選択します (または、キーボード コマンド Control + 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 テーブルに結合しています。 データベース エンジンは、クエリの残りの部分を処理する前に、まずこれらの行を識別する必要があります。

クエリ実行プランのスクリーンショット。

プラン内の各アイコンは、実行プランを構成するさまざまなアクションと決定に対応する特定の操作を表します。 SQL Server データベース エンジンには、実行プランの一部として使用できるクエリ演算子が 100 を超えます。 各演算子アイコンの下には、クエリの合計コストに対するコストの割合があります。 コストが 0% を示す操作であっても、コストは引き続き発生します。 実際には、0% は丸めによるものです。グラフィカルな計画コストは常に整数として表示され、実際のパーセンテージは0.5%未満です。

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

StockItems テーブルに対するクラスター化インデックス スキャン操作のヒントのスクリーンショット。

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

演算子のプロパティのスクリーンショット。

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

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

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

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

軽量プロファイリングがグローバルに有効になっていない場合は、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 とマネージド インスタンスの両方で既定で有効になっています。 軽量プロファイルは、LIGHTWEIGHT_QUERY_PROFILING と呼ばれるデータベース スコープ構成オプションとしても使用できます。 データベース スコープ オプションを使用すると、互いに独立している任意のユーザー データベースの機能を無効にできます。

また、 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 番目の図に示す実行プランが表示されます。

クエリの実際の実行プランの取得のスクリーンショット。

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

キャッシュにクエリで取得された実際の行数があることを示す、取得された実行プランのスクリーンショット。