問題のあるクエリ プランを識別する

  • 15 分

クエリのパフォーマンスのトラブルシューティングを行うために、ほとんどの DBA は、最初に問題のあるクエリ (通常はシステム リソースを最も多く消費するクエリ) を識別し、次にそのクエリの実行プランを取得するやり方を取り入れています。 2 つのシナリオがあります。 1 つは、クエリのパフォーマンスが一貫して低い場合です。 パフォーマンスが一貫して低い場合の原因としては、ハードウェア リソースの制約 (通常、この状況は単独で実行される単一のクエリには影響しません)、最適でないクエリ構造、データベースの互換性設定、インデックスの欠落、クエリ オプティマイザーによる不適切なプランの選択など、いくつかの異なる問題が考えられます。 2 番目のシナリオは、特定の実行に対しては良好なクエリ パフォーマンスが得られるのにそれ以外ではパフォーマンスが低下する場合です。 この問題は、他のいくつかの要因が原因となって発生することがあります。最も一般的なものとして、特定の実行に対するプランが効率的である一方でその他の実行に対するプランが不適切な、パラメーター化されたクエリのデータ スキューがあります。 一貫性のないクエリ パフォーマンスのその他の一般的な要因には、ブロッキング (1 つのクエリがテーブルへのアクセスを取得するために別のクエリの完了を待っている状態) またはハードウェアの競合があります。

これらの潜在的な問題のそれぞれについてさらに詳しく見ていきましょう。

ハードウェア制約

通常、ハードウェアの制約は、1 回クエリを実行しただけではわかりませんが、運用環境の負荷がかかり、クエリ間で共有される CPU スレッドの数とメモリの量が限られていると、明らかになります。 CPU の競合が発生した場合、通常は、サーバーの CPU 使用率を測定するパフォーマンス モニター カウンターの "% プロセッサ時間" を観察することでこれを検出できます。 SQL Server を詳しく見ると、サーバーで CPU 負荷が発生しているときに SOS_SCHEDULER_YIELD と CXPACKET の待機の種類が表示されることがあります。 ただし、ストレージ システムのパフォーマンスが低い場合は、他の状況では最適化されるクエリの 1 回の実行であっても、処理速度が低下する可能性があります。 ストレージ システムのパフォーマンスは、I/O 操作の完了にかかる時間を測定するパフォーマンス モニター カウンターの "Disk Seconds/Read (ディスクの読み取り秒数)" および "Disk Seconds/Write (ディスクの書き込み秒数)" を使用してオペレーティング システム レベルで追跡するのが適切です。 SQL Server は、ストレージ パフォーマンスが低いことを検出すると (I/O の完了に 15 秒以上かかる場合)、エラー ログに書き込みます。 待機の統計を調べ、SQL Server の PAGEIOLATCH_SH 待機の割合が高くなっていれば、ストレージ システムのパフォーマンスに問題がある可能性があります。 通常、ハードウェアのパフォーマンスは、比較的簡単に評価できるため、パフォーマンスのトラブルシューティング プロセスの早い段階で大まかに調べることができます。

データベースのパフォーマンスの問題のほとんどは最適でないクエリ パターンに起因すると考えることができますが、多くの場合、非効率なクエリを実行するとハードウェアに過度の負荷がかかります。 たとえば、インデックスが欠落していると、クエリを処理するために必要以上のデータが取得されるため、CPU、ストレージ、およびメモリの負荷が高くなる可能性があります。 ハードウェアの問題に対処する前に、最適でないクエリに対処し、それらをチューニングすることをお勧めします。 次に、クエリのチューニングについて見ていきます。

最適でないクエリ コンストラクト

リレーショナル データベースは、セットベースの操作を実行するときに最高のパフォーマンスを発揮します。 セットベースの操作では、データ操作 (INSERT、UPDATE、DELETE、SELECT) がセット単位で実行されます。この場合、作業が値のセットに対して実行され、単一の値または結果セットのいずれかが生成されます。 セットベースの操作に代わるものは、カーソルまたは while ループを使用して行ベースの作業を実行する方法です。 この種の処理は行ベースの処理と呼ばれ、影響を受ける行の数に比例してコストが増加します。 この線形スケールは、アプリケーションのデータ量が増えるにつれて問題になります。

