topic Lakehouse SQL endpoint performance tuning in Data Engineering

Lakehouse SQL endpoint performance tuning

Scott_Powell — Tue, 12 Dec 2023 18:16:11 GMT

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 tried a sample query that queried a table with about 1 billion rows by it's primary key column, sorting the keys in ascending order. I realize sorting 1 billion values should take "a long time" - but I'm unsure that 11 minutes is expected / acceptable.

Note: yes this is an extreme and unrealistic case, in my case this was a proxy query that should have similar costs to the query the user actually wanted to run (but couldn't due to me not having the needed column)...

Long and short - what tools are available to understand the performance and potentially help it.

Thanks,

Scott

Re: Lakehouse SQL endpoint performance tuning

v-cboorla-msft — Wed, 13 Dec 2023 16:32:22 GMT

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.

Re: Lakehouse SQL endpoint performance tuning

v-cboorla-msft — Mon, 18 Dec 2023 12:21:37 GMT

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

Re: Lakehouse SQL endpoint performance tuning

Scott_Powell — Wed, 20 Dec 2023 17:49:53 GMT

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

Re: Lakehouse SQL endpoint performance tuning

v-cboorla-msft — Fri, 05 Jan 2024 11:40:50 GMT

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