Analyze your workload for dedicated SQL pool in Azure Synapse Analytics

Techniques for analyzing your dedicated SQL pool workload in Azure Synapse Analytics.

Resource Classes

Dedicated SQL pool 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.

Queued query detection and other DMVs

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
;

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

Dedicated SQL pool has the following wait types:

• LocalQueriesConcurrencyResourceType: Queries that sit outside of the concurrency slot framework. DMV queries and system functions such as SELECT @@VERSION are examples of local queries.
• UserConcurrencyResourceType: Queries that sit inside the concurrency slot framework. Queries against end-user tables represent examples that would use this resource type.
• DmsConcurrencyResourceType: Waits resulting from data movement operations.
• BackupConcurrencyResourceType: This wait indicates that a database is being backed up. The maximum value for this resource type is 1. If multiple backups have been requested at the same time, the others queue. In general, we recommend a minimum time between consecutive snapshots of 10 minutes.

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

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. 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();

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

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

For more information about managing database users and security, see Secure a dedicated SQL pool (formerly SQL DW). For more information about how larger resource classes can improve clustered columnstore index quality, see Rebuilding indexes to improve segment quality.