使用 DMV 監視您的 Azure Synapse Analytics 專用 SQL 集區工作負載
此文章說明如何使用動態管理檢視 (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 的詳細資訊,請參閱系統檢視。