動的管理ビューを使用してクエリ パフォーマンスの特定とトラブルシューティングを行う

  • 8 分

動的管理ビューでは、Transact-SQL 言語を使用して、Azure Synapse Analytics SQL プール アクティビティを監視するためのプログラマティック エクスペリエンスが提供されます。 提供されるビューでは、システムで動作しているワークロードのパフォーマンス ボトルネックのトラブルシューティングおよび特定が可能になるだけでなく、Azure Synapse Analytics に関するレコメンデーションを提供するために、Azure Advisor などの他のサービスによっても使用されます。

専用の SQL プールに対してクエリを実行し、サービスの次の領域に関する情報を取得することができる 90 を超える動的管理ビューがあります。

  • 接続情報とアクティビティ
  • SQL 実行要求とクエリ
  • インデックスと統計情報
  • リソースのブロックとロックのアクティビティ
  • データ移動サービスのアクティビティ
  • エラー

次に、Azure Synapse Analytics SQL プールのクエリ実行を監視する例を示します。 最初の手順では、まずサーバーに対する接続を確認してから、クエリ実行アクティビティを確認します。 

接続の監視

SQL Data Warehouse へのすべてのログインは、sys.dm_pdw_exec_sessionsに記録されます。 プライマリ キーである session_id が、新規ログオンのたびに順次割り当てられます。

-- Other Active Connections
SELECT * FROM sys.dm_pdw_exec_sessions where status <> 'Closed' and session_id <> session_id();

クエリ実行を監視する

SQL プールで実行されるすべてのクエリは、sys. dm_pdw_exec_requestsに記録されます。 request_id により各クエリが一意に識別されます。これはこの DMV のプライマリ キーです。 request_id は、新しいクエリごとに順番に割り当てられ、クエリ ID を表す QID がプレフィックスとして付加されます。 特定の session_id のこの DMV にクエリを実行すると、そのログオンのクエリがすべて表示されます。

手順 1

最初の手順では、調査するクエリを特定します

-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
  AND session_id <> session_id()
ORDER BY submit_time DESC;

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

上記のクエリ結果から、調査するクエリの 要求 ID を書き留めます 。

アクティブな実行中のクエリが多数あるため、中断状態のクエリをキューに入れることができます。 これらのクエリは、UserConcurrencyResourceType 型の sys.dm_pdw_waits 待機クエリにも表示されます。 コンカレンシーの制限に関する詳細については、「メモリおよびコンカレンシーの制限」、またはワークロード管理用のリソース クラスに関する記事を参照してください。 クエリの待機は、オブジェクト ロックなど、他の理由によっても発生します。 クエリがリソースを待っている場合は、この記事の下にある リソースを待機しているクエリの調査 に関するトピックをご覧ください。

sys.dm_pdw_exec_requests テーブル内のクエリの検索を簡素化するには、LABEL を使用して、sys.dm_pdw_exec_requests ビューで検索できるクエリにコメントを割り当てます。

-- Query with Label
SELECT *
FROM sys.tables
OPTION (LABEL = 'My Query')
;

-- Find a query with the Label 'My Query'
-- Use brackets when querying the label column, as it it a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

手順 2

要求 ID を使用して、sys.dm_pdw_request_steps からクエリの分散 SQL (DSQL) プランを取得します。

-- Find the distributed query plan steps for a specific query.
-- Replace request_id with value from Step 1.

SELECT * FROM sys.dm_pdw_request_steps
WHERE request_id = 'QID####'
ORDER BY step_index;

DSQL プランに予想よりも時間がかかる場合は、多くの DSQL 手順が存在する複雑なプランであったり、1 つの手順に長い時間を要することが原因であったりする可能性があります。 プランにいくつかの移動操作を含む多くの手順が存在する場合は、テーブルのディストリビューションを最適化して、データの移動を削減することを検討してください。

テーブルのディストリビューションに関する記事で、クエリを解決するためにデータを移動する必要がある理由について説明しています。 その記事では、データ移動を最小限に抑えるためのいくつかの分散方法についても説明しています。

1 つの手順に関する詳細を調査するには、実行時間の長いクエリ手順の operation_type 列を確認し、手順インデックスを書き留めます。

  • 手順 3 の SQL 操作 に進みます(OnOperation、RemoteOperation、ReturnOperation)。
  • 手順 4 のデータ移動操作に進みます(ShuffleMoveOperation、BroadcastMoveOperation、TrimMoveOperation、PartitionMoveOperation、MoveOperation、CopyOperation)。

手順 3.

要求 ID と手順インデックスを使用して、sys.dm_pdw_sql_requests から詳細情報を取得します。これには、配布されたすべてのデータベースに対するクエリの実行情報が含まれます。

-- Find the distribution run times for a SQL step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_sql_requests
WHERE request_id = 'QID####' AND step_index = 2;

クエリ手順が実行中の場合は、DBCC PDW_SHOWEXECUTIONPLAN を使用して、特定の配布で実行中の手順に対する SQL Server プラン キャッシュから、SQL Server 推定プランを取得できます。

-- Find the SQL Server execution plan for a query running on a specific SQL pool or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(1, 78);

手順 4

要求 ID と手順インデックスを利用し、sys.dm_pdw_dms_workers から各配布で実行されているデータ移動手順に関する情報を取得します。

-- Find information about all the workers completing a Data Movement Step.
-- Replace request_id and step_index with values from Step 1 and 3.

SELECT * FROM sys.dm_pdw_dms_workers
WHERE request_id = 'QID####' AND step_index = 2;
  • total_elapsed_time 列で、特定の配布で他の配布よりデータ移動に時間がかかっていないか確認します。
  • 実行時間の長い配布に対して、rows_processed 列で、その配布から移動された行の数が他の配布より大きいか確認します。 大きい場合、個の検出は、基になるデータの傾斜を示していることがあります。 データ スキューの原因の 1 つは、多数の NULL 値を持つ列 (行がすべて同じ分布に配置される) に配布することです。 このような列に対する配布を回避したり、可能な場合はクエリをフィルター処理して NULL を除外したりすることで、クエリの速度が低下しないようにします。

クエリが実行中の場合は、DBCC PDW_SHOWEXECUTIONPLAN を使用して、特定のディストリビューション内で現在実行中の SQL 手順に対する SQL Server プラン キャッシュから、SQL Server 推定プランを取得できます。

-- Find the SQL Server estimated plan for a query running on a specific SQL pool Compute or control node.
-- Replace distribution_id and spid with values from previous query.

DBCC PDW_SHOWEXECUTIONPLAN(55, 238);

動的管理ビュー (DMV) には、1 万行のデータのみが含まれます。 データが先入れ先出しシステムで管理されているため、使用率が高いシステムでは、このテーブルに保持されているデータは数時間、さらには数分で失われる可能性があるということです。 結果として、システムのクエリ パフォーマンスの問題を診断するのに役立つ意味のある情報が失われる可能性があります。 このような状況では、クエリ ストアを使用する必要があります。

次のような Azure Synapse SQL プールのその他の側面を監視することもできます。

  • 待機の監視
  • tempdb の監視
  • メモリの監視
  • トランザクション ログの監視
  • PolyBase の監視

これらの領域の監視に関する情報はここで確認できます。