SQL Server Direct Query

[ESG-DKnight]

Good Afternoon Everyone,

I've got several PBIX's where the Data Source is a Direct Query into a Linked Server from a SQL Server environment.

I have two SQL Server environments I can choose from. I can point my PBIX are either environment and I'm able to retrieve data no problem.

However if I wish to add a Calculated Measure to my PBIX only one of the SQL Environments will fully resolve the request and update the PBIX. While connected to the other SQL Environment the PBIX will just continue to refresh until it eventually times out.

Does anyone have any expereince with this issue? I'm assuming that something is wrong with my second SQL Environment, but after plaing "spot the difference" between the two environments I can't find an obvious root cause.

Any assistance that you can offer would be greatly appreciated.

Thank you,

Daniel.

[v-stephen-msft]

Hi @ESG-DKnight ,

Probably because two SQL Environments are different.

When you use DirectQuery, the overall experience depends on the performance of the underlying data source. If refreshing each visual, for example after changing a slicer value, takes less than five seconds, the experience is reasonable, although might feel sluggish compared to the immediate response with imported data. If the slowness of the source causes individual visuals to take longer than tens of seconds to refresh, the experience becomes unreasonably poor. Queries might even time out.

Along with the performance of the underlying source, the load placed on the source also impacts performance. Each user who opens a shared report, and each dashboard tile that refreshes, sends at least one query per visual to the underlying source. The source must be able to handle such a query load while maintaining reasonable performance.

When you define the model, follow this guidance to optimize performance::

Model Desgin

When you create a report that uses a DirectQuery connection, follow this guidance to optimize performance:

Report design

Best Regards,

Stephen Tao

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

[ESG-DKnight]

Thank you for your feedback Stephen. I've reviewed both links and after checking both my model and report settings conformed with your suggestions it unfortunately resulted in no change to my PBIX. There must be something wrong with the new SQL Source.

[ESG-DKnight]

Those were my thoughts as well, but I don't know enough about SQL to really know where to begin. Do you know of any SQL experts who may be willing to assist?

[ESG-DKnight]

Hi,

The Size of the Source and the Queries being run in both environments are identical, it's just a case that everything works correctly in one enviroment, and run's until timeout in the other environment.

Thank you,

[christinepayton]

Then I'd guess it would be something to do with the configuration of the second environment, if it has less resources available or the table is locked while another task is running or something like that. 

[christinepayton]

With direct query, the size of the source matters a lot. Is the source that is having issues a large table? The query you're trying to run matters too, whether it has to iterate over all rows or not. It is very common to have performance issues with direct query if you're not optimizing for it.