専用 SQL プールでの遅いクエリのトラブルシューティング

適用対象:Azure Synapse Analytics

この記事は、Azure Synapse Analytics 専用 SQL プールでのクエリに関する一般的なパフォーマンスの問題の理由を特定し、軽減策を適用するのに役立ちます。

手順に従って問題をトラブルシューティングするか、Azure Data Studio を使用してノートブックの手順を実行します。 最初の 3 つの手順では、クエリのライフサイクルを説明するテレメトリの収集について説明します。 記事の最後にある参照は、収集されたデータで見つかった可能性のある機会を分析するのに役立ちます。

Note

このノートブックを開く前に、Azure Data Studio がローカル コンピューターにインストールされていることを確認してください。 インストールするには、「 Azure Data Studio をインストールする方法を学びます。

Azure Data Studio でノートブックを開く

重要

報告されるパフォーマンスの問題のほとんどは、次の原因で発生します。

• 古い統計
• 異常なクラスター化列ストア インデックス (CCI)

トラブルシューティングの時間を節約するには、統計が作成され、最新の状態に保たれCCI が再構築されていることを確認します。

手順 1: request_id (別名 QID) を特定する

低速クエリの潜在的な理由を調査するには、低速クエリの request_id が必要です。 トラブルシューティングするクエリを識別するための開始点として、次のスクリプトを使用します。 低速クエリが特定されたら、 request_id 値をメモします。

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE [status] NOT IN ('Completed','Failed','Cancelled')
AND session_id <> session_id()
-- AND [label] = '<YourLabel>'
-- AND resource_allocation_percentage is not NULL
ORDER BY submit_time DESC;

-- Find top 10 longest running queries
SELECT TOP 10 *
FROM sys.dm_pdw_exec_requests
ORDER BY total_elapsed_time DESC;

低速クエリをより適切にターゲットにするには、スクリプトを実行するときに次のヒントを使用します。

• 結果セットの先頭に実行時間が最も長いクエリが存在するように、 submit_time DESC または total_elapsed_time DESC で並べ替えます。

• クエリで OPTION(LABEL='<YourLabel>') を使用し、 label 列をフィルター処理して識別します。

• ターゲット ステートメントがバッチに含まれていることがわかっている場合は、 resource_allocation_percentage の値を持たない QID を除外することを検討してください。

  注: 他のセッションによってブロックされている一部のクエリも除外される可能性があるため、このフィルターには注意してください。

手順 2: クエリに時間がかかっている場所を特定する

次のスクリプトを実行して、クエリのパフォーマンスの問題を引き起こす可能性がある手順を見つけます。 スクリプト内の変数を、次の表に示す値で更新します。 分散プランの全体像を把握するには、 @ShowActiveOnly の値を 0 に変更します。 結果セットから特定された低速ステップの StepIndex、 Phase、および Description の値を書き留めます。

パラメーター	説明
@QID	Step 1 で取得したrequest_id値
@ShowActiveOnly	0 - クエリのすべてのステップを表示する 1 - 現在アクティブなステップのみを表示する

DECLARE @QID VARCHAR(16) = '<request_id>', @ShowActiveOnly BIT = 1; 
-- Retrieve session_id of QID
DECLARE @session_id VARCHAR(16) = (SELECT session_id FROM sys.dm_pdw_exec_requests WHERE request_id = @QID);
-- Blocked by Compilation or Resource Allocation (Concurrency)
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked waiting on '
       + MAX(CASE WHEN waiting.type = 'CompilationConcurrencyResourceType' THEN 'Compilation Concurrency'
                  WHEN waiting.type LIKE 'Shared-%' THEN ''
                  ELSE 'Resource Allocation (Concurrency)' END)
       + MAX(CASE WHEN waiting.type LIKE 'Shared-%' THEN ' for ' + REPLACE(waiting.type, 'Shared-', '')
             ELSE '' END) AS [Description],
   MAX(waiting.request_time) AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, MAX(waiting.request_time), GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount], NULL AS [TSQL]
FROM sys.dm_pdw_waits waiting
WHERE waiting.session_id = @session_id
   AND ([type] LIKE 'Shared-%' OR
      [type] in ('ConcurrencyResourceType', 'UserConcurrencyResourceType', 'CompilationConcurrencyResourceType'))
   AND [state] = 'Queued'
GROUP BY session_id 
-- Blocked by another query
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, -1 AS [StepIndex], 'Compilation' AS [Phase],
   'Blocked by ' + blocking.session_id + ':' + blocking.request_id + ' when requesting ' + waiting.type + ' on '
   + QUOTENAME(waiting.object_type) + waiting.object_name AS [Description],
   waiting.request_time AS [StartTime], GETDATE() AS [EndTime],
   DATEDIFF(ms, waiting.request_time, GETDATE())/1000.0 AS [Duration],
   NULL AS [Status], NULL AS [EstimatedRowCount], NULL AS [ActualRowCount],
   COALESCE(blocking_exec_request.command, blocking_exec_request.command2) AS [TSQL]
FROM sys.dm_pdw_waits waiting
   INNER JOIN sys.dm_pdw_waits blocking
      ON waiting.object_type = blocking.object_type
      AND waiting.object_name = blocking.object_name
   INNER JOIN sys.dm_pdw_exec_requests blocking_exec_request
      ON blocking.request_id = blocking_exec_request.request_id
WHERE waiting.session_id = @session_id AND waiting.state = 'Queued'
   AND blocking.state = 'Granted' AND waiting.type != 'Shared' 
-- Request Steps
UNION ALL
SELECT @session_id AS session_id, @QID AS request_id, step_index AS [StepIndex],
       'Execution' AS [Phase], operation_type + ' (' + location_type + ')' AS [Description],
       start_time AS [StartTime], end_time AS [EndTime],
       total_elapsed_time/1000.0 AS [Duration], [status] AS [Status],
       CASE WHEN estimated_rows > -1 THEN estimated_rows END AS [EstimatedRowCount],
       CASE WHEN row_count > -1 THEN row_count END AS [ActualRowCount],
       command AS [TSQL]
FROM sys.dm_pdw_request_steps
WHERE request_id = @QID
   AND [status] = CASE @ShowActiveOnly WHEN 1 THEN 'Running' ELSE [status] END
ORDER BY StepIndex;

手順 3: ステップの詳細を確認する

次のスクリプトを実行して、前の手順で特定した手順の詳細を確認します。 スクリプト内の変数を、次の表に示す値で更新します。 すべての分布のタイミングを比較するには、 @ShowActiveOnly の値を 0 に変更します。 パフォーマンスの問題の原因となる可能性があるディストリビューションの wait_type 値を書き留めておきます。

パラメーター	説明
@QID	Step 1 で取得したrequest_id値
@StepIndex	Step 2 で識別されるStepIndex値
@ShowActiveOnly	0 - 指定された StepIndex 値のすべての分布を表示する 1 - 指定された StepIndex 値に対して現在アクティブなディストリビューションのみを表示する

DECLARE @QID VARCHAR(16) = '<request_id>', @StepIndex INT = <StepIndex>, @ShowActiveOnly BIT = 1;
WITH dists
AS (SELECT request_id, step_index, 'sys.dm_pdw_sql_requests' AS source_dmv,
       distribution_id, pdw_node_id, spid, 'NativeSQL' AS [type], [status],
       start_time, end_time, total_elapsed_time, row_count
    FROM sys.dm_pdw_sql_requests
    WHERE request_id = @QID AND step_index = @StepIndex
    UNION ALL
    SELECT request_id, step_index, 'sys.dm_pdw_dms_workers' AS source_dmv,
       distribution_id, pdw_node_id, sql_spid AS spid, [type],
       [status], start_time, end_time, total_elapsed_time, rows_processed as row_count
    FROM sys.dm_pdw_dms_workers
    WHERE request_id = @QID AND step_index = @StepIndex
   )
