SQL Timeout

[precio_96]

Hi everyone,

Hope you are having a great day.

I am stuck in a problem with most of my reports in Power BI. I am struggling to make the automatic refresh to work due to SQL Server timeouts.

If I check most of the queries in SSMS they run normally and return results in 1/2 seconds, but in Power BI service seems like they never end and in Power BI Desktop they get stuck in Evaluating.

Some background, we have the Power BI Gateaway and SQL Server in the same AWS instance, which runs a Windows Server 2021 R2 with 64GB RAM and AMD Epyc 7571 at 2.2ghz (M5a.4XLarge AMI in AWS).

So my question is: Is there any configuration to check? Could it be something like tables lock in SQL? Any ideas/suggestion to improve the connection? I have already set the timeout in PBI to 2hrs.

Thanks in advance!

Best regards,

Pablo

[Anonymous]

Hi  @precio_96 ,

1. In power bi desktop, you can optimize your data model by following the guide in this blog.

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

2. Based on this blog, in Power BI Service, refreshing the dataset will give an error message letting you know your dataset is too large, and it will fail to refresh until you filter the data model back down under 1 GB. You can rename your current PBIX file to .zip and check the size of the data model within it. For more details, please review this blog. If the data model size is too large to refresh in Power BI Service, please break up your dataset into smaller datasets and refresh them in Power BI Service.

This is the related document, you can view this content:

https://community.powerbi.com/t5/Desktop/quot-Microsoft-SQL-Execution-Timeout-Expired/m-p/831355

https://www.codeproject.com/questions/498101/timeoutplusexpired-plusplustheplustimeoutplusperio

https://blog.sqlauthority.com/2016/01/26/sql-server-timeout-expired-the-timeout-period-elapsed-prior...

https://kb.blackbaud.com/knowledgebase/Article/94436

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Install SSMS or ADS etc on the gateway VM and make sure that you can see the SQL server from there

Note there are two timeouts - connection timeout and command timeout. Also the SQL server command timeout overrides the Power BI command timeout.