sys.dm_pdw_resource_waits (Transact-SQL)

  • 2 minutes to read

APPLIES TO: noSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

Displays wait information for all resource types in SQL Data Warehouse.

Column Name Data Type Description Range
wait_id bigint Position of the request in the waiting list. 0-based ordinal. This is not unique across all wait entries.
session_id nvarchar(32) ID of the session in which the wait state occurred. See session_id in sys.dm_pdw_exec_sessions (Transact-SQL).
type nvarchar(255) Type of wait this entry represents. Possible values:

Connection

Local Queries Concurrency

Distributed Queries Concurrency

DMS Concurrency

Backup Concurrency
object_type nvarchar(255) Type of object that is affected by the wait. Possible values:

OBJECT

DATABASE

SYSTEM

SCHEMA

APPLICATION
object_name nvarchar(386) Name or GUID of the specified object that was affected by the wait. Tables and views are displayed with three-part names.

Indexes and statistics are displayed with four-part names.

Names, principals, and databases are string names.
request_id nvarchar(32) ID of the request on which the wait state occurred. QID identifier of the request.

GUID identifier for load requests.
request_time datetime Time at which the lock or resource was requested.
acquire_time datetime Time at which the lock or resource was acquired.
state nvarchar(50) State of the wait state. Information not available.
priority int Priority of the waiting item. Information not available.
concurrency_slots_used int Internal See the Monitor resource waits below
resource_class nvarchar(20) Internal See the Monitor resource waits below

Monitor resource waits

With the introduction of workload groups, concurrency slots are no longer applicable. Use the below query and the resources_requested column to understand the resources needed to execute the request.

select rw.wait_id
      ,rw.session_id
      ,rw.type
      ,rw.object_type
      ,rw.object_name
      ,rw.request_id
      ,rw.request_time
      ,rw.acquire_time
      ,rw.state
      ,resources_requested = s.effective_request_min_resource_grant_percent
      ,r.group_name
  from sys.dm_workload_management_workload_groups_stats s
  join sys.dm_pdw_exec_requests r on r.group_name = s.name collate SQL_Latin1_General_CP1_CI_AS
  join sys.dm_pdw_resource_waits rw on rw.request_id = r.request_id

See Also

SQL Data Warehouse and Parallel Data Warehouse Dynamic Management Views (Transact-SQL)