cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gethelp Frequent Visitor
Frequent Visitor

Query Tracing and performance issues

We are have performance issues and need to go into a deeper diagnostic understanding of the queries generated, their formulation, where they are running and how to tune. 

We are connecting to SSAS multdimensional via import mode. 

In the query editor, multiple filters and transformations are being performed. 

 

The report is taking over an hour.

 

how do we rule out that the queries issued are being selective and passing in values to filter data (I.e. Date > 2015 as opposed to pulling all data). I can see the m code generated but how do I assure my Ssas dab that the tool is not selecting the whole cube, and then subsequently dropping columns and filtering data. I expect that it will pass in filters as early as possible to reduce processing. The way the m code is displayed it suggests it starts big and then winnows down. 

Where do we get a list of actual queries and do a performance review. 

fiddler, sql profiler, etc. 

 

any published best practices for performance reviews?

2 REPLIES 2
Highlighted
Super User
Super User

Re: Query Tracing and performance issues

Look at the applied steps on the right hand side of he query editor. Right click on the first step and select Native Query. This is the query folded code being sent to the server. Work your way down the list, right clicking until this menu is greyed out. The first step where it is greyed out is the step that is causing query folding to stop. When query folding stops, power query takes over, and this is less efficient. The general rule is to use the menus (on manual code) for as long as possible to keep query folding going. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Gethelp Frequent Visitor
Frequent Visitor

Re: Query Tracing and performance issues

We're running Analysis Services multidimensional as the data source so unfortunately the 'view native query' menu isn't supported. We ran a Profiler trace at the server level and it captured what I was looking for and confirmed that it folded in multiple steps into 1 query. 

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 414 members 4,283 guests
Please welcome our newest community members: