SQL Serverでの実行時間の遅いクエリのトラブルシューティング

[アーティクル]
06/20/2023

元の製品バージョン: SQL Server
元の KB 番号: 243589

概要

この記事では、SQL Serverを使用するときにデータベースアプリケーションで発生する可能性があるパフォーマンスの問題を処理する方法について説明します。特定のクエリまたはクエリのグループのパフォーマンスが低下します。次の方法は、低速クエリの問題の原因を絞り込み、解決に向けて指示するのに役立ちます。

低速クエリを検索する

SQL Server インスタンスに対してクエリパフォーマンスの問題があることを確認するには、まず、実行時間 (経過時間) によってクエリを調べます。確立されたパフォーマンスベースラインに基づいて設定したしきい値 (ミリ秒) を超えているかどうかを確認します。たとえば、ストレステスト環境では、ワークロードのしきい値を 300 ミリ秒以下に設定し、このしきい値を使用できます。その後、個々のクエリとその事前に確立されたパフォーマンスベースライン期間に焦点を当てて、そのしきい値を超えるすべてのクエリを特定できます。最終的には、ビジネスユーザーはデータベースクエリの全体的な期間を気にします。したがって、メインフォーカスは実行時間です。 CPU 時間や論理読み取りなどの他のメトリックが収集され、調査の絞り込みにも役立ちます。

現在実行中のステートメントの場合は、sys.dm_exec_requests内のtotal_elapsed_time列とcpu_time列をチェックします。次のクエリを実行してデータを取得します。

SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

クエリの過去の実行については、sys.dm_exec_query_statsの列last_elapsed_timeとlast_worker_timeをチェックします。次のクエリを実行してデータを取得します。

SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time,
     qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
     qs.total_logical_writes / qs.execution_count AS avg_writes,
     (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

注:

負の値を示す場合 avg_wait_time は、並列クエリです。

SQL Server Management Studio (SSMS) または Azure Data Studio でオンデマンドでクエリを実行できる場合は、SET STATISTICS TIMEON と SET STATISTICS IOON を使用してクエリを実行します。

SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

次に、[ メッセージ] から、CPU 時間、経過時間、論理読み取りが次のように表示されます。

  Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

クエリプランを収集できる場合は、[実行プラン] プロパティからデータをチェックします。
1. [実際の実行プランを含める] をオンにしてクエリを実行します。
2. [実行プラン] から左端の演算子を選択します。
3. [プロパティ] で、[QueryTimeStats] プロパティを展開します。
4. ElapsedTime と CpuTime を確認します。

実行中と待機中: クエリの速度が低下する理由

定義済みのしきい値を超えるクエリが見つかる場合は、低速になる理由を調べます。パフォーマンスの問題の原因は、実行中または待機中の 2 つのカテゴリにグループ化できます。

待機中: クエリはボトルネックを長時間待機しているため、低速になる可能性があります。待機の種類のボトルネックの詳細な一覧を参照してください。
RUNNING: クエリが長時間実行 (実行) しているため、クエリの速度が低下する可能性があります。つまり、これらのクエリは CPU リソースを積極的に使用しています。

クエリは一定期間実行され、有効期間 (期間) でしばらく待機している可能性があります。ただし、長い経過時間に影響を与える主要なカテゴリを特定することに焦点を当てます。したがって、最初のタスクは、クエリがどのカテゴリに該当するかを確立することです。単純です。クエリが実行されていない場合は、待機しています。理想的には、クエリは経過時間の大部分を実行中の状態に費やし、リソースを待つ時間がほとんどないのが理想的です。また、最良のシナリオでは、クエリは事前に定義されたベースライン内または下位で実行されます。クエリの経過時間と CPU 時間を比較して、問題の種類を特定します。

型 1: CPU バインド (ランナー)

CPU 時間が経過時間に近い、等しい、またはそれより長い場合は、CPU バインドクエリとして扱うことができます。たとえば、経過時間が 3000 ミリ秒 (ms) で、CPU 時間が 2900 ミリ秒の場合、経過時間の大部分が CPU に費やされます。その後、CPU バインドクエリと言うことができます。

実行 (CPU バインド) クエリの例:

経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
3200	3000	300000
1080	1000	20

論理読み取り ( キャッシュ内のデータ/インデックスページの読み取り) は、SQL Serverにおける CPU 使用率の最も高い要因です。 CPU の使用は、while ループ (T-SQL や XProcs や SQL CRL オブジェクトなどの他のコード) の他のソースから取得される場合があります。表の 2 番目の例は、CPU の大部分が読み取りからのものではありませんが、このようなシナリオを示しています。

注:

CPU 時間が期間を超える場合は、並列クエリが実行されていることを示します。複数のスレッドが同時に CPU を使用しています。詳細については、「並列クエリ - ランナーまたはウェイター」を参照してください。

タイプ 2: ボトルネックを待機している (ウェイター)

経過時間が CPU 時間を大幅に超える場合、クエリはボトルネックを待機しています。経過時間には、CPU でクエリを実行する時間 (CPU 時間) と、リソースが解放されるのを待つ時間 (待機時間) が含まれます。たとえば、経過時間が 2000 ミリ秒で CPU 時間が 300 ミリ秒の場合、待機時間は 1700 ミリ秒 (2000 - 300 = 1700) です。詳細については、「待機の種類」を参照してください。

待機クエリの例:

経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
2000	300	28000
10080	700	80000

並列クエリ - ランナーまたはウェイター

並列クエリでは、全体的な期間よりも多くの CPU 時間が使用される場合があります。並列処理の目的は、複数のスレッドがクエリの一部を同時に実行できるようにすることです。 1 秒のクロック時間で、クエリでは 8 つの並列スレッドを実行することで 8 秒の CPU 時間を使用できます。そのため、経過時間と CPU 時間差に基づいて CPU バインドまたは待機クエリを決定することが困難になります。ただし、一般的な規則として、上記の 2 つのセクションに記載されている原則に従ってください。概要は次のとおりです。

経過時間が CPU 時間よりもはるかに長い場合は、ウェイターと見なします。
CPU 時間が経過時間よりもはるかに長い場合は、ランナーと見なします。

並列クエリの例:

経過時間 (ms)	CPU 時間 (ミリ秒)	読み取り (論理)
1200	8100	850000
3080	12300	1500000

手法の高レベルの視覚的表現

このスクリーンショットは、低速クエリのトラブルシューティング方法の概要を視覚的に示しています。

待機中のクエリを診断して解決する

関心のあるクエリがウェイターであることを確認した場合、次の手順はボトルネックの問題の解決に焦点を当てることです。それ以外の場合は、「手順 4: 実行中のクエリを診断して解決する」に進みます。

ボトルネックを待機しているクエリを最適化するには、待機の時間とボトルネックの場所 (待機の種類) を特定します。待機の種類が確認されたら、待機時間を短縮するか、待機を完全に排除します。

おおよその待機時間を計算するには、クエリの経過時間から CPU 時間 (ワーカー時間) を減算します。通常、CPU 時間は実際の実行時間であり、クエリの有効期間の残りの部分は待機しています。

おおよその待機時間を計算する方法の例:

経過時間 (ms)	CPU 時間 (ミリ秒)	待機時間 (ミリ秒)
3200	3000	200
7080	1000	6080

ボトルネックの特定または待機

待機時間の長いクエリの履歴 (たとえば、経過時間全体の 20% が待機時間) を特定するには、 >次のクエリを実行します。このクエリでは、SQL Serverの開始以降、キャッシュされたクエリプランのパフォーマンス統計が使用されます。

SELECT t.text,
         qs.total_elapsed_time / qs.execution_count
         AS avg_elapsed_time,
         qs.total_worker_time / qs.execution_count
         AS avg_cpu_time,
         (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
         AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count
         AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count
         AS avg_writes,
         qs.total_elapsed_time
         AS cumulative_elapsed_time
FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
         > 0.2
ORDER BY qs.total_elapsed_time / qs.execution_count DESC

待機時間が 500 ミリ秒を超える現在実行中のクエリを特定するには、次のクエリを実行します。

SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
FROM sys.dm_exec_requests r 
   JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
WHERE wait_time > 500
AND is_user_process = 1

クエリプランを収集できる場合は、SSMS の実行プランプロパティから WaitStats をチェックします。
1. [実際の実行プランを含める] をオンにしてクエリを実行します。
2. [ 実行プラン ] タブで、左端の演算子を右クリックします
3. [ プロパティ] を選択し、[ WaitStats ] プロパティを選択します。
4. WaitTimeMs と WaitType を確認します。
PSSDiag/SQLdiag または SQL LogScout LightPerf/GeneralPerf シナリオに精通している場合は、どちらかを使用してパフォーマンス統計を収集し、SQL Server インスタンスで待機中のクエリを特定することを検討してください。収集されたデータファイルをインポートし、 SQL Nexus を使用してパフォーマンスデータを分析できます。

待機の排除または削減に役立つ参照

待機の種類ごとに原因と解決策が異なります。すべての待機の種類を解決する一般的な方法はありません。一般的な待機の種類に関する問題のトラブルシューティングと解決に関する記事を次に示します。

多くの待機の種類とそれらが示す内容の説明については、「待機の種類」の表を参照してください。

実行中のクエリを診断して解決する

CPU (worker) 時間が全体的な経過時間に非常に近い場合、クエリは有効期間の大部分を実行に費やします。通常、SQL Server エンジンが高い CPU 使用率を駆動する場合、CPU 使用率が高いのは、多数の論理読み取り (最も一般的な理由) を駆動するクエリから発生します。

現在、高い CPU アクティビティの原因となっているクエリを特定するために、次のステートメントを実行します。

SELECT TOP 10 s.session_id,
           r.status,
           r.cpu_time,
           r.logical_reads,
           r.reads,
           r.writes,
           r.total_elapsed_time / (1000 * 60) 'Elaps M',
           SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
           ((CASE r.statement_end_offset
                WHEN -1 THEN DATALENGTH(st.TEXT)
                ELSE r.statement_end_offset
            END - r.statement_start_offset) / 2) + 1) AS statement_text,
           COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
           + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
           r.command,
           s.login_name,
           s.host_name,
           s.program_name,
           s.last_request_end_time,
           s.login_time,
           r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

現時点でクエリが CPU を駆動していない場合は、以下のステートメントを実行して、CPUに拘束される過去のクエリを検索することができます。

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

実行時間の長い CPU バインドクエリを解決するための一般的なメソッド

クエリのクエリプランを調べる
統計の更新
不足しているインデックスを特定して適用します。不足しているインデックスを特定する方法の詳細については、「インデックス候補が見つからない非クラスター化インデックスをチューニングする」を参照してください。
クエリの再設計または書き換え
パラメーターに依存するプランを特定して解決する
SARG 機能の問題を特定して解決する
実行時間の長い入れ子になったループが TOP、EXISTS、IN、FAST、SET ROWCOUNT、OPTION (FAST N) によって発生する可能性がある行目標の問題を特定して解決します。詳細については、「Row Goals Gone Rogue と Showplan の機能強化 - Row Goal EstimateRowsWithoutRowGoal」を参照してください。
カーディナリティ推定の問題を評価して解決します。詳細については、「SQL Server 2012 以前から 2014 以降へのアップグレード後のクエリパフォーマンスの低下」を参照してください。
完了していないと思われる量子を特定して解決する」を参照してくださいSQL Server
オプティマイザーのタイムアウトの影響を受ける低速クエリを特定して解決する
CPU パフォーマンスの高い問題を特定します。詳細については、「SQL Serverでの CPU 使用率の高い問題のトラブルシューティング」を参照してください。
2 つのサーバー間のパフォーマンスの大きな違いを示すクエリのトラブルシューティング
システム上のコンピューティングリソースを増やす (CPU)
狭いプランとワイドプランでの UPDATE パフォーマンスの問題のトラブルシューティング

SQL Serverでの実行時間の遅いクエリのトラブルシューティング

概要

低速クエリを検索する

実行中と待機中: クエリの速度が低下する理由

型 1: CPU バインド (ランナー)

タイプ 2: ボトルネックを待機している (ウェイター)

並列クエリ - ランナーまたはウェイター

手法の高レベルの視覚的表現

待機中のクエリを診断して解決する

ボトルネックの特定または待機

待機の排除または削減に役立つ参照

実行中のクエリを診断して解決する

実行時間の長い CPU バインドクエリを解決するための一般的なメソッド

推奨されるリソース

フィードバック

フィードバック

その他のリソース

SQL Serverでの実行時間の遅いクエリのトラブルシューティング

概要

低速クエリを検索する

実行中と待機中: クエリの速度が低下する理由

型 1: CPU バインド (ランナー)

タイプ 2: ボトルネックを待機している (ウェイター)

並列クエリ - ランナーまたはウェイター

手法の高レベルの視覚的表現

待機中のクエリを診断して解決する

ボトルネックの特定または待機

待機の排除または削減に役立つ参照

実行中のクエリを診断して解決する

実行時間の長い CPU バインド クエリを解決するための一般的なメソッド

推奨されるリソース

フィードバック

フィードバック

その他のリソース

実行時間の長い CPU バインドクエリを解決するための一般的なメソッド