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

  • [アーティクル]

適用対象: Azure Synapse Analytics

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

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

注:

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

Azure Data Studio で Notebook を開く

重要

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

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

トラブルシューティングの時間を節約するには、統計が 作成され、最新の状態と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;

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

  • または total_elapsed_time DESC でsubmit_time DESC並べ替えて、実行時間が最も長いクエリを結果セットの先頭に表示します。

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

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

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

手順 2: クエリに時間がかかる場所を決定する

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

パラメーター 説明
@QID request_id手順 1 で取得した値
@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 request_id手順 1 で取得した値
@StepIndex StepIndex手順 2 で識別される値
@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: 診断と軽減

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

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

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

軽減策

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


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

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

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

軽減策

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

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

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

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

軽減策

  • ANSI-92 スタイルの結合を使用します。
  • クエリ ヒントの追加: OPTION(FORCE ORDER, USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))。 詳細については、「FORCE ORDER とカーディナリティ推定 (SQL Server)」を参照してください。
  • クエリを複数の複雑でない手順に分割します。
実行時間の長い DROP TABLE または TRUNCATE TABLE

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

軽減策

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


異常な CCI (一般に)

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

軽減策

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


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

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

軽減策

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


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

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

軽減策

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

実行フェーズの問題

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

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

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

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

      DBCC PDW_SHOWSPACEUSED(<table>);

    2. 最小行値/<最大行数>の値>>が 0.1 の場合<は、データ スキュー (格納) に移動します。

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

不正確な見積もり

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

軽減策

統計の作成/更新。


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

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

軽減策

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

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

軽減策

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


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

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

軽減策

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


データ スキュー (格納)

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

軽減策

分散テーブルに関するガイダンスを確認して、より適切な分散列の選択を支援します。


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

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

軽減策

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

待機の種類に関する問題

上記の一般的な問題がクエリに適用されない場合、 手順 3 のデータは、実行時間が最も長いステップの wait_type クエリ処理に干渉している待機の種類 (および wait_time) を判断する機会を提供します。 待機の種類は多数あり、同様の軽減策が原因で関連カテゴリにグループ化されます。 クエリ ステップの待機カテゴリを見つけるには、次の手順に従います。

  1. wait_type手順 3 で、最も時間がかかる を特定します。
  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 を開きます<。 手順 2 で特定された最も実行時間の長い手順から配布レベルの実行プラン (で<ShowPlanXML>始まる行) を見つけてコピーし、.sqlplan 拡張機能を持つ別のテキスト ファイルに貼り付けます。

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

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

ロック、ワーカー スレッド
  • CCI ではなく行ストア インデックスを利用するために、頻繁に小さな変更を行うテーブルを変更することを検討してください。
  • 変更をバッチ処理し、より頻度の低い行でターゲットを更新します。
バッファー IO、その他のディスク IO、Tran Log IO

異常な CCI

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

古い統計

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

この状況を解決するには、すべての 統計が最新の状態であることを確認し、ユーザー ワークロードに対して更新を維持するためのメンテナンス 計画が整っていることを確認します。

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

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

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

ネットワーク IO

手順 2 の操作中にRETURN問題が発生した場合は、

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

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

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

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