カーソルまたは WHILE ループを使用した行ベースの操作の最適でない使用を検出することは重要ですが、これ以外にも認識できるようにしておく必要がある SQL Server アンチパターンがあります。 テーブル値関数 (TVF) (特に複数ステートメントのテーブル値関数) が原因で、SQL Server 2017 より前の実行プラン パターンで問題が発生していました。 多くの開発者の間では、複数ステートメントのテーブル値関数を使用することが好まれています。なぜなら、1 つの関数内で複数のクエリを実行し、その結果を 1 つのテーブルに集計することができるからです。 ただし、T-SQL コードを記述するすべての人は、TVF を使用するとパフォーマンスが低下する可能性があることに注意する必要があります。

SQL Server には、インライン関数と複数ステートメントの 2 種類のテーブル値関数があります。 インライン TVF を使用した場合、データベース エンジンはこれをビューのように扱います。 複数ステートメントの TVF は、クエリを処理するときに別のテーブルと同じように扱われます。 TVF は動的であり、SQL Server にはそれに関する統計がないため、クエリ プランのコストを推定するときは固定の行数が使われました。 行数が少ない場合は固定の数で問題ありませんが、TVF から数千行または数百万行が返される場合は、実行プランが非効率的になる可能性があります。

もう 1 つのアンチパターンは、スカラー関数の使用でした。これには、同様の推定と実行に関する問題があります。 互換レベル 140 と 150 では、インテリジェント クエリ処理が導入されたことで、パフォーマンスが大幅に向上しています。

SARGability

リレーショナル データベースでの SARGable という用語は、インデックスを使ってクエリの実行速度を上げることができる、特定の形式の述語 (WHERE 句) を指します。 適切な形式の述語は、"検索引数" または SARG と呼ばれます。 SQL Server において、SARG を使用することは、オプティマイザーが、インデックス全体 (またはテーブル全体) をスキャンして値を取得する代わりに、SARG で参照されている列の非クラスター化インデックスを使用して SEEK 操作のための評価を行うことを意味します。

SARG が存在しても、SEEK にインデックスが使われることが保証されるわけではありません。 オプティマイザーのコスト計算アルゴリズムによって、インデックスのコストが高すぎると判断されることもあります。 これは、SARG がテーブル内の行の大部分を参照している場合に当てはまる可能性があります。 SARG がないということは、非クラスター化インデックスではオプティマイザーによって SEEK が評価されないことを意味します。

SARG ではない (非 SARGable (検索引数化可能) とも呼ばれます) 式の例としては、一致させる文字列の先頭にワイルドカードのある LIKE 句を含む式 (例: WHERE lastName LIKE ‘%SMITH%’) があります。 その他の SARG ではない述語は、列で関数を使うと発生します (例: WHERE CONVERT(CHAR(10), CreateDate,121) = ‘2020-03-22’)。 非 SARGable (検索引数化可能) な式を使用するこれらのクエリは、他の状況ではシークが発生する実行プランでインデックス スキャンまたはテーブル スキャンを調べることで識別されます。

Screenshot of query and execution plan using a non-SARGable function.

クエリの WHERE 句で使用されている City 列にインデックスがあり、これが上記の実行プランで使用されている間、インデックスがスキャンされていること (つまり、インデックス全体が読み取られていること) がわかります。 述語の LEFT 関数により、この式が非 SARGable (検索引数化可能) になります。 オプティマイザーは、City 列のインデックスに対するインデックス シークを使用した評価を行いません。

このクエリは、SARGable (検索引数化可能) である述語を使用するように記述できます。 次に、オプティマイザーは、City 列のインデックスに対する SEEK を評価します。 この場合、インデックス シーク操作では、次に示すように、はるかに小さい行のセットが読み取られます。

Screenshot of a query and execution plan with a SARGable Predicate.

LEFT 関数を LIKE に変更すると、インデックス シークが行われるようになります。

Note

この場合、LIKE キーワードの左側にはワイルドカードがないため、M で始まる都市が検索されます。これが "両側" の場合、またはワイルドカード ("%M%" または "%M") で始まる場合は、SARGable (検索引数化可能) ではなくなります。 シーク操作では、1,267 行、つまり非 SARGable (検索引数化可能) 述語を使用したクエリの推定値の約 15% が返されると推定されます。

