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

發行項
10/25/2024

本文說明如何使用動態管理檢視（DMV）來監視您的工作負載，包括調查專用 SQL 集區中的查詢執行。

權限

若要查詢本文中的 DMV，您需要 VIEW DATABASE STATE 或 CONTROL 許可權。通常，授 與 VIEW DATABASE STATE 是慣用的許可權，因為它更嚴格。

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 會顯示指定登入的所有查詢。

注意

預存程式使用多個要求標識碼。要求標識符會依序指派。

以下是調查特定查詢之查詢執行計劃和時間的步驟。

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

從上述查詢結果中，針對您想要調查的查詢記下要求標識符。

處於 [暫停] 狀態的查詢可能由於大量作用中的執行中查詢而排入佇列。這些查詢也會出現在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

使用要求標識碼和步驟索引，從 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：調查分散式資料庫上的數據移動

使用要求標識碼和步驟索引，從 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 Server 計劃快取中擷取 SQL Server 估計計劃，以取得特定散發中目前執行的 SQL 步驟。

-- 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。如果查詢具有所有必要的資源，則會 將狀態授與。

監視 tempdb

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

使用檢視監視tempdb

若要監視tempdb使用量，請先從 Microsoft Toolkit for SQL 集區安裝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 Distribution Advisor 來取得適用於您工作負載的散發方法建議。使用 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,
       waiting.object_name AS Waiting_Object_Name,
       waiting.object_type AS Waiting_Object_Type,
       blocking.session_id AS BlockingSessionId,
       blocking.request_id AS BlockingRequestId,
       COALESCE(blocking_exec_request.command,blocking_exec_request.command2) AS BlockingExecRequestText,
       blocking.object_name AS Blocking_Object_Name,
       blocking.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 的詳細資訊，請參閱系統檢視。

共用方式為