Which SQL Server Profiler Events to select for Analysis Service?

[smpa01]

I am currently trying to undertsand what are the components that are responsible for longer refresh time for my report.

High lvel, I know how to do this through SQL Server Profiler.

Low Level, I want to undertand which Analysis Service events should I select for that? Cause I don't want to see the durations for unnecessary events

If some one can please guide me through this would be great.

Following are the Analysis Service Events on the profiler

[lbendlin]

There is no magic wand - what you are doing sounds reasonable, keep doing it.  Look at all potential causes 

- slow data source (maintain indexes and statistics)

- slow connection (reduce the traffic)

- slow measures (check the query plan in DAX, especially the cycle count)

- slow rendering (remove unneeded UX elements, use small multiples etc.)

[v-henryk-mstf]

Hi @smpa01 ,

First of all, you can refer to the official documentation to learn about Analysis Services with SQL Server Profiler as you progress. Secondly, can you further clarify your needs and questions so that I can answer them as soon as possible.

Here is the reference document link:Use SQL Server Profiler to Monitor Analysis Services | Microsoft Docs

Let me know the result immediately, looking forward to your reply.

Best Regards,
Henry

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

[smpa01]

@v-henryk-mstf 

many thanks for taking time out to look into this.

I have a query that takes about 12 minutes to refresh, has 19 data sources (TSQL tables+shaepoint excels+Web data). The report contains 17 tabs with multiple visuals, ~150 measures, ~5 derived DAX tables.

The admin on the premium workspace has a rule that no query refresh should run for more than 5 minutes.

My end goal - to find out which components of the refresh are amplifying the time.

I came across somewhere on the web about SQL server profiler and leant how to connect that to a pbix and run traces.

By running such a trace on the same pbix I identified two data sources queries (slow query) which I optimized to tune the performance.  SQL server profiler has been extremely helpful in that regard.

Now,

A. I want to explore the profiler further to see what else SQL server profiler can help me identify in terms of performance killers as far as measures or other DAX queries are concerned. There are a lot of events to run the trace on and when I ran the trace and queried the XML trace in PBI later, I could not make sense of a lot of events. Hence, I want to run the trace on the most meaningful ones.

Also, I am a regular user of DAX studio and I know very well how to use DAX studio to identify the slower measures and queries and optimize them. I am using the DAX studio to optimize each individual measure (if needed) on that report as I am writing this.

However,

B. I am simply wondering at this point if server profiler can also help me identify those measures that takes longer to evaluate without needing me to go to DAX studio

Out of all the 150 measures, I want to know which are the measures that takes the longest to evaluate. I was hoping that if I run a trace in server profiler, it will give me the evaluation duration of all the measures at once and I can optimize as I wish. Whereas in DAX studio, I can see the measure evaluation span individually by measure (unless there is a trick I don't know). For my task and volume, I don't want to individually evaluate the measures in a table in DAX studio, rather I want (was hoping SQL server profiler can do that) a program which gives me the evaluation duration of all the measures, all DAX queries, all data source query(included in trace) at once for the whole PBIX.

Sorry for this long elaboration.

[lbendlin]

Please refer to this post from Phil Seamark:

https://dax.tips/2021/02/15/visualise-your-power-bi-refresh/