DMV を使用して Azure Synapse Analytics SQL プールのワークロードを監視するMonitor your Azure Synapse Analytics SQL pool workload using DMVs

このアーティクルでは、動的管理ビュー (DMV) を使用して、SQL プールでのクエリ実行の調査などのワークロードを監視する方法について説明します。This article describes how to use Dynamic Management Views (DMVs) to monitor your workload including investigating query execution in SQL pool.

アクセス許可Permissions

このアーティクルの DMV に対してクエリを実行するには VIEW DATABASE STATE または CONTROL アクセス許可のいずれかが必要です。To query the DMVs in this article, you need either VIEW DATABASE STATE or CONTROL permission. 通常は、さらに制限の厳しい VIEW DATABASE STATE のアクセス許可を付与することが推奨されます。Usually, granting VIEW DATABASE STATE is the preferred permission as it is much more restrictive.

GRANT VIEW DATABASE STATE TO myuser;

接続を監視するMonitor connections

SQL Data Warehouse へのすべてのログインは、sys.dm_pdw_exec_sessionsに記録されます。All logins to your data warehouse are logged to sys.dm_pdw_exec_sessions. この DMV には、過去 10,000 件のログインが含まれています。This DMV contains the last 10,000 logins. プライマリ キーである session_id が、新規ログオンのたびに順次割り当てられます。The session_id is the primary key and is assigned sequentially for each new logon.

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

クエリ実行を監視するMonitor query execution

SQL プールで実行されるすべてのクエリは、sys. dm_pdw_exec_requestsに記録されます。All queries executed on SQL pool are logged to sys.dm_pdw_exec_requests. この DMV には、実行された過去 10,000 件のクエリが含まれています。This DMV contains the last 10,000 queries executed. request_id により各クエリが一意に識別されます。これはこの DMV のプライマリ キーです。The request_id uniquely identifies each query and is the primary key for this DMV. request_id は、新しいクエリごとに順番に割り当てられ、クエリ ID を表す QID がプレフィックスとして付加されます。The request_id is assigned sequentially for each new query and is prefixed with QID, which stands for query ID. 特定の session_id のこの DMV にクエリを実行すると、そのログオンのクエリがすべて表示されます。Querying this DMV for a given session_id shows all queries for a given logon.

注意

ストアド プロシージャでは複数の要求 ID が使用されます。Stored procedures use multiple Request IDs. 要求 ID は順次割り当てられます。Request IDs are assigned in sequential order.

クエリ実行プラン、および特定のクエリの実行時間を調査するには、次の手順に従います。Here are steps to follow to investigate query execution plans and times for a particular query.

手順 1: 調査するクエリを特定するSTEP 1: Identify the query you wish to investigate

-- 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 を書き留めますFrom the preceding query results, note the Request ID of the query that you would like to investigate.

アクティブな実行中のクエリが多数あるため、中断状態のクエリをキューに入れることができます。Queries in the Suspended state can be queued due to a large number of active running queries. これらのクエリは、UserConcurrencyResourceType 型の sys.dm_pdw_waits 待機クエリにも表示されます。These queries also appear in the sys.dm_pdw_waits waits query with a type of UserConcurrencyResourceType. コンカレンシーの制限に関する詳細については、「メモリおよびコンカレンシーの制限」、またはワークロード管理用のリソース クラスに関する記事を参照してください。For information on concurrency limits, see Memory and concurrency limits or Resource classes for workload management. クエリの待機は、オブジェクト ロックなど、他の理由によっても発生します。Queries can also wait for other reasons such as for object locks. クエリがリソースを待っている場合は、この記事の下にある リソースを待機しているクエリの調査 に関するトピックをご覧ください。If your query is waiting for a resource, see Investigating queries waiting for resources further down in this article.

sys.dm_pdw_exec_requests テーブル内のクエリの検索を簡素化するには、LABEL を使用して、sys.dm_pdw_exec_requests ビューで検索できるクエリにコメントを割り当てます。To simplify the lookup of a query in the sys.dm_pdw_exec_requests table, use LABEL to assign a comment to your query, which can be looked up in the sys.dm_pdw_exec_requests view.

-- 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: クエリ プランを調査するSTEP 2: Investigate the query plan

要求 ID を使用して、sys.dm_pdw_request_steps からクエリの分散 SQL (DSQL) プランを取得します。Use the Request ID to retrieve the query's distributed SQL (DSQL) plan from sys.dm_pdw_request_steps