SELECT sr.step_index, sr.distribution_id, sr.pdw_node_id, sr.spid,
       sr.type, sr.status, sr.start_time, sr.end_time,
       sr.total_elapsed_time, sr.row_count, owt.wait_type, owt.wait_time
FROM dists sr
   LEFT JOIN sys.dm_pdw_nodes_exec_requests owt
      ON sr.pdw_node_id = owt.pdw_node_id
         AND sr.spid = owt.session_id
         AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests'
                 AND sr.status = 'Running') -- sys.dm_pdw_sql_requests status
              OR (sr.source_dmv = 'sys.dm_pdw_dms_requests'
                     AND sr.status not LIKE 'Step[CE]%')) -- sys.dm_pdw_dms_workers final statuses
WHERE sr.request_id = @QID
      AND ((sr.source_dmv = 'sys.dm_pdw_sql_requests' AND sr.status =
               CASE WHEN @ShowActiveOnly = 1 THEN 'Running' ELSE sr.status END)
           OR (sr.source_dmv = 'sys.dm_pdw_dms_workers' AND sr.status NOT LIKE
                  CASE WHEN @ShowActiveOnly = 1 THEN 'Step[CE]%' ELSE '' END))
      AND sr.step_index = @StepIndex
ORDER BY distribution_id

手順 4: 診断と軽減

コンパイル フェーズの問題

• Step 2 で取得したDescriptionの値に従って、次の表の詳細については、関連するセクションを確認してください。

  説明	一般的な原因
  Compilation Concurrency	ブロック: コンパイルコンカレンシー
  Resource Allocation (Concurrency)	ブロック: リソースの割り当て

• クエリが Step 1 で特定された "実行中" 状態にあるが、 Step 2 にステップ情報がない場合は、シナリオに最も適した原因を確認して、次の表から詳細情報を取得します。

  シナリオ	一般的な原因
  ステートメントに複雑な結合フィルター ロジックが含まれているか、 WHERE 句で結合を実行します	複雑なクエリまたは以前の JOIN 構文
  ステートメントは、実行時間の長い DROP TABLE または TRUNCATE TABLE ステートメントです	実行時間の長い DROP TABLE または TRUNCATE TABLE
  CC には、削除された行または開いている行の割合が高くなります (クラスター化列ストア インデックスの最適化 を参照してください)	異常な CCI (通常)

• 低速クエリの送信直後に実行された 1 つ以上のCREATE STATISTICS ステートメントについて、Step 1 の結果セットを分析します。 次の表から、シナリオに最も適した原因を確認します。

  シナリオ	一般的な原因
  予期せず作成された統計	統計の自動作成からの遅延
  5 分後に統計の作成に失敗しました	統計の自動作成タイムアウト

ブロック: コンパイルコンカレンシー

コンカレンシー コンパイル ブロックはほとんど発生しません。 ただし、この種類のブロックが発生した場合は、大量のクエリが短時間で送信され、コンパイルを開始するためにキューに登録されていることを示します。

軽減策

同時に送信するクエリの数を減らします。

ブロック: リソースの割り当て

リソースの割り当てがブロックされているということは、クエリが次に基づいて実行を待機していることを意味します。

• ユーザーに関連付けられているリソース クラスまたはワークロード グループの割り当てに基づいて付与されるメモリの量。
• システムまたはワークロード グループで使用可能なメモリの量。
• (省略可能) ワークロード グループ/分類子の重要度。

軽減策

• ブロック セッションが完了するまで待ちます。
• リソース クラスの選択肢を評価。 詳細については、「 コンキュレンシーの制限」を参照してください。
• ブロック セッションを する方が望ましいかどうかを評価します。

複雑なクエリまたは以前の JOIN 構文

コンパイル フェーズに時間がかかるため、既定のクエリ オプティマイザー メソッドが効果がないと証明される場合があります。 クエリが次の場合に発生する可能性があります。

• 多数の結合やサブクエリ (複雑なクエリ) が含まれます。
• (ANSI-92 スタイルの結合ではなく) FROM 句で結合子を利用します。

