Azure Synapse Analytics concurrency slots are the number of queries that can be executed in your database at the same time. The number of concurrency slots depends on how many resources you allocate to your Azure Synapse Analytics Dedicated SQL Pool.
In this blog post, you’ll learn how to monitor the concurrent slots of your Azure Synapse Analytics SQL Pool.
Table of Contents
Azure Synapse Analytics Concurrency Slots
To begin, in Azure Synapse Analytics, the number of concurrent slots depends on the Data Warehouse Units (DWUs) that you have allocated to a Dedicated SQL Pool.
When you have many queries running simultaneously in a database, if there are enough concurrency slots available, some of the queries will be queued.
It is very important to monitor if your queries are being queued. This will help you optimize workloads.
A query can open multiple sessions in a database. The maximum number of concurrent open sessions depends on DWUs as and this is a hard limit. Sessions might be rejected if you exceed this number.
For example: For less than DWU500c, the maximum is 512. Here is some more information about limits in the workload management documentation.
First, check the maximum number of concurrent queries in Azure Synapse Analytics in the following table:
| Data Warehouse Units | Maximum concurrent queries |
| DW100c | 4 |
| DW200c | 8 |
| DW300c | 12 |
| DW400c | 16 |
| DW500c | 20 |
| DW1000c | 32 |
| DW1500c | 32 |
| DW2000c | 48 |
| DW2500c | 48 |
| DW3000c | 64 |
| DW5000c | 64 |
| DW6000c | 128 |
| DW7500c | 128 |
| DW10000c | 128 |
| DW15000c | 128 |
| DW30000c | 128 |
With the release of workload groups, instead of using slots/concurrent queries, a percentage is allocated instead of a maximum number.
For example:
- The minimum % for DW100c is 25% (which with concurrent queries is 25% x 4 concurrent queries = 100%)
- The minimum % for DW1000c is 3% (which with concurrent queries is 3% rounded x 32 concurrent queries = 96%)
Azure Synapse Analytics Active Queries
Second, to monitor active Azure Synapse Analytics queries, you can query sys.dm_pdw_exec_requests
-- Monitor active queries
SELECT *
FROM sys.dm_pdw_exec_requests
WHERE status not in ('Completed','Failed','Cancelled')
AND session_id <> session_id()
ORDER BY submit_time DESC;
Azure Synapse Analytics Identifying Queued Queries
Now, to monitor if your Azure Synapse Analytics queries are being queued, you can execute the following queries.
Queries that are waiting in a concurrency queue:
-- Queries that are waiting in a concurrency queue (or for other resources)
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(MILLISECOND, [submit_time], [start_time]) AS Request_InitiateDuration_ms
,DATEDIFF(MILLISECOND, [submit_time], GETDATE()) / 1000.0 AS Queued_sec
,r.resource_class AS Request_resource_class
FROM sys.dm_pdw_exec_requests r
WHERE r.[status] = 'Suspended';
Query to identify which resource types queries are waiting for:
-- Monitor the queries that are queued and for how long
SELECT
*
, [queued_sec] = DATEDIFF(MILLISECOND, request_time, GETDATE()) / 1000.0
FROM
sys.dm_pdw_resource_waits
WHERE
[state] = 'Queued'
Azure Synapse Analytics Concurrency Slots Being Used
Finally, you can calculate how many concurrency slots are being used by executing the following query and comparing it with the maximum number of concurrent queries in the table mentioned before.
-- Monitor calculate how many concurrency slots are being used
SELECT isnull(sum([concurrency_slots_used]),0) as total_granted_slots
FROM sys.[dm_pdw_resource_waits]
WHERE [state] = 'Granted'
AND [resource_class] is not null
AND [session_id] <> session_id()
Summary
To summarize, queued queries are really important in any database engine, like in SQL Server. Monitoring your Azure Synapse Analytics is important to identify points of improvement and avoid waiting times when executing queries or queries being rejected by the database.
WHAT’S NEXT?
In upcoming blog posts, we’ll continue to explore some of the features within Azure Services.
Please follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!
As always, please leave any comments or questions below.
