Intermittent SQL Failure on Databricks SQL Warehouse

[Ramakrishnan83]

Team,

I did setup a SQL Warehouse Cluster to support request from Mobile devices through REST API. I read through the documentation of concurrent query limit which is 10. But in my scenario I had 5 small clusters and the query monitoring indicated there is not peak load or query being queued. But however the front end initiate 2 queries at the same time to 2 different tables(Select query). No complex joins or transformation in the select statement.  These 2 queries gets executed most the of the time in less than a second. But there are scenarios where only one query completes in average of 10-11 seconds and the second query stays in the "Executing" phase and gets time out after 120 seconds. 

The same query gets completed next time if the request comes in. So it is not about the query or data. I am not able to track out why the query fails as the query monitor is also not having much information. 

I would like to understand: 

1. Does databricks SQL Support concurrent read request ? if yes, what is definition of concurrency?

10 read request hitting at the same second/millisecond or max 10 request is processed by the cluster.

2. Does Databricks SQL has logging to trace back the request from the time it hit the SQL Warehouse to completion?

[Kaniz]

Hi @Ramakrishnan83, 

• Databricks SQL does indeed support concurrent read requests. However, the exact definition of concurrency can vary based on the cluster configuration and workload.
• By default, Databricks limits the number of concurrent queries per cluster assigned to a SQL warehou...¹.
• The maximum number of concurrent queries allowed depends on factors such as the cluster size, available resources, and query complexity.
• In your case, you mentioned that you have 5 small clusters. Each cluster might have its own concurrent query limit. If you’re observing issues with concurrent queries, it’s essential to consider the overall resource availability across all clusters.
• To increase the number of concurrent queries, you can explore adjusting the cluster configuration se....
• Databricks SQL provides query monitoring and logging features, but the level of detail may vary.
• You can monitor queries through the Databricks UI or programmatically using APIs.
• Unfortunately, Databricks SQL does not offer granular tracing from the exact moment a query hits the SQL Warehouse to its completion.
• However, you can review query execution plans, query history, and resource utilization metrics to identify bottlenecks or performance issues.
• Consider enabling query logging and analyzing query execution plans to gain insights into query behavior and resource utilization.