前へ

次へ

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

完了

実行プランの詳細について掘り下げる前に、データベース オプティマイザーの動作の基本を理解しておくと役立ちます。 SQL Server では、コストベースのクエリ オプティマイザーと呼ばれるものが使用されます。 クエリ オプティマイザーは、使用されている列に関する統計と、各クエリ プラン内の各操作に使用できる可能性のあるインデックスに基づいて、考えられる複数のプランのコストを計算します。 この情報に基づいて、各プランの総コストが算出されます。 一部の複雑なクエリには、何千もの実行プラン候補が存在する場合があります。 オプティマイザーは、考えられるすべてのプランを評価することはせず、ヒューリスティックを使用して、良好なパフォーマンスが得られる可能性が高いプランを特定します。 次に、オプティマイザーは、特定のクエリに対して評価されたすべてのプランの中から、最も低コストのプランを選択します。

クエリ オプティマイザーはコストベースであるため、意思決定のための適切な入力があることが重要です。 列およびインデックス内のデータの分布を追跡するために SQL Server によって使用される統計は、最新の状態に保たれている必要があります。そうでないと、最適でない実行プランが生成される可能性があります。 SQL Server は、テーブル内のデータが変更されると、その統計を自動的に更新します。ただし、急速に変化するデータに対しては、より頻繁な更新が必要になる場合があります。 エンジンでプランが作成されるときは、データベースの互換性レベル、統計に基づく行の推定値、使用可能なインデックスなど、多数の要素が使用されます。

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

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

注意

クエリ プロセッサの動作のしくみについて詳しくは、「クエリ処理アーキテクチャ ガイド」を参照してください

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

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

この例では、SQL Server は、FactResellerSales テーブルに OrderDate、ShipDate、および SalesAmount 列が存在するかどうかを確認します。 これらの列が存在する場合は、クエリのハッシュ値を生成し、プラン キャッシュに一致するハッシュ値があるかどうかを調べます。 ハッシュが一致するクエリのプランがある場合、エンジンはそのプランの再利用を試みます。 ハッシュが一致するプランがない場合は、OrderDate 列と ShipDate 列に関して使用できる統計を調べます。 ShipDate 列を参照する WHERE 句は、このクエリで述語と呼ばれるものです。 ShipDate 列を含む非クラスター化インデックスがある場合、クラスター化インデックスからデータを取得するよりもコストが低ければ、SQL Server がそれをプランに含める可能性が最も高くなります。 次に、オプティマイザーが、使用可能なプランの中で最も低コストのプランを選択し、クエリを実行します。

クエリ プランでは、一連の関係演算子を組み合わせてデータを取得し、推定される行数などのデータに関する情報も取得します。 実行プランのもう 1 つの要素は、データの結合や並べ替えなどの操作を実行するために必要なメモリです。 クエリで必要とされるメモリは、メモリ許可と呼ばれます。 メモリ許可は、統計の重要性を示す良い例です。 ある演算子によって 10,000,000 行が返されると SQL Server が想定している一方で、返されたのがわずか 100 行であった場合、過剰に多くのメモリがクエリに与えられることになります。 必要以上に大きいメモリ許可が原因で、2 つの問題が発生する可能性があります。 最初に、クエリで RESOURCE_SEMAPHORE 待機が発生する可能性があります。これは、SQL Server によって大量のメモリが割り当てられるのをクエリが待っていることを示します。 SQL Server は、既定で、実行の前にクエリのコストの 25 倍 (秒単位) 待ちます (最大 24 時間)。 次に、クエリの実行時に使用可能なメモリが十分にない場合、クエリは tempdb にスピルされます。この場合、処理速度がメモリ内での動作よりもはるかに低下します。

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

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

ほとんどの DBA は、プランをグラフィカルに表示することを好みます。グラフィカルなプランを使用すると、プランの 形状 と呼ばれるものを含め、プラン全体を簡単に確認できるためです。 グラフィカルなクエリ プランを表示して保存するには、いくつかの方法があります。 この目的で使用される最も一般的なツールは SQL Server Management Studio ですが、推定プランは Azure Data Studio でも表示できます。 また、グラフィカルな実行プランの表示をサポートするサードパーティ製のツールもあります。

表示できる実行プランには、3 つの異なる種類があります。

推定実行プラン

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

実際の実行プラン

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

[ライブ クエリ統計]

このプラン表示オプションでは、推定プランと実際のプランを組み合わせて、プラン内の演算子を通じて実行の進行状況が表示される、アニメーション化されたプランを作成します。 1 秒ごとに更新され、演算子を通過した実際の行数が示されます。 ライブ クエリ統計のもう 1 つのベネフィットは、演算子から演算子へのハンドオフが表示されることです。これは、一部のパフォーマンスの問題のトラブルシューティングに役立つ場合があります。 この種類のプランはアニメーション化されるため、グラフィカル プランとしてのみ使用できます。

続行