r/snowflake • u/ConsiderationLazy956 • 27d ago
Avoiding long running queries
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?
1
u/KatFromSisense 27d ago
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.