As a follow-up to my last blog, Azure Synapse Analytics & Power BI performance, I wanted to focus on concurrency when using these two products.
A common question I here from customers is because of the performance of Azure Synapse Analytics (formally called Azure SQL Data Warehouse or SQL DW), can they run Power BI dashboards against it using DirectQuery (and not have to use Azure Analysis Services (AAS), Import the data into Power BI, or use Power BI aggregation tables), avoiding having another copy of the data (saving money), and having data “real time” (as of the last refresh of the data warehouse)?
There are two things to think of in considering an answer this question. The first is if you will get the performance you need (discussed in my last blog), the second is if a certain amount of concurrent queries or connections will cause a problem (the subject of this blog).
In Power BI, if you use import mode or create aggregation tables, then you are not hitting SQL DW. But if you define a table to be in DirectQuery mode or Dual Storage Mode (and the query is not fulfilled in cache so switches to DirectQuery mode) then you need to worry about concurrency against SQL DW.
The first thing to be aware of is that there is a limit of 128 max concurrent queries in SQL DW and 1,024 max open concurrent connections. When the concurrency limit is exceeded, the request goes into an internal queue where it waits to be processed. When the connection limit is exceeded, connections will be refused with an error. So if you have thousands of dashboard users who will be hitting the database at the same time, you may want to leave SQL DW just for ad-hoc queries and have those dashboard users query against another source such as: data marts in Azure SQL Database, AAS tabular models (“cubes”), or within Power BI by importing the data or using Power BI aggregation tables.
This concurrent query limit is imposed since there is no resource governor or CPU query scheduler in SQL DW like there is in SQL Server. But the benefit is each SQL DW query gets its own resources (CPU, memory) and it won’t affect other queries (i.e. you don’t have to worry about a query taking all resources and blocking everyone else). There are also resource classes in SQL DW that allow more memory and CPU cycles to be allocated to queries run by a given user so they run faster, with the trade-off that it reduces the number of concurrent queries that can run.
Note that the max concurrent queries is based on the SQL DW service level (see here). Maximum Concurrent open sessions is also based on the SQL DW service level (see here). Keep in mind those concurrent queries that have to wait in the queue just means they will likely take only a few extra seconds to run due to the fast performance of SQL DW. I have seen customers running 600+ concurrent queries with no user complaints on delays due to the nature of those queries being “ad-hoc” and overall taking just a few seconds. But you would likely get complaints if these queries came from a dashboard where users are expecting millisecond response time as they slice and dice through the dashboard.
Another option mentioned before is to use Azure Analysis Services (AAS), which supports thousands of concurrent queries and connections based on the tier selected, and can support even more queries via the scale-out feature. Note there are not any hard limits for concurrent queries or connections, but certainly soft limits (based on tier/QPU and avg query duration). For example, setting a tier of 100 QPU = ~5 cores = ~5 concurrent queries, and any additional queries “wait” for resources. So while the queries don’t fail, performance and user experience suffers (this math gets a bit more complicated with the new query interleaving feature). Because AAS contains aggregated data, queries against it usually take a few milliseconds.
Be aware that SQL DW queries that hit the SQL DW result-set cache do not use any concurrency slots in Azure Synapse Analytics and thus do not count against existing concurrency limits.
Coming to Azure Synapse Analytics in the future is a feature called Multi-master cluster where user workloads can operate over the same shareable relational data set while having independent clusters to serve those various workloads. This allows for very high concurrency. This was demo’d at Ignite by Rohan Kumar showing 10k concurrent queries (video at 0:28). Two features called Workload Isolation (public preview) and Workload Importance will make this even more powerful. I’ll post a blog with more details when Azure Synapse Analytics GA’s.
