cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerRon
Helper IV
Helper IV

DAX Studio and SQL profiler

Hi,

I ran a refresh of a dataset  for the first time. It took 4.5 hours. 

The main query in this is a view in SQLServer, being an aggregate on a table. When I run this view in SQLServer it takes 40 minutes, going from 1.3 billion rows to 9 million.
So refreshing takes long,also the second refresh. 4 hours. Incremental refresh keeps 4 years of history, refreshes the last 13 months (based on creation-date), but only if changed (detect data change on last-change-date).

Becaise refresh takes so long I use SQL Profiler in DAX Studio for the first time. Connecting to the shared dataset in the service goes fine, but once I start SQL Profiler I get an error
Knipsel.JPG


It says trace with id doesn't exist in the server or the user is not authorized for having access to the object.

How to solve this?
@smpa01 

@lbendlin 
Regards
Ron

9 REPLIES 9
PowerRon
Helper IV
Helper IV

I will @lbendlin but I presume refreshing does more than only running the query in SQL ...

If you want you can show a sanitized version of your Power Query code.  Unless you are doing lots of merge operations the M transforms usually take much less time than the actual source query. In any case you can use the Power Query Diagnostics options to figure that out.

smpa01
Super User
Super User

@PowerRon if I were you I would start with SQL query first. Why does it take 40 minutes to run a query ? Are the SQL tables missing any index? How can the SQL query be optimized.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


agree with @smpa01  - you should focus on the Power Query part, not on DAX Studio.  Also be aware that you are dangerously close to hitting the hard limit of 300 minutes - after that any dataset or dataflow refresh on the service will be killed. You want to get your duration down to not more than 2.5 hrs (to be on the safe side). Keep in mind that your refresh is blocking a renderer for the entire time, which means that renderer is not available for other users either.

 

Read about bootstrapping for incremental refresh partitions.

I completely agree @lbendlin and @smpa01 . Just ran the view again in SQLServer. Took 23 minutes. But we are planning to add an index. The main issue for now is that I get that SQL Profiler error. So I can't get insights in why it takes so long.

Furthermore I will dive into bootstrapping. Never heard of it 🙂

Thnx, is on my read list. Is there something else then SQL Profiler to get insight in why refreshing takes so long?

@PowerRon once the index is sorted, and the SQL query is still slow, start dissecting it in parts.

There are lots of known performance optimization techniques within SQL (one syntax is better than other performance-wise for the same output) that can be used.

 

If you still have issues, stack-Sql-server  will rescue out, as it did for me many many many times.

 

One more thing to remember, it might be possible that a native SQL query that runs on SSMS in 3 minutes, PQ might return that query result in more than what SSMS takes. (I don't know why and I struggled with that a lot when I started playing out with large SQL tables). SO in that case, I learnt that the sever-side query needs to be super-optimized in order for PQ to not time out.

 

Also, you might have a choice to bring the whole SQL table `select * from tbl` and use PQ syntax to manipulate the table (to preserve the PQ Query Folding) which will be even more time consuming (from my experience) and I will advise against it (speaking from my bitter experience).

 

Is there something else then SQL Profiler to get insight in why refreshing takes so long? - I am not sure but try Fiddler.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


Use SSMS and examine the actual query execution plan. It will even suggest indexes for you.

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors