Power BI Incremental Refresh – Long-Running SQL Query Causing DB Blocking

Hi manoj_0911​

Based on the information provided, I cannot personally yet determine why the Summary & Detail report is generating a full dataset query while the Queue report is issuing a MAX(UPDATE_DATETIME) query.

To move forward, I would recommend confirming the following:

• Whether the CALL_DATE incremental refresh filter is fully folding to SQL Server for both reports
• Whether Detect Data Changes is configured identically in both datasets
• Whether Power BI is refreshing only the expected 7day partitions or triggering refreshes on additional partitions
• Whether the underlying views (LOS_REPORT_V and LOS_QUEUE_REPORT_V) behave differently when the date filter is applied

The key question is whether Power BI is pushing the date filter down to SQL Server before evaluating the view. If not, the SQL Server engine may need to process a much larger portion of the view, which could explain the long-running queries and blocking reported by the DBA team.

Reviewing the refresh traces, generated SQL, and query folding behavior for both datasets should help identify the root cause before any configuration changes are made.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Let's connect on LinkedIn
🟧 Feel free to join this LinkedIn Group for Data Enthusiasts.

Hello manoj_0911​ 

The different SQL queries are likely normal, one checks whether data has changed, while the other loads the affected partition. The date filter is reaching SQL Server, but the underlying view may still perform expensive scans or joins that cause blocking. We need to check the actual query dates to confirm that only the expected seven-day window is refreshed. Before changing the configuration, review execution plans and indexes, stagger the refresh schedules, and test whether change detection reduces or adds to the workload.

 

If my response helped you, please consider clicking
Accept as Solution and giving it a Like – it helps others in the community too.

Thanks,

Connect with me on:
LinkedIn |
Data With Pankaj - YouTube

Hi manoj_0911​,

Based on the behavior, I would first verify whether the CALL_DATE filter is folding correctly to SQL Server. If the generated query contains the expected CALL_DATE >= RangeStart AND CALL_DATE < RangeEnd filter, Power BI is requesting only the configured 7-day refresh window, even if the SELECT statement contains many columns.

I would also temporarily disable Detect Data Changes (UPDATE_DATETIME) and compare the refresh duration and SQL impact. This will help determine whether change detection is contributing to the additional workload.

If the 7-day query is still causing significant blocking, I would then investigate the SQL view execution plan and indexing, particularly around CALL_DATE and the underlying joins.

As an alternative, Tabular Editor/XMLA partition management with a Fabric Notebook can be used to refresh only the latest partition(s). This gives more control over exactly what gets refreshed and avoids unnecessary partition processing, but it will not resolve an inefficient SQL view or poor query plan by itself.

So I would recommend: validate folding → test without Detect Data Changes → review SQL execution plan/indexes → consider notebook-driven partition refresh if required.


Please mark this post as solution if it helps you. Appreciate Kudos.

HI manoj_0911​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya

HI manoj_0911​ ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

Regards,
Chaithanya