Forum Discussion

Scott_Powell's avatar
Scott_Powell
Advocate IV
2 years ago

Lakehouse SQL endpoint performance tuning

Hi - I'm curious to know if there is any way to see an "explain plan" or otherwise determine why a SQL query against a Lakehouse is taking a long time, and what could be done to help it. In my case I...
data engineering
v-cboorla-msft's avatar
v-cboorla-msft
Microsoft Employee
2 years ago

Hi Scott_Powell 

 

Thanks for using Microsoft Fabric Community.

Apologies for the issue you are facing here.

Microsoft Fabric Data Warehouse currently doesn't offer native "explain plan" functionality like SHOWPLAN_XML or EXPLAIN for SQL queries and there are plans to expose the query plan in a "Query Insights" view. However, there are still ways to understand the performance of your query and potentially improve it:

 

1. Data Preview and Query Insights:

  • Data Preview: This feature provides basic statistics about the queried table, including the number of rows, distinct values in each column, and data types. This can help identify potential issues like missing indexes or skewed data distributions.
  • Query Insights: This feature is still in development but is planned to offer insights into query performance, including the estimated time and cost. While not a full explain plan, it can provide valuable hints about the chosen execution strategy and potential bottlenecks.

You can refer to this link for more information.

 

2. Monitoring and Metrics:

  • Resource Utilization: Monitor CPU, memory, and disk utilization during query execution. High utilization in any of these areas could indicate a resource bottleneck impacting performance.
  • Query Duration: Track the actual execution time of the query. Compare it to the estimated time in Query Insights or the expected time for similar queries on your system to identify significant deviations.

Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

Hope this helps. Please let me know if you have any further queries.

  • v-cboorla-msft's avatar
    v-cboorla-msft
    Microsoft Employee
    2 years ago

    Hi Scott_Powell 

     

    We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
    In case if you have any resolution please do share that same with the community as it can be helpful to others.
    Otherwise, will respond back with the more details and we will try to help.


    Thanks

    • Scott_Powell's avatar
      Scott_Powell
      Advocate IV
      2 years ago

      hi v-cboorla-msft in my case I was specifically talking about a Lakehouse not a Warehouse (using the SQL endpoint). I'll try a similar test on a Warehouse to see if that makes a difference. Overall I'm just really trying to judge the performance to see if it's reasonable or not.

       

      Thanks!

      Scott

      • v-cboorla-msft's avatar
        v-cboorla-msft
        Microsoft Employee
        2 years ago

        Hi Scott_Powell 


        Following up to see on the last response and was just checking back to see if you have a resolution yet.
        In case if you have any resolution please do share that same with the community as it can be helpful to others.
        Otherwise, will respond back with the more details and we will try to help.


        Thanks