使用 DMV 監視您的 Azure Synapse Analytics 專用 SQL 集區工作負載

發行項
06/07/2023

此文章說明如何使用動態管理檢視 (DMV)，在專用 SQL 集區中監視工作負載，包括調查查詢執行。

權限

若要查詢此文章中的 DMV，您需要檢視資料庫狀態或控制權限。通常，授與檢視資料庫狀態是慣用的權限，因為它較具限制性。

GRANT VIEW DATABASE STATE TO myuser;

監視連接

資料倉儲的所有登入都會記錄至 sys.dm_pdw_exec_sessions。這個 DMV 會包含最後 10,000 筆登入。 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。這個 DMV 會包含最後 10,000 筆執行的查詢。 request_id 可唯一識別每筆查詢，而且是此 DMV 的主索引鍵。 request_id 會依序指派給每筆新查詢，並加上 QID 代表查詢識別碼。針對指定的 session_id 查詢此 DMV，即會顯示指定登入的所有查詢。

注意

預存程序會使用多個要求 ID。要求 ID 是依序指派。

請遵循以下步驟來調查特定查詢的查詢執行計畫和時間。

步驟 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 。

處於 [暫停] 狀態的查詢可能由於大量作用中的執行中查詢而排入佇列。這些查詢也會出現在 sys.dm_pdw_waits 中。在此情況下，請尋找 UserConcurrencyResourceType 之類的等待查詢。如需並行限制的相關資訊，請參閱記憶體和並行限制或工作負載管理的資源類別。查詢也會因其他原因 (例如物件鎖定) 而等候。如果您的查詢正在等候資源，請參閱本文稍後的檢查查詢是否正在等候資源。

若要簡化在 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 is a key word
SELECT  *
FROM    sys.dm_pdw_exec_requests
WHERE   [label] = 'My Query';

步驟 2：調查查詢計劃

使用要求識別碼，以從 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 步驟的複雜計劃所導致，或只是某個步驟需要長時間處理。如果計劃是含有數個移動作業的許多步驟，請考慮最佳化您的資料表散發以減少資料移動。資料表散發文章說明為何必須移動資料才能解決問題。本文也會說明將資料移動降至最低的一些散發策略。

若要調查單一步驟的進一步詳細資料，請檢查 operation_type 長時間執行查詢步驟的資料行，並記下 步驟索引：

若為 SQL 作業 (OnOperation、RemoteOperation、ReturnOperation) ，請繼續進行步驟 3
對於資料 移動作業 (ShuffleMoveOperation、BroadcastMoveOperation、TrimMoveOperation、PartitionMoveOperation、MoveOperation、CopyOperation) ，請繼續進行步驟 4。

步驟 3：調查分散式資料庫的 SQL

使用要求 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 資料行，查看從該散發移動的資料列數是否遠多過其他散發。若是如此，這個結果可能表示基礎資料的扭曲。資料扭曲的其中一個原因是在具有許多 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);

監視等候中的查詢

如果您發現您的查詢因為正在等候資源而沒有進度，以下查詢可顯示查詢正在等候的所有資源。

-- 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。如果查詢具有全部的所需資源，則狀態會是 Granted。

監視 tempdb

tempdb 資料庫可用來保存查詢執行期間的中繼結果。 tempdb 資料庫的高使用率可能會導致查詢效能變慢。針對每個設定的 DW100c，會配置 399 GB 的 tempdb 空間 (DW1000c 會有 3.99 TB 的 tempdb 總空間)。以下是監視 tempdb 使用量，以及減少查詢中 tempdb 使用量的提示。

使用檢視來監視 tempdb

若要監視 tempdb 使用量，請先從適用於 SQL 集區的 Microsoft 工具組 (英文) 安裝 microsoft.vw_sql_requests (英文) 檢視。然後，您可以執行下列查詢，以查看所有已執行查詢之每個節點的 tempdb 使用量：

-- Monitor tempdb
SELECT
    sr.request_id,
    ssu.session_id,
    ssu.pdw_node_id,
    sr.command,
    sr.total_elapsed_time,
    exs.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
    LEFT JOIN sys.dm_pdw_exec_requests exr on exr.request_id = sr.request_id
    LEFT JOIN sys.dm_pdw_exec_sessions exs on exr.session_id = exs.session_id