-- 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 つの手順に長い時間を要することが原因であったりする可能性があります。When a DSQL plan is taking longer than expected, the cause can be a complex plan with many DSQL steps or just one step taking a long time. プランにいくつかの移動操作を含む多くの手順が存在する場合は、テーブルのディストリビューションを最適化して、データの移動を削減することを検討してください。If the plan is many steps with several move operations, consider optimizing your table distributions to reduce data movement. テーブルのディストリビューションに関する記事で、クエリを解決するためにデータを移動する必要がある理由について説明しています。The Table distribution article explains why data must be moved to solve a query. その記事では、データ移動を最小限に抑えるためのいくつかの分散方法についても説明しています。The article also explains some distribution strategies to minimize data movement.

1 つの手順に関する詳細を調査するには、実行時間の長いクエリ手順の operation_type 列を確認し、手順インデックスを書き留めます。To investigate further details about a single step, the operation_type column of the long-running query step and note the Step Index:

  • 手順 3 の SQL 操作 に進みます(OnOperation、RemoteOperation、ReturnOperation)。Proceed with Step 3 for SQL operations: OnOperation, RemoteOperation, ReturnOperation.
  • 手順 4 のデータ移動操作に進みます(ShuffleMoveOperation、BroadcastMoveOperation、TrimMoveOperation、PartitionMoveOperation、MoveOperation、CopyOperation)。Proceed with Step 4 for Data Movement operations: ShuffleMoveOperation, BroadcastMoveOperation, TrimMoveOperation, PartitionMoveOperation, MoveOperation, CopyOperation.

手順 3:分散データベースでの SQL を調査するSTEP 3: Investigate SQL on the distributed databases

要求 ID と手順インデックスを使用して、sys.dm_pdw_sql_requests から詳細情報を取得します。これには、配布されたすべてのデータベースに対するクエリの実行情報が含まれます。Use the Request ID and the Step Index to retrieve details from sys.dm_pdw_sql_requests, which contains execution information of the query step on all of the distributed databases.

-- 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 推定プランを取得できます。When the query step is running, DBCC PDW_SHOWEXECUTIONPLAN can be used to retrieve the SQL Server estimated plan from the SQL Server plan cache for the step running on a particular distribution.

-- 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:分散データベース上のデータ移動を調査するSTEP 4: Investigate data movement on the distributed databases

要求 ID と手順インデックスを利用し、sys.dm_pdw_dms_workers から各配布で実行されているデータ移動手順に関する情報を取得します。Use the Request ID and the Step Index to retrieve information about a data movement step running on each distribution from 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 列で、特定の配布で他の配布よりデータ移動に大幅に時間がかかっていないか確認します。Check the total_elapsed_time column to see if a particular distribution is taking significantly longer than others for data movement.
  • 実行時間の長い配布に対して、rows_processed 列で、その配布から移動された行の数が他の配布より大幅に大きいか確認します。For the long-running distribution, check the rows_processed column to see if the number of rows being moved from that distribution is significantly larger than others. 大きい場合、個の検出は、基になるデータの傾斜を示していることがあります。If so, this finding might indicate skew of your underlying data.

クエリが実行中の場合は、DBCC PDW_SHOWEXECUTIONPLAN を使用して、特定のディストリビューション内で現在実行中の SQL 手順に対する SQL Server プラン キャッシュから、SQL Server 推定プランを取得できます。If the query is running, you can use DBCC PDW_SHOWEXECUTIONPLAN to retrieve the SQL Server estimated plan from the SQL Server plan cache for the currently running SQL Step within a particular distribution.

-- 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);

待機クエリを監視するMonitor waiting queries

クエリがリソースを待っていることが原因で進行状況に変化がないことがわかった場合は、次のクエリを使用すると、待機対象のすべてのリソースが表示されます。If you discover that your query is not making progress because it is waiting for a resource, here is a query that shows all the resources a query is waiting for.

-- Find queries
-- Replace request_id with value from Step 1.

SELECT waits.session_id,
      waits.request_id,  
      requests.command,
      requests.status,
      requests.start_time,  
      waits.type,
      waits.state,
      waits.object_type,
      waits.object_name
FROM   sys.dm_pdw_waits waits
   JOIN  sys.dm_pdw_exec_requests requests
   ON waits.request_id=requests.request_id
WHERE waits.request_id = 'QID####'
ORDER BY waits.object_name, waits.object_type, waits.state;

