How many concurrent query can run in 1 Azure SQL DW Connection

Question

I am new to Azure so trying to understand the Concurrency limits of Azure SQL DW: As per Concurrency limits in SQL Data Warehouse "SQL Data Warehouse allows up to 1,024 concurrent connections. All 1,024 connections can submit queries concurrently"

How many concurrent queries can run in a single connection if i take example of DW1000. is it like 32 Queries in 1 connection or only 32 Queries can run concurrently?

Thanks !!

Answer 1

Concurrency in Azure SQL Data Warehouse is fairly straightforward, but it does need you to understand a few key terms first:

1. Resource Class

Each login has associated with it a resource class. This can be one of either smallrc, mediumrc, largerc, and xlargerc. These resource classes control how much resource (eg memory, max transaction size) the login has access to during its current session. The default is smallrc. The concept is similar to Resource Governor. A user executing a query will use so many concurrency slots when executing a query, depending on their resource class:* Data correct at time of publishing but may change.

2. Concurrency slots

For each DWU, so many concurrency slots are allocated. See the chart here: * Data correct at time of publishing but may change.

Generally, higher resource classes allow more resources (or "power") but reduce concurrency.

The maximum concurrent queries executing is always 32 for DWU1000, but it can be lower depending on which resource classes you are using.

So let's do some worked examples which will hopefully aid your understanding. We can see that for DWU1000, it has a max of 32 concurrent queries with 40 concurrency slots. This gives us the following possibilities:

For your example of DWU1000, you can one of the following combinations:

1. As smallrc uses 1 concurrency slot at DWU1000, you could have a max of 32 smallrc users concurrently executing queries OR
2. As mediumrc uses 8 concurrency slots at DWU1000, you could have a max of 1 mediumrc user, with a max of 31 smallrc users concurrently executing queries OR
3. 2 mediumrc users (totalling 16 concurrency slots used) with 24 smallrc users OR
4. 3 mediumrc users (totalling 24 concurrency slots used) with 16 smallrc users OR
5. 4 mediumrc users (totalling 32 concurrency slots used) with 8 smallrc users OR ...
6. 2 largerc users (totalling 32 concurrency slots) with 8 smallrc users
7. 1 xlargerc user (totalling 32 concurrency slots) with 8 smallrc users

There are many other scenarios / combinations of resource class but hopefully you get the idea by now.

Answer 2

Concurrent queries are the queries executing at the same time. SQL Data Warehouse supports up to 32 concurrent queries on the larger DWU sizes.

Therefore only 32 queries can run concurrently in your case.

Hope it helps!