A few months ago I blogged about the new limits available for the Maximum Connections Per Data Source property in Premium and why the number of connections that a DirectQuery semantic model can open to a data source is so important for report performance. At that time, however, there was no way for you to know whether the performance of your reports was being affected by a lack of available connections. The good news is that, with the announcement this week of the new Execution Metrics event in Log Analytics and Profiler, you can now see when this is happening.
To illustrate this I created a Power BI semantic model with three tables in DirectQuery mode connected to SQL Server. Each table consisted of a single row and column and was bound to a SQL query that took 10 seconds to run (using the TSQL custom function I blogged about here).
I then built a report connected to this model containing three cards, each of which displayed the single value returned by each of these three tables. As you would expect, the DAX queries associated with each of these card visuals took 10 seconds to run when run in isolation.
I left the Max Connections Per Data Source property set to the default value of 10 and published the model and the report to the Power BI Service:
I then opened the report and let it render to make sure there were some connections open in Power BI’s connection pool, then re-ran the report while running a Profiler trace that recorded the Query Begin/End, DirectQuery Begin/End and the new Execution Metrics event.
As you might expect, the three DAX queries for the three cards were run in parallel and each query took around 10 seconds to run. Each DAX query generated a single SQL query and each SQL query also took around 10 seconds to run. Here’s what the Execution Metrics event returned for the first DAX query (the Execution Metrics for the other two DAX queries were more or less identical):
{"timeStart": "2024-05-17T10:39:53.833Z","timeEnd": "2024-05-17T10:40:04.381Z","durationMs": 10548,"datasourceConnectionThrottleTimeMs": 0,"directQueryConnectionTimeMs": 4,"directQueryExecutionTimeMs": 10105,"directQueryIterationTimeMs": 126,"directQueryTotalTimeMs": 10109,"queryProcessingCpuTimeMs": 0,"totalCpuTimeMs": 16,"executionDelayMs": 0,"approximatePeakMemConsumptionKB": 0,"directQueryTimeoutMs": 225000,"tabularConnectionTimeoutMs": 225000,"commandType": 27,"queryDialect": 3,"queryResultRows": 1,"directQueryRequestCount": 1,"directQueryTotalRows": 1}The datasourceConnectionThrottleTimeMs metric is 0, which indicates that no time was spent waiting for a connection before the model could run the sole SQL query linked to this DAX query.
I then created a copy of the semantic model, changed the Maximum Connections Per Data Source property to 1, and published this new model and an identical report and ran a Profiler trace while running the report. Changing the Maximum Connections Per Data Source property to 1 meant that this new model could only have one connection open to SQL Server at any one time and therefore could only run one SQL query at a time.
Here’s what the Profiler trace looked like:
The first thing to note is that even though the three DAX queries were still run in parallel they now took 10, 21 and 33 seconds to run, as seen from the Duration column for each of the three Query End events. The SQL queries generated by each DAX query, however, still only took 10 seconds each, as shown from the Duration column for the three DirectQuery End events.
Here’s what the Execution Metrics event for the last DAX query to finish (the one that took 33 seconds) returned:
{"timeStart": "2024-05-17T10:12:19.436Z","timeEnd": "2024-05-17T10:12:52.341Z","durationMs": 32906,"datasourceConnectionThrottleTimeMs": 21896,"directQueryConnectionTimeMs": 21901,"directQueryExecutionTimeMs": 10531,"directQueryIterationTimeMs": 129,"directQueryTotalTimeMs": 32434,"queryProcessingCpuTimeMs": 0,"totalCpuTimeMs": 0,"executionDelayMs": 9,"approximatePeakMemConsumptionKB": 0,"directQueryTimeoutMs": 204000,"tabularConnectionTimeoutMs": 225000,"commandType": 27,"queryDialect": 3,"queryResultRows": 1,"directQueryRequestCount": 1,"directQueryTotalRows": 1}This clearly shows that out of the total query duration of 33 seconds, 22 seconds of that was spent waiting for a connection to become available, as indicated by the datasourceConnectionThrottleTimeMs value. This was because this DAX query had to wait for the SQL queries generated by the two other DAX queries to complete before it could get a connection back to the source. As for the other two DAX queries generated for this report, the query that took 10 seconds still had a datasourceConnectionThrottleTimeMs of 0 because it was the first query to run and there was a connection available for it to use, while the second query to run that took 22 seconds had a datasourceConnectionThrottleTimeMs of 11 seconds because it only had to wait for the first DAX query’s SQL query to run before it could use the connection.
What should you do if you have performance problems and can see that datasourceConnectionThrottleTimeMs is consistently high? Before anything else you need to tune your model and your data source (see here and here for some tips); the more SQL queries your model generates when a report is run, and the slower those queries are, the more likely you are to run out of connections. If that doesn’t work and you are using Pro/Shared or a capacity that is lower than an F64, then you will need buy an F64 capacity or greater (see the table here for the maximum number of DirectQuery connections supported by each SKU) all to allow for more than 10 connections back to your source. Once you have done that you will also need to set the Max Connections Per Data Source property in your model to something larger than 10. However, you also need to be aware that increasing the maximum number of connections can make performance worse if your data source cannot handle the number of SQL queries that Power BI is trying to run on it.
