Forum Discussion

smpa01's avatar
smpa01
Community Champion
5 years ago

Which SQL Server Profiler Events to select for Analysis Service?

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 wa...
Need Help
v-henryk-mstf's avatar
v-henryk-mstf
Community Support
5 years ago

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's avatar
    smpa01
    Community Champion
    5 years ago

    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.