cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
precio_96
Frequent Visitor

SQL Timeout

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.

 

errorpbi.PNG

 

Thanks in advance!

Best regards,

Pablo

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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
Super User
Super User

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.

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Solution Authors
Top Kudoed Authors