WHERE DB_NAME(ssu.database_id) = 'tempdb'
    AND es.session_id <> @@SPID
    AND es.login_name <> 'sa'
ORDER BY sr.request_id;

注意

資料移動會使用 tempdb 。若要減少資料移動期間的使用量 tempdb ，請確定您的資料表使用平均散發資料的散發策略。使用Azure Synapse SQL 散發建議程式來取得適用于您工作負載的散發方法建議。使用Azure Synapse Toolkit來監視 tempdb 使用 T-SQL 查詢。

如果您的查詢正在耗用大量的記憶體，或收到與 tempdb 配置相關的錯誤訊息，可能是因為非常大型的 CREATE TABLE AS SELECT (CTAS) 或 INSERT SELECT 陳述式執行在最後資料移動作業中失敗所致。這通常可在分散式查詢計畫中識別為最後一個 INSERT SELECT 之前的 ShuffleMove 作業。使用 sys.dm_pdw_request_steps 來監視 ShuffleMove 作業。

最常見的緩和措施是將 CTAS 或 INSERT SELECT 陳述式分割成多個載入陳述式，讓資料磁碟區不會超過每個 100DWUc tempdb 399 GB 的限制。您也可以將叢集調整為較大的大小，以增加您擁有的 tempdb 空間。

除了 CTAS 和 INSERT SELECT 陳述式，以足夠記憶體執行的大型、複雜查詢可能會溢寫到 tempdb，導致查詢失敗。請考慮以較大的資源類別來執行，以避免溢出到 tempdb。

監視記憶體

記憶體是效能緩慢及記憶體不足問題的根本原因。如果您在查詢執行期間發現 SQL Server 記憶體使用量達到其上限，請考慮調整您的資料倉儲。

下列查詢會傳回每個節點的 SQL Server 記憶體使用量和記憶體不足壓力：

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

監視交易記錄大小

下列查詢會傳回每個發佈上的交易記錄大小。如果其中一個記錄檔達到 160 GB，您應該考慮將您的執行個體相應放大或限制您交易的大小。

-- 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 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 負載

下列查詢會針對負載的進度提供大約估計。此查詢只會顯示目前正在處理的檔案。

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

監視查詢封鎖

下列查詢會提供環境中前 500 個遭到封鎖的查詢。

--Collect the top blocking
SELECT
    TOP 500 waiting.request_id AS WaitingRequestId,
    waiting.object_type AS LockRequestType,
    waiting.object_name AS ObjectLockRequestName,
    waiting.request_time AS ObjectLockRequestTime,
    blocking.session_id AS BlockingSessionId,
    blocking.request_id AS BlockingRequestId
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
WHERE
    waiting.state = 'Queued'
    AND blocking.state = 'Granted'
ORDER BY
    ObjectLockRequestTime ASC;

從等候和封鎖查詢擷取查詢文字

下列查詢提供等候和封鎖查詢的查詢文字和識別碼，以便輕鬆進行疑難排解。

-- To retrieve query text from waiting and blocking queries

SELECT waiting.session_id AS WaitingSessionId,
       waiting.request_id AS WaitingRequestId,
       COALESCE(waiting_exec_request.command,waiting_exec_request.command2) AS WaitingExecRequestText,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       waiting.object_name AS Blocking_Object_Name,
       waiting.object_type AS Blocking_Object_Type,
       waiting.type AS Lock_Type,
       waiting.request_time AS Lock_Request_Time,
       datediff(ms, waiting.request_time, getdate())/1000.0 AS Blocking_Time_sec
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
       INNER JOIN sys.dm_pdw_exec_requests waiting_exec_request
       ON waiting.request_id = waiting_exec_request.request_id
WHERE waiting.state = 'Queued'
       AND blocking.state = 'Granted'
ORDER BY Lock_Request_Time DESC;

後續步驟

如需 DMV 的詳細資訊，請參閱系統檢視。

Share via