Hi everyone,
We’re experiencing resource contention issues where our scheduled queries are consuming all available slots, preventing team members from running ad-hoc queries effectively.
Current situation:
-
We have a 100-slot reservation that all queries (both scheduled and ad-hoc) draw from
-
Scheduled queries frequently consume all 100 slots during execution
-
Ad-hoc queries from our team get delayed or fail due to lack of available slots
-
This is creating bottlenecks in our data analysis and development workflows
What we’re looking to do:
-
Split our 100-slot reservation into separate pools for scheduled queries and ad-hoc queries
-
Allocate appropriate slots to each workload type to prevent resource contention
Questions:
-
What’s the recommended approach for structuring reservations to isolate these workload types? (e.g., 70 slots for scheduled, 30 for ad-hoc?)
-
Is it possible to assign reservations based on service accounts? (e.g., dedicated service account for scheduled queries gets its own reservation)
-
Are there any best practices for determining slot allocation ratios between scheduled and ad-hoc workloads?
-
Any gotchas or common pitfalls we should be aware of when implementing this separation?
Has anyone dealt with similar issues? Would appreciate any guidance or insights from the community.
Thanks!