你当前正在访问 Microsoft Azure Global Edition 技术文档网站。 如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站,请访问 https://docs.azure.cn。
使用 DMV 监视 Azure Synapse Analytics 专用 SQL 池工作负载
本文介绍了如何使用动态管理视图 (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
在每次进行新的查询时按顺序分配,并会加上代表查询 ID 的前缀 QID。 在此 DMV 中查询给定 session_id
会显示给定登录的所有查询。
注意
存储过程使用多个请求 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:调查查询计划
使用请求 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 步骤,也可能是一个步骤占用很长的时间。 如果计划有很多步骤,包含多个移动操作,可考虑优化表分布,减少数据移动。 表分布一文说明了为何必须移动数据才能解决查询。 该文还介绍了一些用于最大程度减少数据移动的分布策略。
若要进一步调查单个步骤的详细信息,请检查长时间运行的查询步骤的 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 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。 如果查询具有全部所需资源,则状态为 Granted。
监视 tempdb
tempdb
用于在查询执行期间保存临时结果。 重度使用 tempdb
数据库可能会导致查询性能变慢。 对于所配置的每个 DW100c,将分配 399 GB 的 tempdb
空间(DW1000c 的总 tempdb
空间为 3.99 TB)。 下面是有关监视 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
的使用,请确保表使用均匀分发数据的分发策略。
如果某个查询消耗了大量内存或收到了与 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 的详细信息,请参阅系统视图。