在 Azure Synapse Analytics 中分析工作负荷Analyze your workload in Azure Synapse Analytics

在 Azure Synapse Analytics 中分析 Synapse SQL 工作负荷的技巧。Techniques for analyzing your Synapse SQL workload in Azure Synapse Analytics.

资源类Resource Classes

Synapse SQL 提供了资源类来将系统资源分配给查询。Synapse SQL provides resource classes to assign system resources to queries. 有关资源类的详细信息,请参阅资源类和工作负荷管理For more information on resource classes, see Resource classes & workload management. 如果分配给查询的资源类需要的资源超出目前能够提供的量,则查询会等待。Queries will wait if the resource class assigned to a query needs more resources than are currently available.

对排队的查询进行的检测,以及其他 DMVQueued query detection and other DMVs

可以使用 sys.dm_pdw_exec_requests DMV 来确定在并发队列中等待的查询。You can use the sys.dm_pdw_exec_requests DMV to identify queries that are waiting in a concurrency queue. 正在等待并发槽的查询的状态为“已挂起” 。Queries waiting for a concurrency slot have a status of suspended.

SELECT  r.[request_id]                           AS Request_ID
,       r.[status]                               AS Request_Status
,       r.[submit_time]                          AS Request_SubmitTime
,       r.[start_time]                           AS Request_StartTime
,       DATEDIFF(ms,[submit_time],[start_time])  AS Request_InitiateDuration_ms
,       r.resource_class                         AS Request_resource_class
FROM    sys.dm_pdw_exec_requests r
;

可以使用 sys.database_principals来查看工作负荷管理角色。Workload management roles can be viewed with sys.database_principals.

SELECT  ro.[name]           AS [db_role_name]
FROM    sys.database_principals ro
WHERE   ro.[type_desc]      = 'DATABASE_ROLE'
AND     ro.[is_fixed_role]  = 0
;

以下查询显示分配给每个用户的角色。The following query shows which role each user is assigned to.

SELECT  r.name AS role_principal_name
,       m.name AS member_principal_name
FROM    sys.database_role_members rm
JOIN    sys.database_principals AS r            ON rm.role_principal_id      = r.principal_id
JOIN    sys.database_principals AS m            ON rm.member_principal_id    = m.principal_id
WHERE   r.name IN ('mediumrc','largerc','xlargerc')
;

Synapse SQL 具有以下等待类型:Synapse SQL has the following wait types:

  • LocalQueriesConcurrencyResourceType:位于并发槽框架外部的查询。LocalQueriesConcurrencyResourceType: Queries that sit outside of the concurrency slot framework. DMV 查询和 SELECT @@VERSION 等系统函数是本地查询的示例。DMV queries and system functions such as SELECT @@VERSION are examples of local queries.
  • UserConcurrencyResourceType:位于并发槽框架内部的查询。UserConcurrencyResourceType: Queries that sit inside the concurrency slot framework. 针对最终用户表的查询代表使用此资源类型的示例。Queries against end-user tables represent examples that would use this resource type.
  • DmsConcurrencyResourceType:数据移动操作导致的等待。DmsConcurrencyResourceType: Waits resulting from data movement operations.
  • BackupConcurrencyResourceType:此等待表明正在备份数据库。BackupConcurrencyResourceType: This wait indicates that a database is being backed up. 此资源类型的最大值为 1。The maximum value for this resource type is 1. 如果同时请求了多个备份,则其他备份会排队。If multiple backups have been requested at the same time, the others queue. 通常情况下,建议连续快照的最小间隔时间为 10 分钟。In general, we recommend a minimum time between consecutive snapshots of 10 minutes.

可以使用 sys.dm_pdw_waits DMV 来查看请求所等待的具体资源。The sys.dm_pdw_waits DMV can be used to see which resources a request is waiting for.

SELECT  w.[wait_id]
,       w.[session_id]
,       w.[type]                                           AS Wait_type
,       w.[object_type]
,       w.[object_name]
,       w.[request_id]
,       w.[request_time]
,       w.[acquire_time]
,       w.[state]
,       w.[priority]
,       SESSION_ID()                                       AS Current_session
,       s.[status]                                         AS Session_status
,       s.[login_name]
,       s.[query_count]
,       s.[client_id]
,       s.[sql_spid]
,       r.[command]                                        AS Request_command
,       r.[label]
,       r.[status]                                         AS Request_status
,       r.[submit_time]
,       r.[start_time]
,       r.[end_compile_time]
,       r.[end_time]
,       DATEDIFF(ms,r.[submit_time],r.[start_time])        AS Request_queue_time_ms
,       DATEDIFF(ms,r.[start_time],r.[end_compile_time])   AS Request_compile_time_ms
,       DATEDIFF(ms,r.[end_compile_time],r.[end_time])     AS Request_execution_time_ms
,       r.[total_elapsed_time]
FROM    sys.dm_pdw_waits w
JOIN    sys.dm_pdw_exec_sessions s  ON w.[session_id] = s.[session_id]
JOIN    sys.dm_pdw_exec_requests r  ON w.[request_id] = r.[request_id]
WHERE    w.[session_id] <> SESSION_ID()
;

sys.dm_pdw_resource_waits DMV 显示给定查询的等待信息。The sys.dm_pdw_resource_waits DMV shows the wait information for a given query. 资源等待时间度量等待提供资源的时间。Resource wait time measures the time waiting for resources to be provided. 信号等待时间是基础 SQL Server 将查询调度到 CPU 所需的时间。Signal wait time is the time it takes for the underlying SQL servers to schedule the query onto the CPU.

SELECT  [session_id]
,       [type]
,       [object_type]
,       [object_name]
,       [request_id]
,       [request_time]
,       [acquire_time]
,       DATEDIFF(ms,[request_time],[acquire_time])  AS acquire_duration_ms
,       [concurrency_slots_used]                    AS concurrency_slots_reserved
,       [resource_class]
,       [wait_id]                                   AS queue_position
FROM    sys.dm_pdw_resource_waits
WHERE    [session_id] <> SESSION_ID()
;

还可以使用 sys.dm_pdw_resource_waits DMV 计算已授予的并发槽数。You can also use the sys.dm_pdw_resource_waits DMV calculate how many concurrency slots have been granted.

SELECT  SUM([concurrency_slots_used]) as total_granted_slots
FROM    sys.[dm_pdw_resource_waits]
WHERE   [state]           = 'Granted'
AND     [resource_class] is not null
AND     [session_id]     <> session_id()
;

可以使用 sys.dm_pdw_wait_stats DMV 对等待进行历史趋势分析。The sys.dm_pdw_wait_stats DMV can be used for historic trend analysis of waits.

SELECT   w.[pdw_node_id]
,        w.[wait_name]
,        w.[max_wait_time]
,        w.[request_count]
,        w.[signal_time]
,        w.[completed_count]
,        w.[wait_time]
FROM    sys.dm_pdw_wait_stats w
;

后续步骤Next steps

有关如何管理数据库用户和安全性的详细信息,请参阅在 Synapse SQL 中保护数据库For more information about managing database users and security, see Secure a database in Synapse SQL. 若要进一步了解如何通过更大型资源类来改进聚集列存储索引质量,请参阅重建索引以提升段质量For further information about how larger resource classes can improve clustered columnstore index quality, see Rebuilding indexes to improve segment quality.