Databricks SQL Warehouse Querys went to orphan state

[ChsAIkrishna]

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?

[Walter_C]

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

1. 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.

2. 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.