As a follow-up to my final blog, Azure Synapse Analytics & Power BI performance, I wanted to the cognizance of concurrency when using those products.

A common question I right here from customers is because of the overall performance of Azure Synapse Analytics, can they run Power BI dashboards in opposition to it the use of DirectQuery, heading off having another reproduction of the information, and having information “real-time”?

There are matters to think about in thinking about a solution to this question. The first is if you will get the overall performance you want, the second one is if a certain quantity of concurrent queries or connections will purpose a problem.

In Power BI, in case 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 then you want to worry about concurrency towards SQL DW.

The first issue to be aware of is that there may be 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 is going into an internal queue wherein it waits to be processed. When the connection restricts is exceeded, connections might be refused with an error. So when you have heaps of dashboard users who can be hitting the database at the identical time, you may need to go away SQL DW just for ad-hoc queries and have the one's dashboard users question against some other source such as information marts in Azure SQL Database, AAS tabular models, or inside Power BI with the aid of uploading the statistics or using Power BI aggregation tables.

This concurrent question limit is imposed since there is no aid governor or CPU query scheduler in SQL DW like there's in SQL Server. But the gain is each SQL DW question receives its sources and it won’t affect other queries. There are also useful resource classes in SQL DW that permit more reminiscence and CPU cycles to be allocated to queries run via a given person so they run faster, with the trade-off that it reduces the variety of concurrent queries that may run.

Note that the max concurrent queries are based totally on the SQL DW service level. Maximum Concurrent open classes are also based totally on the SQL DW provider level. Keep in thoughts those concurrent queries that have to wait in the queue simply means they'll in all likelihood take only a few more seconds to run due to the fast performance of SQL DW. I have seen customers walking 600+ concurrent queries with no user proceedings on delays because of the nature of these queries being “ad-hoc” and overall taking only some seconds. But you'll probably get lawsuits if these queries got here from a dashboard wherein customers are anticipating millisecond response time as they slice and cube through the dashboard. Read more Power BI Course 

Another option noted earlier than is to use Azure Analysis Services (AAS), which supports lots of concurrent queries and connections primarily based at the tier selected, and can help even more queries through the scale-out feature. Note there are not any hard limits for concurrent queries or connections, but tender limits. For example, putting a tier of a hundred QPU = ~5 cores = ~5 concurrent queries, and any additional queries “wait” for sources. So whilst the queries don’t fail, overall performance and user enjoy suffers. Because AAS contains aggregated information, queries against it usually take some milliseconds.

Be aware that SQL DW queries that hit the SQL DW result-set cache does not use any concurrency slots in Azure Synapse Analytics and therefore do not count in opposition to existing concurrency limits (just like a query to a DMV does no longer count towards a concurrency slot). However, it does count towards the 1,024 max open concurrent connections.

Coming to Azure Synapse Analytics within the future is a feature referred to as a Multi-grasp cluster where consumer workloads can operate over the same shareable relational information set whilst having unbiased clusters to serve those numerous workloads. This permits for very excessive concurrency. This becomes demoed at Ignite employing Rohan Kumar displaying 10k concurrent queries. Two functions referred to as Workload Isolation and Workload Importance will make this even greater power. I’ll publish a blog with extra info when Azure Synapse Analytics GA’s.

If you want to learn about Power bi with Azure, then Getting Started with Power BI online training Hyderabad in onlineitguru is an excellent course to start with.