topic Re: (Big) Problem with SQL Warehouse Auto stop in Warehousing & Analytics

(Big) Problem with SQL Warehouse Auto stop

leo-machado — Thu, 02 Jan 2025 14:18:22 GMT

Long story short, I'm not sure if this is an already known problem, but the Auto Stop feature on SQL Warehouses after minutes of inactivity is not working properly.

We started using SQL Warehouses more aggressively this December when we scaled up one of our operations (this process uses a GoLang connector) and noticed it was getting pretty expensive.

After some investigation, we found this behavior:

Even though queries were finishing running and there was no activity in the cluster, the cluster down did not go down, as if it was still running.

To make sure it was not a problem with the visualization, I've checked warehouse events on the system.compute.warehouse_events table, and it was, in fact, not turning the warehouse off.

In one particular example, this is the log for the warehouse:

STARTING 0 2024-12-25T03:02:46.198+00:00
STOPPING 1 2024-12-25T04:07:09.253+00:00

And these were the only queries executed during that period (start time and end time):

2024-12-25T03:15:44.147+00:00 2024-12-25T03:15:49.266+00:00
2024-12-25T03:56:27.947+00:00 2024-12-25T03:56:28.063+00:00

This warehouse is configured to auto-stop after 1 minute of inactivity, which did NOT happen. In order to test the cost impact of this, I've designed a quick code using Serverless and AWS Lambda to force warehouse stopping when there are not queries running/queued.

These are the results in terms of warehouse costs:

Basically a drop from $40/day to $10/day less.

I'm not sure what is causing the warehouse to stay "on", but I'm guessing it is interpreting JDBC connections as "active sessions" even though they're not running any queries - anyway, this is a serious problem in my opinion.

For those of you that want to copy the approach, the code can be found here: https://github.com/lmachado-sousa/databricks-sql-warehouse-terminator

Re: (Big) Problem with SQL Warehouse Auto stop

Alberto_Umana — Thu, 02 Jan 2025 15:00:01 GMT

Hi @leo-machado,

This problem likely happen because of active sessions on the JDBC connection to the warehouse as you pointed out.

This kind of issues required more investigations, could you please share your warehouseID and your workspaceID on a DIM message I can check our backend logs to understand the behavior.

Re: (Big) Problem with SQL Warehouse Auto stop

leo-machado — Thu, 02 Jan 2025 15:12:33 GMT

@Alberto_Umana, I've sent you the IDs as requested in a direct message

To point out, we did investigate the active connection hypothesis and made sure our code was always closing the connection - our jobs take 10 min at most to finish as well - the problem continued even after making this check.

Re: (Big) Problem with SQL Warehouse Auto stop

Alberto_Umana — Thu, 02 Jan 2025 15:20:14 GMT

Thanks Leo, I received the details, I will review it once I have some time and get back!

So, currently behavior it's not happening because you implemented the custom logic, right?

Re: (Big) Problem with SQL Warehouse Auto stop

leo-machado — Thu, 02 Jan 2025 16:32:12 GMT

Correct, the lambda terminator solved the problem in the short term

Re: (Big) Problem with SQL Warehouse Auto stop

pdiamond — Tue, 21 Jan 2025 17:45:09 GMT

Is this still being investigated by Databricks? I'm seeing similar behavior that's costing us a lot of money.

Re: (Big) Problem with SQL Warehouse Auto stop

leo-machado — Mon, 07 Apr 2025 17:38:54 GMT

Hi! @pdiamond as of today, no one has returned on this topic and I'm still using the lambda terminator to make sure the clusters are terminated when no query is running - the issue is still present.

Re: (Big) Problem with SQL Warehouse Auto stop

Advika — Tue, 08 Apr 2025 08:51:32 GMT

Hello @leo-machado and @pdiamond!

Thanks for sharing the details and your workaround.
I understand the seriousness of the issue and will reach out to the relevant team internally. I’ll follow up on this thread as soon as I have an update. Appreciate your patience in the meantime!

Re: (Big) Problem with SQL Warehouse Auto stop

pdiamond — Tue, 08 Apr 2025 11:39:26 GMT

One thing we noticed is that the "Monitoring" tab of the SQL Warehouse page can be misleading because it doesn't always account for time spent fetching & serving the data to the client, only the actual query execution time. This was confirmed by Support and they also said enhancments to the Monitoring page were being developed to address it.

Re: (Big) Problem with SQL Warehouse Auto stop

leo-machado — Tue, 08 Apr 2025 12:22:16 GMT

Makes sense! The go connector paginates the results and it takes a while longer to fetch everything - we did stop using pagination and started using the INSERT OVERWRITE DIRECTORY statement and got better cost efficient results.

Re: (Big) Problem with SQL Warehouse Auto stop

HNguyen — Tue, 30 Sep 2025 06:50:38 GMT

This is a good catch. Auto termination is something you tend to set and trust it will do the right thing 🙂 .

Wondering if the Databricks team managed to fix this, seeing it has been half a year since the problem was raised.

This is also important for our cost control, so Im interested.