これらのシナリオは非定型ですが、既定の動作をオーバーライドして、クエリ オプティマイザーがプランを選択するのにかかる時間を短縮するオプションがあります。

軽減策

• ANSI-92 スタイルの結合を使用します。
• クエリ ヒントの追加: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))。 詳細については、「FORCE ORDER と Cardinality Estimation (SQL Server)」を参照してください。
• クエリを複数の複雑ではないステップに分割します。

実行時間の長い DROP TABLE または TRUNCATE TABLE

実行時間の効率を高めるために、 DROP TABLE ステートメントと TRUNCATE TABLE ステートメントは、ストレージのクリーンアップをバックグラウンド プロセスに延期します。 ただし、ワークロードで短時間で多数の DROP/TRUNCATE TABLE ステートメントが実行される場合は、メタデータが混雑し、後続の DROP/TRUNCATE TABLE ステートメントの実行速度が低下する可能性があります。

軽減策

メンテナンス期間を特定し、すべてのワークロードを停止し、 DBCC SHRINKDATABASE を実行して、以前に削除または切り捨てられたテーブルの即時クリーンアップを強制します。

異常な CCI (通常)

クラスター化列ストア インデックス (CCI) の正常性が低い場合は、追加のメタデータが必要です。これにより、クエリ オプティマイザーが最適なプランを決定するのに時間がかかる可能性があります。 このような状況を回避するには、すべての CCI の正常性を確保します。

軽減策

専用 SQL プールでクラスター化列ストア インデックスの正常性を評価して修正します。

統計の自動作成からの遅延

統計の自動作成オプションAUTO_CREATE_STATISTICSは、クエリ オプティマイザーが適切な分散プランの決定を行えるようにするために、既定でONされます。 ただし、自動作成プロセス自体では、最初のクエリに同じクエリの後続の実行よりも時間がかかる場合があります。

軽減策

クエリの最初の実行で統計を一貫して作成する必要がある場合は、クエリの実行前に統計手動で作成する必要があります。

統計の自動作成タイムアウト

統計の自動作成オプションAUTO_CREATE_STATISTICSは、クエリ オプティマイザーが適切な分散プランの決定を行えるようにするために、既定でONされます。 統計の自動作成は SELECT ステートメントに応答して行われ、完了までのしきい値は 5 分です。 作成するデータのサイズや統計の数が 5 分のしきい値を超える必要がある場合、クエリの実行を続行できるように統計の自動作成は破棄されます。 統計を作成できないと、効率的な分散実行プランを生成するクエリ オプティマイザーの機能に悪影響が及び、クエリパフォーマンスが低下する可能性があります。

軽減策

識別されたテーブル/列の自動作成機能に依存するのではなく統計を手動で作成します。

実行フェーズの問題

• 次の表を使用して、 Step 2 の結果セットを分析します。 シナリオを特定し、詳細情報と考えられる軽減手順の一般的な原因を確認します。

  シナリオ	一般的な原因
  EstimatedRowCount/ActualRowCount< 25%	不正確な見積もり
  Description値はBroadcastMoveOperationを示し、クエリはレプリケートされたテーブルを参照します。	キャッシュされていないレプリケート テーブル
  1. @ShowActiveOnly = 0 2. ステップ数が多い、または予期しない数 (step_index) が観察されます。 3. 結合子列のデータ型は、テーブル間で同じではありません。	データ型/サイズの不一致
  1. Description 値は、 HadoopBroadcastOperation、 HadoopRoundRobinOperation 、または HadoopShuffleOperationを示します。 2. 特定のstep_indexのtotal_elapsed_time値は、実行間で一貫性がありません。	アドホック外部テーブル クエリ

• 手順 3. で取得した total_elapsed_time 値 確認。 特定の手順のいくつかのディストリビューションで大幅に高い場合は、次の手順に従います。

  1. 関連するstep_idのTSQL フィールドで参照されているすべてのテーブルのデータ分散を確認するには、それぞれに対して次のコマンドを実行します。

     DBCC PDW_SHOWSPACEUSED(<table>);
     

  2. 行 <最小値>/<maximum 行の値>> 0.1 の場合は、 Data skew (stored)に移動します。

  3. それ以外の場合は、 インフライト データ スキューに移動します。

