SQL Serverでの実行時間の遅いクエリのトラブルシューティング
元の製品バージョン: 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 TIME
ON
と 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.
クエリ プランを収集できる場合は、[実行プラン] プロパティからデータをチェックします。
[実際の実行プランを含める] をオンにしてクエリを実行します。
[実行プラン] から左端の演算子を選択します。
[プロパティ] で、[QueryTimeStats] プロパティを展開します。
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 をチェックします。
- [実際の実行プランを含める] をオンにしてクエリを実行します。
- [ 実行プラン ] タブで、左端の演算子を右クリックします
- [ プロパティ] を選択し、[ WaitStats ] プロパティを選択します。
- WaitTimeMs と WaitType を確認します。
PSSDiag/SQLdiag または SQL LogScout LightPerf/GeneralPerf シナリオに精通している場合は、どちらかを使用してパフォーマンス統計を収集し、SQL Server インスタンスで待機中のクエリを特定することを検討してください。 収集されたデータ ファイルをインポートし、 SQL Nexus を使用してパフォーマンス データを分析できます。
待機の排除または削減に役立つ参照
待機の種類ごとに原因と解決策が異なります。 すべての待機の種類を解決する一般的な方法はありません。 一般的な待機の種類に関する問題のトラブルシューティングと解決に関する記事を次に示します。
- ブロックの問題を理解して解決する (LCK_M_*)
- データベース のブロックに関する問題Azure SQL理解して解決する
- I/O の問題 (PAGEIOLATCH_*、WRITELOG、IO_COMPLETION、BACKUPIO) によって発生するSQL Serverパフォーマンスの低下のトラブルシューティング
- SQL Serverの最終ページ挿入PAGELATCH_EX競合を解決する
- メモリ許可の説明と解決策 (RESOURCE_SEMAPHORE)
- 待機の種類ASYNC_NETWORK_IO起因する低速クエリのトラブルシューティング
- Always On 可用性グループを使用した高HADR_SYNC_COMMIT待機の種類のトラブルシューティング
- 動作方法: CMEMTHREAD とデバッグ
- 並列処理待機を実行可能にする (CXPACKET と CXCONSUMER)
- THREADPOOL 待機
多くの待機の種類とそれらが示す内容の説明については、「 待機の種類」の表を参照してください。
実行中のクエリを診断して解決する
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 パフォーマンスの問題のトラブルシューティング
推奨されるリソース
フィードバック
https://aka.ms/ContentUserFeedback」を参照してください。
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「フィードバックの送信と表示