SQL database DTUs affecting the Powerbi reports resfresh

Question

I have created about 5 reports in Microsoft PowerBI using a SQL database created in Microsoft Azure. The database has more than 50 million row. Recently my reports have stopped refreshing. In case they refresh, the refresh time is long and is running really slow. here is a screenshot of the error i'm having enter image description here

I contacted yesterday Microsoft PowerBI to check if the issue is from the software itself. I showed them my database and my reports and they told me that the DTU in my SQL database is reaching a maximum of 100% which is slowing down the response of the database during the refresh and preventing it from performing well. Here is a screenshot of my database performance enter image description here. Please note that this picture is showing only the maximum of the DTUs, the average is giving a 50% value

I'm not an expert in Azure and i need to know if the DTUs can really effect the performance of calling the data from the database to Powerbi.

Answer 1

Yes, the DTUs will affect the query performance for Azure SQL database. Off course it will effect the performance of calling the data from the database to PowerBi.

Reference Database transaction units (DTUs):

A database transaction unit (DTU) represents a blended measure of CPU, memory, reads, and writes.

For a single database at a specific compute size within a service tier, Microsoft guarantees a certain level of resources for that database (independent of any other database in the Azure cloud). This guarantee provides a predictable level of performance. The amount of resources allocated for a database is calculated as a number of DTUs and is a bundled measure of compute, storage, and I/O resources.

The ratio among these resources is originally determined by an online transaction processing (OLTP) benchmark workload designed to be typical of real-world OLTP workloads. When your workload exceeds the amount of any of these resources, your throughput is throttled, resulting in slower performance and time-outs.

When the DTUs reaching a maximum of 100%, it means the performance of the database has reached the resource limits.

You need to scale the Azure SQL database service price tier or do a performance turning.

For more details, please see: Monitoring and performance tuning. Azure SQL Database provides tools and methods you can use to monitor usage easily, add or remove resources (such as CPU, memory, or I/O), troubleshoot potential problems, and make recommendations to improve the performance of a database.

Hope this helps.