不正確な見積もり

クエリ オプティマイザーで最適なプランが生成されるように、統計を最新の状態にします。 推定行数が実際の数よりも大幅に少ない場合は、統計を維持する必要があります。

軽減策

統計の作成/更新。

キャッシュされていないレプリケート テーブル

レプリケート テーブルを作成し、レプリケートされたテーブル キャッシュを適切にウォームアップできない場合は、追加のデータ移動や最適ではない分散プランの作成が原因で、予期しないパフォーマンスが低下します。

軽減策

• DML 操作の後 レプリケートされたキャッシュをウォームします。
• DML 操作が頻繁に発生する場合は、テーブルの分散を ROUND_ROBINに変更します。

データ型/サイズの不一致

テーブルを結合するときは、結合列のデータ型とサイズが一致していることを確認します。 そうしないと、不要なデータ移動が発生し、ワークロードの残りの部分への CPU、IO、およびネットワーク トラフィックの可用性が低下します。

軽減策

同じデータ型とサイズを持たない関連するテーブル列を修正するには、テーブルを再構築します。

アドホック外部テーブル クエリ

外部テーブルに対するクエリは、専用 SQL プールにデータを一括読み込みする目的で設計されています。 外部テーブルに対するアドホック クエリは、同時ストレージ コンテナー アクティビティなどの外部要因により、変動する期間が発生する可能性があります。

軽減策

最初に専用 SQL プールにデータを読み込み 読み込まれたデータに対してクエリを実行します。

データ スキュー (格納)

データ スキューは、データがディストリビューション全体に均等に分散されていないことを意味します。 分散プランの各ステップでは、次の手順に進む前にすべてのディストリビューションを完了する必要があります。 データが偏っている場合、CPU や IO などの処理リソースの可能性をすべて達成できないため、実行時間が遅くなります。

軽減策

分散テーブルの ガイドを確認して より適切な分散列の選択を支援します。

インフライト データ スキュー

インフライト データ スキューは、 データ スキュー (格納) 問題の一種です。 ただし、偏っているのはディスク上のデータの分散ではありません。 特定のフィルターまたはグループ化されたデータの分散プランの性質により、 ShuffleMoveOperation 型操作が発生します。 この操作により、ダウンストリームで使用される偏った出力が生成されます。

軽減策

• 統計が 作成され、最新であることを確認します。
• GROUP BY列の順序を変更して、カーディナリティの高い列を使用します。
• 結合が複数の列を対象とする場合は、複数列の統計を作成します。
• クエリ にクエリ ヒント OPTION(FORCE_ORDER) を追加します。
• クエリをリファクタリングします。

待機の種類の問題

上記の一般的な問題がクエリに当てはまらない場合、 Step 3 データは、実行時間が最も長いステップのクエリ処理を妨げている待機の種類 ( wait_type と wait_time) を判断する機会を与えます。 多数の待機の種類があり、同様の軽減策により、関連するカテゴリにグループ化されます。 クエリ ステップの待機カテゴリを見つけるには、次の手順に従います。

1. 最も時間がかかっている手順 3 のwait_typeを特定します。
2. カテゴリ マッピング テーブル 待機の種類を見つけて 含まれている待機カテゴリを特定します。
3. 推奨される軽減策については、次の一覧から待機カテゴリに関連するセクションを展開します。

コンパイル

コンパイル カテゴリの待機の種類の問題を軽減するには、次の手順に従います。

1. 問題のあるクエリに関係するすべてのオブジェクトのインデックスを再構築します。
2. 問題のあるクエリに関連するすべてのオブジェクトの統計を更新します。
3. 問題のあるクエリをもう一度テストして、問題が解決しないかどうかを検証します。

問題が解決しない場合は、次のようにします。

1. 次を使用して .sql ファイルを作成します。

   SET QUERY_DIAGNOSTICS ON; <Your_SQL>; SET QUERY_DIAGNOSTICS OFF;
   