クエリが別のクエリからのリソースを積極的に待っている場合、状態は AcquireResourcesになります。If the query is actively waiting on resources from another query, then the state will be AcquireResources. クエリに必要なリソースがすべて揃っている場合、状態は Grantedになります。If the query has all the required resources, then the state will be Granted.

tempdb を監視するMonitor tempdb

tempdb は、クエリ実行中に中間結果を保持するために使用されます。Tempdb is used to hold intermediate results during query execution. tempdb データベースの使用率が高いと、クエリのパフォーマンスが低下する可能性があります。High utilization of the tempdb database can lead to slow query performance. 構成されているすべての DW100c について、399 GB の tempdb 領域が割り当てられます (DW1000c は、合計 tempdb 領域が 3.99 TB になります)。For every DW100c configured, 399 GB of tempdb space is allocated (DW1000c would have 3.99 TB of total tempdb space). tempdb の使用状況を監視するためのヒントと、クエリでの tempdb 使用率を減らすためのヒントを以下に示します。Below are tips for monitoring tempdb usage and for decreasing tempdb usage in your queries.

ビューでの tempdb の監視Monitoring tempdb with views

tempdb の使用状況を監視するには、まず、「Microsoft Toolkit for SQL プール」(SQL プール向け Microsoft Toolkit) から microsoft.vw_sql_requests ビューをインストールします。To monitor tempdb usage, first install the microsoft.vw_sql_requests view from the Microsoft Toolkit for SQL pool. その後、次のクエリを実行し、実行したすべてのクエリのノードごとの tempdb 使用状況を確認することができます。You can then execute the following query to see the tempdb usage per node for all executed queries:

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    es.login_name AS 'LoginName',
    DB_NAME(ssu.database_id) AS 'DatabaseName',
    (es.memory_usage * 8) AS 'MemoryUsage (in KB)',
    (ssu.user_objects_alloc_page_count * 8) AS 'Space Allocated For User Objects (in KB)',
    (ssu.user_objects_dealloc_page_count * 8) AS 'Space Deallocated For User Objects (in KB)',
    (ssu.internal_objects_alloc_page_count * 8) AS 'Space Allocated For Internal Objects (in KB)',
    (ssu.internal_objects_dealloc_page_count * 8) AS 'Space Deallocated For Internal Objects (in KB)',
    CASE es.is_user_process
    WHEN 1 THEN 'User Session'
    WHEN 0 THEN 'System Session'
    END AS 'SessionType',
    es.row_count AS 'RowCount'
FROM sys.dm_pdw_nodes_db_session_space_usage AS ssu
    INNER JOIN sys.dm_pdw_nodes_exec_sessions AS es ON ssu.session_id = es.session_id AND ssu.pdw_node_id = es.pdw_node_id
    INNER JOIN sys.dm_pdw_nodes_exec_connections AS er ON ssu.session_id = er.session_id AND ssu.pdw_node_id = er.pdw_node_id
    INNER JOIN microsoft.vw_sql_requests AS sr ON ssu.session_id = sr.spid AND ssu.pdw_node_id = sr.pdw_node_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

クエリで大量のメモリを消費しているか、tempdb の割り当てに関するエラー メッセージが表示された場合は、非常に大きな CREATE TABLE AS SELECT (CTAS) または INSERT SELECT ステートメントが実行されていることが原因の可能性があります。この場合、最終的なデータ移動操作に失敗します。If you have a query that is consuming a large amount of memory or have received an error message related to allocation of tempdb, it could be due to a very large CREATE TABLE AS SELECT (CTAS) or INSERT SELECT statement running that is failing in the final data movement operation. これは通常、最終的な INSERT SELECT の直前の、分散クエリ プランの ShuffleMove 操作として識別できます。This can usually be identified as a ShuffleMove operation in the distributed query plan right before the final INSERT SELECT. ShuffleMove 操作を監視するには、sys.dm_pdw_request_steps を使用します。Use sys.dm_pdw_request_steps to monitor ShuffleMove operations.

最も一般的な軽減策は、データ ボリュームが tempdb の制限である 1 TB (ノードあたり) を超えないように、CTAS または INSERT SELECT ステートメントを複数の LOAD ステートメントに分割することです。The most common mitigation is to break your CTAS or INSERT SELECT statement into multiple load statements so the data volume will not exceed the 1TB per node tempdb limit. また、クラスターをより大きなサイズにスケーリングすることができます。これにより、より多くのノードに tempdb サイズが分散され、個々のノードの tempdb が減ります。You can also scale your cluster to a larger size which will spread the tempdb size across more nodes reducing the tempdb on each individual node.

CTAS ステートメントと INSERT SELECT ステートメントだけでなく、大規模で複雑なクエリがメモリ不足の状態で実行されると、tempdb に書き込まれ、クエリが失敗する可能性があります。In addition to CTAS and INSERT SELECT statements, large, complex queries running with insufficient memory can spill into tempdb causing queries to fail. tempdb への書き込みを避けるために、より大きなリソース クラスを使用して実行することを検討してください。Consider running with a larger resource class to avoid spilling into tempdb.

メモリを監視するMonitor memory

メモリが、パフォーマンスの低下とメモリ不足の問題の根本原因になることがあります。Memory can be the root cause for slow performance and out of memory issues. クエリの実行中に SQL Server のメモリ使用率が制限に達していることがわかった場合は、データ ウェアハウスのスケーリングを検討してください。Consider scaling your data warehouse if you find SQL Server memory usage reaching its limits during query execution.

次のクエリでは、ノードあたりの SQL Server のメモリ使用率とメモリ負荷が返されます。The following query returns SQL Server memory usage and memory pressure per node:

-- Memory consumption
SELECT
  pc1.cntr_value as Curr_Mem_KB,
  pc1.cntr_value/1024.0 as Curr_Mem_MB,
  (pc1.cntr_value/1048576.0) as Curr_Mem_GB,
  pc2.cntr_value as Max_Mem_KB,
  pc2.cntr_value/1024.0 as Max_Mem_MB,
  (pc2.cntr_value/1048576.0) as Max_Mem_GB,
  pc1.cntr_value * 100.0/pc2.cntr_value AS Memory_Utilization_Percentage,
  pc1.pdw_node_id
FROM
-- pc1: current memory
sys.dm_pdw_nodes_os_performance_counters AS pc1
-- pc2: total memory allowed for this SQL instance
JOIN sys.dm_pdw_nodes_os_performance_counters AS pc2
ON pc1.object_name = pc2.object_name AND pc1.pdw_node_id = pc2.pdw_node_id
WHERE
pc1.counter_name = 'Total Server Memory (KB)'
AND pc2.counter_name = 'Target Server Memory (KB)'

トランザクション ログのサイズを監視するMonitor transaction log size

次のクエリでは、配布ごとのトランザクション ログのサイズが返されます。The following query returns the transaction log size on each distribution. ログ ファイルのいずれかが 160 GB に達している場合は、インスタンスのスケールアップまたはトランザクション サイズの制限を検討する必要があります。If one of the log files is reaching 160 GB, you should consider scaling up your instance or limiting your transaction size.

-- Transaction log size
SELECT
  instance_name as distribution_db,
  cntr_value*1.0/1048576 as log_file_size_used_GB,
  pdw_node_id
FROM sys.dm_pdw_nodes_os_performance_counters
WHERE
instance_name like 'Distribution_%'
AND counter_name = 'Log File(s) Used Size (KB)'

トランザクション ログのロールバックを監視するMonitor transaction log rollback

クエリが失敗するか、続行するのに長時間かかる場合は、トランザクションのロールバックを確認および監視できます。If your queries are failing or taking a long time to proceed, you can check and monitor if you have any transactions rolling back.

-- Monitor rollback
SELECT
    SUM(CASE WHEN t.database_transaction_next_undo_lsn IS NOT NULL THEN 1 ELSE 0 END),
    t.pdw_node_id,
    nod.[type]
FROM sys.dm_pdw_nodes_tran_database_transactions t
JOIN sys.dm_pdw_nodes nod ON t.pdw_node_id = nod.pdw_node_id
GROUP BY t.pdw_node_id, nod.[type]

PolyBase の読み込みを監視するMonitor PolyBase load

次のクエリでは、読み込みの進行状況を概算で見積もります。The following query provides an approximate estimate of the progress of your load. このクエリでは、現在処理中のファイルのみが表示されます。The query only shows files currently being processed.


-- To track bytes and files
SELECT
    r.command,
    s.request_id,
    r.status,
    count(distinct input_name) as nbr_files,
    sum(s.bytes_processed)/1024/1024/1024 as gb_processed
FROM
    sys.dm_pdw_exec_requests r
    inner join sys.dm_pdw_dms_external_work s
        on r.request_id = s.request_id
GROUP BY
    r.command,
    s.request_id,
    r.status
ORDER BY
    nbr_files desc,
    gb_processed desc;

次のステップNext steps

DMV の詳細については、システム ビューに関するページを参照してください。For more information about DMVs, see System views.