Multiple concurrent Power BI users againts Azure SQL Data Warehouse

[octoni]

Hi ,

Does anyknow know any official references that covering what is need to be considered when choosing Azure SQL Data warehouse as main data source read by Power BI service using Direct Query ? e.g. since Azure SQL Data Warehouse has limitiation of 32 conccurent queries, and there will be probably more that 32 conccurent Power BI users doing some slide and dice analysis over it , will it degrade performance of one or both components ?

really appreciate hearing for any thought. thanks

regards, Octoni

[v-shex-msft · Accepted solution]

Hi @octoni,

I think you misunderstood on concurrent connections limit.

Based on document, current maximum of SQL Data Warehouse concurrent connections are allows up to 1,024. Which you mentioned 32 concurrent connections amount limit is only contains on larger DWU sizes.

In my opinion, you not need to care about concurrent connections on normal data amount.

Reference link:

Concurrency and workload management in SQL Data Warehouse

Concurrency limits

SQL Data Warehouse allows up to 1,024 concurrent connections. All 1,024 connections can submit queries concurrently. However, to optimize throughput, SQL Data Warehouse may queue some queries to ensure that each query receives a minimal memory grant. Queuing occurs at query execution time. By queuing queries when concurrency limits are reached, SQL Data Warehouse can increase total throughput by ensuring that active queries get access to critically needed memory resources.

Concurrency limits are governed by two concepts: concurrent queries and concurrency slots. For a query to execute, it must execute within both the query concurrency limit and the concurrency slot allocation.

• Concurrent queries are the queries executing at the same time. SQL Data Warehouse supports up to 32 concurrent queries on the larger DWU sizes.
• Concurrency slots are allocated based on DWU. Each 100 DWU provides 4 concurrency slots. For example, a DW100 allocates 4 concurrency slots and DW1000 allocates 40. Each query consumes one or more concurrency slots, dependent on the resource class of the query. Queries running in the smallrc resource class consume one concurrency slot. Queries running in a higher resource class consume more concurrency slots.

The following table describes the limits for both concurrent queries and concurrency slots at the various DWU sizes.

Concurrency limits

DWU Max concurrent queries Concurrency slots allocated

DW100	4	4
DW200	8	8
DW300	12	12
DW400	16	16
DW500	20	20
DW600	24	24
DW1000	32	40
DW1200	32	48
DW1500	32	60
DW2000	32	80
DW3000	32	120
DW6000	32	240

BTW, you can also contact to power bi team to know more about this limit.

Regards,

Xiaoxin Sheng