2. コマンド プロンプト ウィンドウを開き、次のコマンドを実行します。

   sqlcmd −S <servername>.database.windows.net −d <databasename> −U <username> −G −I −i .\<sql_file_name>.sql −y0 −o .\<output_file_name>.txt
   

3. テキスト エディターで <output_file_name>.txtを開きます。 Step 2 で識別された実行時間が最も長い手順からディストリビューション レベルの実行プラン (<ShowPlanXML>で始まる行) を見つけて、.sqlplan 拡張子を持つ別のテキスト ファイルに貼り付けます。

   注: 分散プランの各ステップには、通常、60 個の配布レベルの実行プランが記録されます。 同じ分散プラン ステップから実行プランを準備して比較していることを確認します。

4. Step 3 クエリでは、他のディストリビューションよりもはるかに長い時間がかかることが頻繁に示されます。 SQL Server Management Studio で、実行時間の長いディストリビューションのディストリビューション レベルの実行プラン (作成された .sqlplan ファイル) を高速実行ディストリビューションと比較して、潜在的な原因の違いを分析します。

Lock、Worker Thread

• CCI の代わりに行ストア インデックスを使用するように、頻繁に小さな変更を行うテーブルを変更することを検討してください。
• 変更をバッチ処理し、頻度の低い行数でターゲットを更新します。

バッファー IO、その他のディスク IO、Tran Log IO

異常な CCI

異常な CCI は、IO、CPU、メモリの割り当ての増加に寄与し、クエリのパフォーマンスに悪影響を及ぼします。 この問題を軽減するには、次のいずれかの方法を試してください。

• 専用 SQL プールでクラスター化列ストア インデックスの正常性を評価して修正します。
• クエリを実行し、「クラスター化列ストア インデックスの最適化」で一覧表示されるクエリの出力を確認して、ベースラインを取得します。
• この手順に従って、インデックスをしてセグメントの品質を向上させ、問題のクエリの例に含まれるテーブルを対象とします。

古い統計

古い統計により、最適化されていない分散プランが生成される可能性があります。これには、必要以上に多くのデータ移動が含まれます。 不要なデータ移動により、保存データだけでなく、 tempdbでもワークロードが増加します。 IO はすべてのクエリの共有リソースであるため、パフォーマンスへの影響がワークロード全体で認識される可能性があります。

この状況を解決するには、すべての 状態を最新の状態に保ちユーザー ワークロードに対して更新を維持するためのメンテナンス プランを用意します。

負荷の高い IO ワークロード

ワークロード全体で大量のデータが読み取られる可能性があります。 Synapse の専用 SQL プールによって、リソースが DWU に従ってスケーリングされます。 パフォーマンスを向上させるには、次のいずれかまたは両方を検討してください。

• クエリに大きな リソース クラス を使用する。
• コンピューティング リソースを増やす。

CPU、並列処理

シナリオ	対応策
CCI の正常性の低下	専用 SQL プールでクラスター化列ストア インデックスの正常性を評価して修正する
ユーザー クエリに変換が含まれる	すべての書式設定と他の変換ロジックを ETL プロセスに移動して、書式設定されたバージョンが格納されるようにします
ワークロードの優先順位が正しく設定されていない	workload 分離を実装する
ワークロードに対する DWU の不足	コンピューティング リソース 作成することを検討する

ネットワーク IO

Step 2 でRETURN操作中に問題が発生した場合、

• 同時実行並列プロセスの数を減らします。
• 最も影響を受けるプロセスを別のクライアントにスケールアウトします。

他のすべてのデータ移動操作では、ネットワークの問題が専用 SQL プールの内部にあるように見える可能性があります。 この問題を迅速に軽減するには、次の手順に従います。

1. 専用 SQL プールを DW100c にスケーリングする
2. 目的の DWU レベルにスケールバックする

SQL CLR

データを変換する別の方法 (スタイルを使用したCONVERT()など) を実装することで、FORMAT()関数を頻繁に使用しないようにします。