Query Performance / Missing Index

[Anonymous]

I'm using PowerBI and would like to add some indices based on the dashboards that I've built. If I write sql to display the same data, I can hit a button in Sql Server Mgmt Studio to show the explain plan which will also recommend indices. Is there any way to get to that same kind of information out of PowerBI itself? I have the ability to add indices if I think they will help the dashboard perform, but I would like a quicker way to get to that point instead of trying to recreate each visual's base data in a sql query.

[v-cherch-msft]

Hi @Anonymous 

It seems you may try to add bookmarks and buttons.Below are some articles about it and DirectQuery best practices.If it is not your case,please explain more about your expected output.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-2017-feature-summary/#bookmarking

https://radacad.com/bookmarks-and-buttons-making-power-bi-charts-even-more-interactive

Regards,

[Anonymous]

No, I'm trying to do as the best practice suggests and analyze the source data. I'm using direct query, but some of the visuals take a long time to load. I would like to see what indices I should add on the source data in order to speed up the visuals. In a tool like MSSSMS I can enter a query, hit 'explain plan', and it will suggest an index if it thinks that would speed up the result. I'm wanting to do something similar but with the queries coming off of the power BI visuals.

I'm not seeing that that is available anywhere. Is it possible to see what queries are being sent to the DB from power BI? If I can do that then I could copy and paste the queries into MSSSMS and see what indices it suggests.

Thanks,

Andy

[Anonymous · Accepted solution]

OK I found the FlightRecorder trace file and am able to use that for this purpose. Also just found out there's an easier way thanks to my IT team that DPA is available and it can do the same type of stuff.