Dela via

Checking for queued queries in Azure SQL Data Warehouse

  • Artikel

Azure SQL Data Warehouse manages query workload through a combination of concurrent query execution slots and resources classes. The SQL Data Warehouse workload manager enables you to manage your workload within your data warehouse that are contending for system resources. SQL Data Warehouse defines a set of concurrency slots for query execution. The system manages the resources consumed by each query to ensure proper system balance. You can see the concurrency slots per DWU setting on the Concurrency Maximums article. If you issue enough queries that require system resources from the distributed nodes, SQL Data Warehouse will queue the queries until enough system resources/concurrency slots are available.

T-SQL for monitoring queries

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

Using the sys.dm_pdw_resource_waits view, you can craft a query to see if any query is queued waiting for system resources. Using the sample queued_queries T-SQL script, we can see what is waiting for execution.

 
            
SELECT
    *
    , [queued_sec] = DATEDIFF(MILLISECOND, request_time, GETDATE()) / 1000.0
FROM
    sys.dm_pdw_resource_waits
WHERE
    [state] = 'Queued'
ORDER BY
    queued_sec DESC;

The results lists all queries that are queued with the number of concurrency slots requested and the duration of the queue.

queued