topic Re: Databricks SQL Warehouse Querys went to orphan state in Data Engineering

Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 12 Dec 2024 16:54:09 GMT

We're experiencing an issue with our Databricks dbt workflow and workflow job is using the SQL warehouse L size cluster that's been working smoothly for the past couple of weeks. However, today we've noticed that at a specific time, all queries are getting stuck in an 'orphan' state and none are being executed. and the query's is not having any abnormality those are very lite sql's. after restarting the SQL Warehouse jobs process continues.

is any permanent fix for this?

Re: Databricks SQL Warehouse Querys went to orphan state

Walter_C — Thu, 12 Dec 2024 17:44:31 GMT

Here are some steps and considerations to help troubleshoot and resolve this issue:

Check SQL Warehouse Sizing and Scaling:
- Ensure that the SQL warehouse is appropriately sized for your workload. According to the documentation, SQL warehouses use dynamic concurrency to handle varying demands. If the number of concurrent queries or the complexity of the queries has increased, it might be necessary to adjust the cluster size or the number of clusters.
- Refer to the SQL warehouse sizing, scaling, and queuing behavior documentation to understand how to optimize the cluster size and manage concurrent loads effectively.
Monitor Query Performance:
- Use the monitoring tools provided by Databricks to evaluate the performance of your SQL warehouse. Check the peak query count and the number of queries in the queue. If the peak queued queries are commonly above one, consider adding more clusters.
- Review the query history and query profiles to identify any patterns or specific queries that might be causing the issue.

Re: Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 02 Jan 2025 13:24:55 GMT

@Walter_C No luck, after the sql warehouse reboot it is coming back, is any remedy apart from the query's killing ?

Re: Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 02 Jan 2025 13:28:59 GMT

this is happening weekly once or twice and suddenly all the queries went to hang state. and observed all the query's are started on almost same time 1-2 mins different, max limit is 10 concurrent due to this other jobs are not moving.

Re: Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 02 Jan 2025 13:37:31 GMT

verified the sql monitor no peak queued queries

data volumes are same and some time even one row sql also went to hang state

verified the history and query profiles same sql is working after the reboot.

Re: Databricks SQL Warehouse Querys went to orphan state

Walter_C — Thu, 02 Jan 2025 14:12:07 GMT

You have mentioned that this was working properly in the past, does this warehouses have autoscaling enabled?

Re: Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 02 Jan 2025 14:16:51 GMT

"working properly in the past " its a diffrent issue that is connection failure added the profile.yml with your suggests it went well.

this is new issue : query's are going to orphan state (hang not moving) on sql warehouse

Re: Databricks SQL Warehouse Querys went to orphan state

Walter_C — Thu, 02 Jan 2025 15:28:55 GMT

Do you have capability to open support case so we can look at your workspace?

Re: Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 02 Jan 2025 16:14:54 GMT

yes we have a open ticket with MS EXT CHAT 2412190030007696

Re: Databricks SQL Warehouse Querys went to orphan state

Walter_C — Thu, 02 Jan 2025 17:03:57 GMT

Thanks, MSFT might reach out to us and we will provide assistance

Re: Databricks SQL Warehouse Querys went to orphan state

ChsAIkrishna — Thu, 16 Jan 2025 11:41:03 GMT

Databricks team resolved and applied a patch (spark config on sql warehouse).

Re: Databricks SQL Warehouse Querys went to orphan state

Walter_C — Thu, 16 Jan 2025 12:31:15 GMT

Great to hear your issue got resolved