BigQuery Slots: What You Need to Know
A few weeks ago, I posted that Artemis picked up an insight that saved a customer $11k annually in BigQuery costs. A few people asked how we did it, and the answer was optimizing BigQuery Slots.
A few weeks ago, I posted that Artemis picked up an insight that saved a customer $11k annually in BigQuery costs. One insight, implemented in less than an hour, saved $11k—not bad ROI for a Tuesday morning.
We work with a lot of data teams that use BigQuery, and we've noticed that most teams only use 40-60% of the compute they pay for due to misusing BigQuery slots. Sometimes, there is a business use case for overspending, but teams are often unaware of what they are spending.
What is a Slot?
BigQuery slots are virtual CPUs that power your SQL queries by providing compute resources. When you run a query, BigQuery calculates how many slots you need based on its size and complexity.
So what happens? When you run a query in BigQuery, the system first analyzes your query and creates a plan. Then, it assigns slots based on the query's complexity, available resources, and priorities. These slots work together to process your query efficiently.
Contention
There are a few edge cases when you can have slot contention, which occurs when more queries are waiting to be executed than available slots. This typically happens when too many queries run simultaneously, a particularly heavy query needs extra horsepower, or everyone's trying to run reports simultaneously. BigQuery will automatically react to this and change the number of slots based on fair scheduling. What’s fair scheduling?
Fair scheduling
Fair scheduling is the mechanism BigQuery uses to distribute slots among multiple queries and projects. Essentially, it ensures that all queries have equal access to available slots, regardless of the project or user. When a query is run, it is placed in a queue. The scheduler then fairly allocates slots to the queries in the queue. It ensures no queries are hogging resources. While this means queries are treated the same, if the system is overloaded, some queries can get paused or prioritize the larger queries in the queue, impacting performance, a tradeoff teams must wrestle with.
What goes wrong?
So now you have a sense of how they work, how do teams misuse them? In most cases, teams are unaware of over-provisioning slots and utilize more than they need. Slots also go hand in hand with poor query design. If a query has a lot of joins, doesn’t filter the data early in the query, or has a whole host of other minor issues, the query will take more resources and require more slots.
So, to start, teams first need to examine their queries. Whether it be to split your tables into smaller chunks, filter early to reduce unnecessary work, or keep your joins efficient, all of this impacts your query performance, which affects your slot utilization. Once you can fix and improve your queries, you can start to work on better scheduling. Syncing larger jobs at times of the day when teams use BI tools, dedicated resources for specific teams or workloads, or keeping an eye on when and what is using the most resources will help you use less while moving faster.
Final thoughts
While you might think that adding more slots will mean more queries get executed faster, it can cover significant performance issues. Like I said at the beginning, most teams we work with who use BigQuery don’t utilize 1/3rd of the compute they pay for. That is a ton of money!
Getting the most out of BigQuery means understanding how to use your slots wisely while balancing performance and cost.
Don't want to deal with the hassle? Our platform has a simulation model that can accurately predict the most optimal slot allocation for you. Let Artemis handle the heavy lifting - we'll optimize your slots automatically so you can focus on what matters.