クエリ プランについて理解する

実行プランの詳細に進む前に、データベース オプティマイザーのしくみを理解することが不可欠です。 SQL Server では、コストベースのクエリ オプティマイザーを使用します。これは、使用されている列に対する統計と、クエリ プラン内の各操作の潜在的なインデックスに基づいて、複数のプランのコストを計算します。 この情報は、オプティマイザーが各プランの合計コストを決定するのに役立ちます。 複雑なクエリには数千の実行可能な実行プランを含めることができますが、オプティマイザーでは 1 つ 1 つごとに評価されるわけではありません。 代わりに、ヒューリスティックを使用して、適切なパフォーマンスを発揮する可能性が高いプランを特定し、評価されたプランから最も低いコストプランを選択します。

クエリ オプティマイザーはコストベースであるため、意思決定のために正確な入力を提供することが重要です。 SQL Server は統計に依存して列とインデックス内のデータの分布を追跡します。これらの統計は、最適でない実行プランが生成されないように最新の状態に保つ必要があります。 SQL Server では、テーブル内のデータの変更に応じて統計が自動的に更新されますが、データを急速に変更するには、より頻繁な更新が必要になる場合があります。 オプティマイザーは、データベースの互換性レベル、統計に基づく行の見積もり、使用可能なインデックスなど、プランを構築する際に多くの要因を考慮します。

ユーザーがデータベース エンジンにクエリを送信すると、次の処理が行われます。

1. クエリは適切な構文で解析され、正しい場合はデータベース オブジェクトの解析ツリーが生成されます。
2. 解析ツリーは、バインドのために Algebrizer と呼ばれるデータベース エンジン コンポーネントに入力されます。 この手順では、クエリ内の列とオブジェクトが存在することを検証し、処理されているデータ型を識別します。 出力はクエリ プロセッサ ツリーであり、次の手順の入力として機能します。
3. クエリの最適化は CPU を集中的に使用するため、データベース エンジンはプラン キャッシュと呼ばれる特別なメモリ領域に実行プランをキャッシュします。 クエリのプランが既に存在する場合は、キャッシュから取得されます。 キャッシュ内の各クエリには、 query_hashと呼ばれるクエリの T-SQL に基づいて生成されたハッシュ値があります。 エンジンは、現在のクエリの query_hash を生成し、プラン キャッシュ内の一致をチェックします。
4. プランが存在しない場合、クエリ オプティマイザーはコストベースのオプティマイザーを使用して、クエリで使用される列、テーブル、およびインデックスに関する統計に基づいて、いくつかの実行プラン オプションを生成します。 出力はクエリ実行プランです。
5. クエリは、プラン キャッシュの実行プランまたは前の手順で生成された新しいプランを使用して実行されます。 出力はクエリの結果です。

例を見てみましょう。 次のクエリがあるとします。

SELECT orderdate,
        AVG(salesAmount)
FROM FactResellerSales
WHERE ShipDate = '2013-07-07'
GROUP BY orderdate;

この例では、SQL Server は FactResellerSales テーブルに OrderDate、ShipDate、SalesAmount 列が存在するかどうかを確認します。 これらの列が存在する場合、SQL Server はクエリのハッシュ値を生成し、プラン キャッシュで一致するハッシュ値を調べます。 一致するハッシュ値が見つかった場合、エンジンはプランの再利用を試みます。 一致するハッシュ値が見つからない場合、SQL Server は OrderDate 列と ShipDate 列で使用可能な統計を調べます。

WHERE 列を参照する句は、このクエリの述語と呼ばれます。 ShipDate 列を含む非クラスター化インデックスがある場合、コストがクラスター化インデックスからデータを取得するよりもコストが低い場合、SQL Server はそれをプランに含める可能性があります。 次に、オプティマイザーは使用可能なオプションから最低コストプランを選択し、クエリを実行します。

クエリ プランは、一連の関係演算子を組み合わせてデータを取得し、推定行数などの情報をキャプチャします。 実行プランのもう 1 つの要素は、メモリ許可と呼ばれるデータの結合や並べ替えなどの操作に必要なメモリです。 メモリ許可では、統計の重要性が強調表示されます。 SQL Server が実際に 100 行を返したときに演算子が 10,000,000 行を返すと推定される場合、より大きなメモリ許可がクエリに割り当てられます。 メモリの許可が大きすぎると、2 つの問題が発生する可能性があります。 最初に、クエリで RESOURCE_SEMAPHORE 待機が発生し、SQL Server が大量のメモリを割り当てるのを待機中であることを示します。 SQL Server は、既定で、実行の前にクエリのコストの 25 倍 (秒単位) 待ちます (最大 24 時間)。 2 つ目は、クエリの実行時に使用可能なメモリが不足している場合、tempdb にスピルするため、メモリ内での操作よりも遅くなります。

実行プランには、データベース互換性レベル、並列処理の次数、クエリがパラメーター化された場合に指定されたパラメーターなど、クエリに関する他のメタデータも格納されます。

クエリ プランは、グラフィカル表現またはテキストベースの形式で表示できます。 テキスト ベースのオプションは、SET コマンドを使用して呼び出され、現在の接続にのみ適用されます。 これらのプランは、T-SQL クエリを実行できる任意の場所で表示できます。

ほとんどの DBA では、プランの 形状 を含め、プラン全体を表示できるため、グラフィカル プランが優先されます。 グラフィカル クエリ プランを表示および保存するには、いくつかの方法があります。 この目的で最も一般的なツールは、SQL Server Management Studio です。 さらに、グラフィカル実行プランの表示をサポートするサードパーティ製のツールもあります。

実行プランには 3 種類あります。

推定実行プラン

この種類の実行プランは、クエリ オプティマイザーによって生成されます。 クエリ メモリ許可のメタデータとサイズは、クエリのコンパイル時にデータベースに存在する統計からの見積もりに基づいています。 テキストベースの推定プランを表示するには、クエリを実行する前にコマンド SET SHOWPLAN_ALL ON を実行します。 クエリを実行すると、実行プランの手順が表示されますが、クエリは実行されないため、結果は表示されません。 SET オプションは、OFF に設定するまで有効なままです。

実際の実行プラン

この種類のプランは、見積もりプランと同じです。ただし、クエリの実行コンテキストも含まれます。 このコンテキストには、推定行数と実際の行数、実行警告、並列処理の実際の次数 (使用されるプロセッサの数)、実行中に使用された経過時間と CPU 時間が含まれます。 テキスト ベースの実際のプランを表示するには、クエリを実行する前にコマンド SET STATISTICS PROFILE ON を実行します。 クエリが実行され、プランと結果の両方が取得されます。

[ライブ クエリ統計]

このプラン表示オプションは、推定プランと実際のプランを組み合わせて、オペレーターによる実行の進行状況を表示するアニメーション化されたプランにします。 1 秒ごとに更新され、演算子を通過した実際の行数が示されます。 ライブ クエリ統計のもう 1 つの利点は、演算子から演算子への引き継ぎが表示されることです。これは、パフォーマンスの問題のトラブルシューティングに役立ちます。 この種類のプランはアニメーション化されているため、グラフィカルプランとしてのみ使用できます。