How many queries and slots are running in my Azure SQL Data Warehouse?

  • Article

Azure SQL Data Warehouse allows you to manage your workload using resource classes and concurrency slots for query execution. In the previous blog, Checking for Queued Queries, we showed you how to check to see if your query was queued and awaiting execution. That's great to know why your query hasn't started executing yet but what if you want to know what is running in your data warehouse. There is a very simple way to do this via T-SQL - just query the sys.dm_pdw_exec_requests and sys.dm_pdw_resource_waits views.

Using these two views, we can get a sense of the running queries from the dm_pdw_exec_requests view and then merge that with any queued information from the dm_pdw_resource_waits view. Using the running_queued_queries_slots T-SQL script, we can see the number of running queries, how many concurrency slots those queries are taking, the number of queued queries and how many slots the queued queries will need.

 SELECT
 SUM(CASE WHEN r.[status] = 'Running' THEN 1 ELSE 0 END) [running_queries],
 SUM(CASE WHEN r.[status] = 'Running' THEN rw.concurrency_slots_used ELSE 0 END) [running_queries_slots],
 SUM(CASE WHEN r.[status] = 'Suspended' THEN 1 ELSE 0 END) [queued_queries],
 SUM(CASE WHEN rw.[state] = 'Queued' THEN rw.concurrency_slots_used ELSE 0 END) [queued_queries_slots]
FROM
 sys.dm_pdw_exec_requests r 
 JOIN sys.dm_pdw_resource_waits rw ON rw.request_id = r.request_id
WHERE
 ( (r.[status] = 'Running' AND r.resource_class IS NOT NULL ) OR r.[status] ='Suspended' )
 AND rw.[type] = 'UserConcurrencyResourceType';

This query returns four columns:

  • running_queries - this is the number of queries actively executing in the data warehouse.
  • running_queries_slots - this is the number of concurrency slots consumed by the running queries.
  • queued_queries - this is the number of queries queued.
  • queued_queries_slots - this is the number of concurrency slots requested by the queued queries.

This example shows 4 running queries each consuming 8 concurrency slots with 6 additional queries each requesting 8 concurrency slots.

T-SQL for monitoring queries

SQL Data Warehouse Git Hub repo: https://github.com/Microsoft/sql-data-warehouse-samples