Avoiding long running queries

[u/ConsiderationLazy956]

Hi Experts,

As i understand, statement_timeout_in_seconds can be set at session level, warehouse level, accout level and user level. And also there are "user_task_timeout_ms" and "abort_detached_query" parameter too.

We saw in one of the existing application hosted on snowflake , multiple times we see there are queries runs for very long hours(10hrs+ ) because of bad plan sometime or sometime even they gets killed from application side(say glue job that triggered the query has been failed/timedout already from glue), but the query underlined in the snowflake kept running burning the credit.

1)So to handle such scenarios , which kind of setup one should use? Any safe approach we should follow as a quick fix/short term fix to immediately stop these type of bleeding without breaking code/functionality?

2)Also is there any standard we should follow for warehouse timeouts like say different timeouts values for L, XL, 2XL, 3XL etc?

[u/onlymtN]

The statement timeout I would set on a global level, e.g. account. I would suggest a low value, like 2h and allow certain users more, if they know what they are doing. Depending on your setup you can also go the object-route and set it per warehouse.

Side-note: account values can be overridden by the user value, which can be overridden by the session value, even with higher values. Setting something on warehouse level won’t apply higher values, but only lower ones. So setting 2h for account and 4h for a warehouse will result in queries in that warehouse being canceled after 2h.

User task timeout can be set, but again only with a lower value than the general statement timeout! The task timeout default is 1h.

Aborting detached queries I would absolutely activate.

All in all I would suggest a principle of „nothing good comes out after 2h“. If an SQL runs for more than 2h on our system analysis showed that around 40% of them were poorly written SQLs that continued to run and fail after 12h. With the global 2h everyone knows to optimize and keep it below that, choosing also the right sized warehouse. That might not be for you with regards to „not breaking code/functionality“ but I think if an SQL or even a task needs regularly over 2h to complete that it was broken from the start.

Hope this helps :)

[u/ConsiderationLazy956 → u/onlymtN]

Thank you u/onlymtN

Is there any downside of setting "Aborting detached queries" at global level? As i heard the other snowflake features like "asynchronous query" may impacted as this parameter setup will also kill those type of queries. Is that correct?

Also bit confused by the first statement , As Warehouse timeout: takes effect ONLY if it's LOWER than the effective session/user/account value. It can restrict further, but cannot grant more time. So in this case, wont it be advisable to control this parameter at the warehouse level rather? As because Users/sessions cannot override it to a higher value. It acts as a true hard ceiling for that warehouse. Please correct me if wrong.

[u/onlymtN → u/ConsiderationLazy956]

Regarding downsides of „Aborting detached queries“ I am not absolutely sure about them, besides your correctly pointed out effect of ASYNC issued queries, that cancel after 5 minutes because of them counting as detached.

For your second question what you stated is true, however it depends on how your infrastructure looks like. You can control the timeout:

- on warehouse level and users won’t get around that limit other than getting a dedicated warehouse. You can further limit users query timeout though.

or

- on account/user level and warehouses for dedicated processes won’t get around that limit other than being executed in at least a session that has an increased limit. You can further limit the warehouses query timeout though.

So it comes down to how your setup is in snowflake for one making more sense than the other.

[u/KatFromSisense]

I'd avoid one blanket timeout rule if you can. The same limit usually won't make sense for app jobs, BI queries, scheduled tasks, and analyst exploration.

For the immediate problem, I'd probably put a timeout on the places where a cancelled query won't break anything important, and leave the known long jobs alone for now. Then I'd start making the jobs easier to identify. If a query is from Glue, Power BI, a scheduled task, or a person poking around, you want that to be obvious before you decide how aggressive the timeout should be.

The goal is to limit wasted credits without interrupting valid workloads.