他のいくつかのデータベース開発アンチパターンでは、データベースをデータ ストアではなくサービスとして扱います。 データベースを使用してデータを JSON に変換したり、文字列を操作したり、複雑な計算を実行したりすると、CPU が過剰に使用され、待ち時間が長くなる可能性があります。 すべてのレコードを取得してからデータベースでの計算の実行を試みるクエリは、IO と CPU の過剰な使用を招く可能性があります。 理想的には、データベースは、データ アクセス操作と、集計などの最適化されたデータベース コンストラクトに使用するべきです。

欠落したインデックス

データベース管理者である私たちが目にする最も一般的なパフォーマンスの問題の原因は、有用なインデックスがないため、エンジンがクエリの結果を返すために必要なページよりもはるかに多くのページを読み取ることにあります。 インデックスはリソースの観点からは無料ではありませんが (テーブルにインデックスを追加すると、書き込みのパフォーマンスが低下し、領域を消費する可能性があります)、実現されるパフォーマンスの向上によって、追加のリソース コストを何倍も相殺できます。 これらのパフォーマンスの問題が発生する頻度の高い実行プランは、クエリ演算子 Clustered Index Scan (クラスター化インデックス スキャン) または Nonclustered index seek (非クラスター化インデックスのシーク) と Key Lookup (キーの参照) (既存のインデックスに列がないことを示します) の組み合わせによって識別できます。

この問題の解決を支援するために、実行プランで欠落しているインデックスがデータベース エンジンから報告されます。 推奨されるインデックスの名前と詳細は、sys.dm_db_missing_index_details という名前の動的管理ビューで取得できます。 SQL Server には、sys.dm_db_index_usage_stats や sys.dm_db_index_operational_stats のような他の DMV もあり、既存のインデックスの使用率を強調表示します。

データベース内のどのクエリでも使用されていないインデックスを削除することは、合理的である場合もあります。 欠落しているインデックス DMV とプランの警告は、クエリをチューニングするための開始点としてのみ使用するべきです。 重要なクエリが何であるかを理解し、それらのクエリをサポートするインデックスを作成することが重要です。 相互のコンテキストでインデックスを評価せずに、欠落しているすべてのインデックスを作成することはお勧めしません。

不足している統計と古い統計

クエリ オプティマイザーにとっての列とインデックスの統計の重要性について学習しました。 統計が古くなる可能性のある条件と、この問題が SQL Server でどのように現れるかを理解することも重要です。 Azure SQL のオファリングでは、統計の自動更新は既定でオンに設定されます。 SQL Server 2016 より前の統計の自動更新機能の既定の動作では、インデックス内の列に対する変更の数がテーブル内の行数の約 20% になるまで統計が更新されませんでした。 この動作により、クエリのパフォーマンスを変更するのに十分な重要なデータ変更があっても、統計は更新されませんでした。 変更されたデータを含むテーブルを使用しているプランは、古い統計に基づくことから、最適ではないことが多くありました。

SQL Server 2016 より前では、トレース フラグ 2371 を使用するオプションがありました。これは、必要な変更の数を動的な値に変更するもので、その結果、テーブルのサイズが大きくなるにつれて、統計の更新をトリガーするために必要な行の変更の割合が小さくなりました。 SQL Server、Azure SQL Database、Azure SQL Managed Instance の新しいバージョンでは、この動作は既定でサポートされています。 また、sys.dm_db_stats_properties という動的管理関数もあります。これは、統計が最後に更新された日時と、前回の更新以降に行われた変更の数を示します。これにより、手動で更新する必要がある可能性のある統計をすばやく識別できます。

オプティマイザーの不適切な選択

クエリ オプティマイザーは、ほとんどのクエリを最適化するのに適しています。ただし、いくつかのエッジ ケースでは、コストベースのオプティマイザーが、十分に理解されない、影響力のある決定を行う可能性があります。 これに対処して安定した最適なクエリ プランに到達するには、クエリ ヒント、トレース フラグ、実行プランの強制、その他の調整の使用など、多くの方法があります。 Microsoft には、これらのシナリオのトラブルシューティングを支援するサポート チームが用意されています。

次の AdventureWorks2017 データベースの例では、クエリ ヒントを使用して、常に Seattle の都市名を使用するようにデータベース オプティマイザーに指示しています。 このヒントは、すべての都市の値に対して最適な実行プランを保証するものではありませんが、予測可能になります。 @city_name に対する値 "Seattle" は、最適化中にのみ使用されます。 実行中は、実際に指定された値 (‘Ascheim’) が使用されます。

