The recent announcement of Surge Protection gives Fabric/Power BI capacity admins a way to restrict the impact of background operations on a capacity, preventing them from causing throttling. However, at the time of writing, Surge Protection does not prevent users that are running expensive DAX or MDX queries – which are interactive operations – from causing problems on your capacity. Indeed, right now, there is no direct way to stop runaway queries from consuming a lot of CUs, although there is something you can do which will help a lot: reducing the query timeout.
Surge Protection doesn’t address the problem of expensive queries yet because Power BI only knows the CU usage of a DAX or MDX query when it has finished running – by which time it’s too late to do anything about it. In many cases, though, DAX or MDX queries that consume a lot of CUs are also slow. Therefore reducing the query timeout, which will kill any query that runs longer than a specified duration, will stop these queries from consuming so many CUs.
There are two default query timeouts that you should be aware of in Power BI. First, all DAX queries generated by a Power BI report have a 225 second timeout applied by the report itself. This timeout can be changed in Power BI Desktop but it cannot be changed on a published report in the Power BI Service. Second, you can set a timeout at the capacity level by changing the Query Timeout property in the admin portal. The default setting here is 3600 seconds (one hour). Unlike the first timeout, which only applies to the DAX queries generated by a Power BI report, this timeout applies to all queries run on any semantic model associated with the capacity, including the MDX queries generated by Excel PivotTables via Analyze In Excel. Setting this second timeout to less than 225 seconds means that it will take precedence over the first timeout. Therefore it’s the Query Timeout property on your capacity that you should set.
Hitting a timeout in a Power BI report will give the user a “Query has exceeded the available resources” error; clicking See Details/More Details will give you a message like this:
The XML for Analysis request timed out before it was completed. Timeout value: 10 sec.
Hitting the query timeout in an Excel PivotTable will give you the same message:
What value should you set the Query Timeout to? In my opinion no query should ever run for more than 30 seconds because anything slower will result in a poor experience for your end users – no-one wants to sit around for ages waiting for a report to render. I also think it should be possible to tune any semantic model so all queries run under 30 seconds if you know what you’re doing. That said, in the real world, setting a timeout of 30 seconds may be unrealistic: developers may not have the skills to tune their semantic models. As a result I find a timeout of 100 seconds is often a good compromise but you should experiment with different timeouts to see what the minimum value you can get away with is.
It’s important to note that reducing the query timeout will not stop every expensive query. This is because it’s perfectly possible to have very fast queries that consume a lot of CUs – for example when distinct count measures are used, and/or when there are very large data volumes and/or when there are complex but highly-optimised measures. Also there relatively rare cases where a query will carry on running beyond the duration specified by the timeout, because the Vertipaq engine only checks if the timeout has been exceeded at certain points in the code and depending on the query there could be several seconds (sometimes more) between these checks. Equally, some very slow queries may not use a lot of CUs and having them time out might cause unnecessary disruption. Overall, though, in my experience setting a timeout will stop enough expensive queries to make doing so worthwhile.
[Update: my colleague Akshai Mirchandani has just reminded me that you can also set the Query Timeout at the workspace level as a Server Property using SQL Server Management Studio, as detailed here. The property is called ServerTimeout. This gives you more flexibility than setting it for the whole capacity.]
We all know that Query Timeout is a suggestion at best. On a regular basis we observe queries blowing right past that limit, often going up to twice the distance and causing twice the damage. Whoever wrote that piece of monitoring code did never have to pay for the consequences.
It’s a trade off though. It would be possible to check more frequently but then the performance of all queries would be slower.
I can only speak for our company but we would very gladly take that performance hit, rather than helplessly watching our capacities lock up.
Portal Admin has the option to limit memory, you know any use case that reduces memory is good? I don’t see where I can use use this feature
Portal Admin has the option to limit memory, you know any use case that reduces memory is good? I don’t see where I can use use this feature
Do you mean reducing the query memory limit? Yes, in some cases reducing this limit might stop queries that use inefficient DAX calculations from running. See https://blog.crossjoin.co.uk/2024/06/23/power-bi-semantic-model-memory-errors-part-4-the-query-memory-limit/ for some context.
Hi Chris, I found the document (https://learn.microsoft.com/en-us/power-bi/enterprise/service-admin-premium-workloads?tabs=gen2#query-memory-limit) states:”Note that Power BI reports override this default with a much smaller timeout for each query to the capacity. The timeout for each query is typically about three minutes.”
Does this means that regardless of the Query Timeout setting in Capacity settings, Power BI reports published in cloud will always be subject to the default timeout of around 3 minutes per query. And the Query Timeout setting in Capacity settings seems only applies to external tools(e.g., Analyze in Excel or third-party applications)?
No – if the timeout you set on the capacity is less than 225 seconds then it will take priority, so if you set a timeout of 10 seconds on the capacity then it will apply to your Power BI reports too.
Thanks! So I understand that the Capacity Query Timeout settings override happens when the it’s less than 225 seconds. If the Capacity Query Timeout is greater than 225 seconds, then the Power BI report timeout (225 seconds) still applies.
Correct. The lowest of the two timeouts is applied.
Thank you for the reply. Based on my understanding, I’ve come across some additional points, which I hope are correct and might be helpful to others:
1. Power BI Reports: The default timeout is 225 seconds . The Capacity Query Timeout setting cannot override this if it is set to a higher value. The lower timeout always takes precedence, meaning the Capacity Query Timeout only affects Power BI reports if set below 225 seconds.
2. SSAS and AAS: The same logic applies, but their default query timeout is 600 seconds. If the Capacity Query Timeout is set below 600 seconds, it will take effect. For example, setting 225 seconds in the Capacity settings will impact SSAS/AAS queries since the lower value applies.
3. External Tools (XMLA, Analyze in Excel, etc.): There is no default limit for these tools, so any value set in the Capacity Query Timeout will apply to them.
4. Workspace-Level Query Timeout: This must be configured via SQL Server Management Studio (SSMS). Power BI tenant admins cannot enforce query timeout settings across users’ workspaces (at workspace level) unless the admins have admin access to user’s workspaces.
5. Query Limit Simulation in Power BI desktop is for Testing, no matter what we change in this, it only impact the query timeout in Power BI desktop.
Will, Query Memory Limit (%), impact background operations like a dataset refresh or does this config only affect the interactive queries?
It’s only for queries, not refreshes
Dear Chris,
this is very useful!
I have a question though: do you know of a way to get sample data from my tenant to identify the real query durations, so that I can take a qualified decision for my tenant in particular?
I am Power BI Admin, tried to use FUAM, but did not find a relevant table so far.
thanks a lot in advance!
regards
Masha