Azure Synapse Analytics Queries #8 Concurrency Slots

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. 

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 
DW200c 
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 Active Queries 

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

				
			

 

Azure Synapse Analytics Identifying Queued Queries 

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

				
			

Azure Synapse Analytics Concurrency Slots Being Used 

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.    

Check out these other posts

comment [ 0 ]
share
No tags 0

No Comments Yet.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *