topic Re: Understand cluster activity Serverless SQL in Warehousing & Analytics

Understand cluster activity Serverless SQL

EmmaP — Wed, 11 Sep 2024 08:28:38 GMT

Hello,

Following abnormally high costs when using serverless sql on September 9 and 10, I noticed that the cluster sometimes stays on for an hour even though it's not receiving any new requests, and that the auto-stop is set to 5 minutes of inactivity.

Is there an explanation for this?

Thank you for your help.

Re: Understand cluster activity Serverless SQL

EmmaP — Wed, 11 Sep 2024 08:26:46 GMT

Re: Understand cluster activity Serverless SQL

filipniziol — Wed, 11 Sep 2024 08:30:28 GMT

Hi @EmmaP

Have you checked the monitoring tab?
Any active sessions, queued queries that could block cluster from terminating?
Monitor a SQL warehouse - Azure Databricks | Microsoft Learn

Re: Understand cluster activity Serverless SQL

EmmaP — Wed, 11 Sep 2024 09:37:00 GMT

Yes I checked the monitoring tab. I don't see any active or queued query after 12:05. And none of the previous queries (executed before 12:05) took longer than 10 seconds.

Re: Understand cluster activity Serverless SQL

EmmaP — Mon, 16 Sep 2024 13:01:36 GMT

I have the impression that the cluster's auto-stop (set to turn off the cluster after 5 minutes of inactivity) does not work when the last request was sent from Power BI Services. Has anyone encountered this issue?

Re: Understand cluster activity Serverless SQL

RCo — Mon, 16 Sep 2024 16:30:38 GMT

Hi @EmmaP!

I have encountered this. Even though the UI says that they are complete, they actually are not. While the query itself completed, the client is still fetching the data from the SQL Warehouse.

To check if this is your issue, from the monitoring page (shown in your screenshot) click one of the queries to open the details panel. Then check the "Result fetching by client" metric. While queries are in progress (or show as "Finished") -- If it's null or doesn't show a value, that means the client is still fetching the result.

This seems to really be a problem with Power BI - for some reason it leaves the connection open. I'm not sure if it actually completes fetching the data. Databricks kills these connections after one hour -- which is what I think you're seeing in your screenshot. After the one-hour timeout, the query should swap (I think) from "Finished" to "Failed" and show the error "Query has been timed out due to inactivity."

To combat this, I have a workflow that runs every hour to check if any queries are stuck fetching data -- and if so, kill the queries.