DECLARE @city_name nvarchar(30) = 'Ascheim',
        @postal_code nvarchar(15) = 86171;

SELECT * 
FROM Person.Address
WHERE City = @city_name 
      AND PostalCode = @postal_code
OPTION (OPTIMIZE FOR (@city_name = 'Seattle');

例に示されているように、このクエリでは、ヒント (OPTION 句) を使用して、特定の変数値を使用して実行プランを作成するようにオプティマイザーに指示しています。

パラメーターを見つけ出す

SQL Server は、クエリ実行プランを将来の使用に備えてキャッシュします。 実行プランの取得プロセスはクエリのハッシュ値に基づいているため、使用するキャッシュされたプランのクエリのすべての実行においてクエリ テキストが同一である必要があります。 多くの開発者は、同じクエリで複数の値をサポートするために、次の例に示すようなストアド プロシージャを介して渡されるパラメーターを使用します。

CREATE PROC GetAccountID (@Param INT)
AS

<other statements in procedure>

SELECT accountid FROM CustomerSales WHERE sales > @Param;

<other statements in procedure>

RETURN;

-- Call the procedure:

EXEC GetAccountID 42;

クエリは、sp_executesql プロシージャを使用して明示的にパラメーター化することもできます。 ただし、個々のクエリの明示的なパラメーター化は、通常、PREPARE および EXECUTE のなんらかの形式 (API によって異なります) を使用してアプリケーションを介して行われます。 データベース エンジンは、そのクエリを初めて実行するときに、パラメーターの初期値 (この場合は 42) に基づいてクエリを最適化します。 パラメーター スニッフィングと呼ばれるこの動作により、サーバー上でクエリをコンパイルする全体的なワークロードが軽減されます。 ただし、データ スキューが発生している場合は、クエリのパフォーマンスが大きく異なる可能性があります。

たとえば、テーブルに 1,000 万件のレコードがあり、そのレコードのうちの 99% が ID として 1 を持ち、残りの 1% が一意の番号を持つ場合、パフォーマンスは、クエリを最適化するために最初に使用された ID に基づいて決まります。 この大きく変動するパフォーマンスは、データ スキューを示すものであり、パラメーター スニッフィングに固有の問題ではありません。 この動作は、非常に一般的なパフォーマンスの問題であり、注意する必要があります。 問題を軽減するためのオプションを理解しておく必要があります。 この問題に対処する方法はいくつかありますが、それぞれにトレードオフがあります。

  • クエリで RECOMPILE ヒントを使用するか、ストアド プロシージャで WITH RECOMPILE 実行オプションを使用します。 このヒントを使用すると、クエリまたはプロシージャは、実行のたびに再コンパイルされます。これにより、サーバーの CPU 使用率が増加しますが、常に最新のパラメーター値が使用されるようになります。
  • OPTIMIZE FOR UNKNOWN クエリ ヒントを使用できます。 このヒントを使用すると、オプティマイザーは、パラメーターをスニッフィングしないことを選択し、値を列データのヒストグラムと比較します。 このオプションを使用しても可能な限り最適なプランは得られませんが、一貫性のある実行プランが得られます。
  • 既知の問題のあるパラメーターに対してのみ RECOMPILE を実行するようにパラメーター値の周りにロジックを追加して、プロシージャまたはクエリを書き直す。 次の例では、SalesPersonID パラメーターが NULL の場合、クエリは OPTION (RECOMPILE) を使用して実行されます。
CREATE OR ALTER PROCEDURE GetSalesInfo (@SalesPersonID INT = NULL)
AS
DECLARE  @Recompile BIT = 0
         , @SQLString NVARCHAR(500)

SELECT @SQLString = N'SELECT SalesOrderId, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = @SalesPersonID'

IF @SalesPersonID IS NULL
BEGIN
     SET @Recompile = 1
END

IF @Recompile = 1
BEGIN
    SET @SQLString = @SQLString + N' OPTION(RECOMPILE)'
END

EXEC sp_executesql @SQLString
    ,N'@SalesPersonID INT'
    ,@SalesPersonID = @SalesPersonID
GO

上記の例は優れたソリューションですが、非常に大規模な開発作業が必要であり、データの分散について十分に理解している必要があります。 また、データの変更に応じてメンテナンスが必要になる場合もあります。