Performance Best Practices for Fast Queries, High Concurrency, and Scaling on Databricks SQL

Summary

• Sublinear scaling: Databricks SQL uses an architecture of intelligence to achieve sublinear cost scaling rather than linear or exponential cost growth.
• High concurrency performance: Resource-based scheduling and autoscaling enhancements enable fast queries and high concurrent workload handling at enterprise scale.
• Comprehensive workloads: Modern data warehouses must handle ETL, BI and analytics efficiently, not just one workload type optimized in isolation.

Performance Best Practices for Fast Queries, High Concurrency, and Scaling on Databricks SQL

Talk by Jeremy Lewallen, Product Manager, Databricks ; Mostafa Mokhtar, Principal Software Engineer, Databricks
Data warehousing in enterprise and mission-critical environments needs special consideration for price/performance. this video will explain how Databricks SQL addresses the most challenging requirements for high-concurrency, low-latency performance at scale. We will also cover the latest advancements in resource-based scheduling, autoscaling and caching enhancements that allow for seamless performance and workload management.
Here's more to explore:
The best data warehouse is a lakehouse: https://www.databricks.com/product/databricks-sql
How to migrate legacy data warehouses: https://www.databricks.com/resources/ebook/migrate-your-legacy-data-warehouse-databricks
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

• 0:00 Introduction
• 0:50 The evolution of data scale
• 2:27 Architecture of intelligence
• 4:52 Performance innovations
• 7:33 DBSQL best practices
• 11:16 Troubleshooting with AI

FAQs

What does sublinear scaling mean for data warehouses?

Most data warehouses see costs scale linearly or exponentially as data grows. Databricks SQL uses an architecture of intelligence to achieve sublinear cost scaling, meaning cost increases at a slower rate than data growth. This enables organizations to run larger workloads more economically.

What performance innovations enable high concurrency?

This video covers resource-based scheduling, autoscaling and caching enhancements that allow Databricks SQL to handle high-concurrency workloads efficiently. These innovations ensure fast query performance even when many users query simultaneously.

How has data warehousing evolved?

The video traces data warehousing from the dark ages of 2010 when most questions couldn't be answered, through the growth period of 2020 when costs exploded, to today's modern warehouses that must efficiently handle ETL, BI, and analytics workloads simultaneously.

What troubleshooting tools help optimize SQL performance?

Databricks SQL provides query profiles and AI-assisted troubleshooting to help identify performance bottlenecks. This video demonstrates how to use these tools to diagnose issues and implement best practices for optimal query performance.

Full transcript

0:00 Introduction

All right. Test. Okay. Hey everyone, Uh I'm a product manager on performance and Uh he is uh the TL for performance for all data bricks. And today we're going to be giving a a talk today about DBSQL serverless best practices uh and how to get the best performance. All right. So uh just kind of kicking this off. Uh I want to just level set like how far we've come. Uh we're going to announce some features that we've launched today.
Uh also we're going to go through what those best practices are and then is actually going to give a demo of uh how to use query profiles and do uh troubleshooting for best performance. All right. So I wanted to kind bring everybody back to hey 15 years ago. What was it like? We haven't actually always been able to ask questions of our data in 2010s. This was kind of the dark ages of data. Uh most questions couldn't be answered.

0:50 The evolution of data scale

If you had a a scientist who uh had an insight and they wanted to go do some analysis, they really didn't have the ability to do it then. They could do maybe do basic counts, but even that would have been difficult. Data engineering was very primitive. The tools were lacking. the hardware wasn't as good and then Spark comes along kind of changes this this model you know fast forward 10 more years 2020 and the data is now growing exponentially you can see here this this this is a horizontal line suddenly becomes looking a lot more vertical in about a 20-year period the questions become much harder and then suddenly the cost started to explode and then this new kind of economic wall emerged like we were looking at you just couldn't even ask the questions.
But now the new model is hey you you could ask them but it's really expensive and it's uh it's going to cost a lot of money to do it. And the way that the traditional warehouses solve this is that they would just throw hardware at the problem. This is a a benchmark we we run every year and that the cost curve looks the exact same for this particular warehouse. As the data grows, you can see it on the scale factor on the bottom.
The costs go linear to exponential. But this isn't 2020 and the modern warehouse has to do many things well. It can't just do ETL. It can't just do BI. It's got to do a lot of things well. And so this is how we think about performance inside of DBSQL. Um we have this model. It's called the architecture of intelligence. And it really has one goal and that's sublinear scaling. And it really is enabled it's not enabled to just be fast.

2:27 Architecture of intelligence

It's enabled for that economic breakthrough to where you can ask any question of your data and it's it gets cheaper instead of more expensive. Layer one is this data intelligence. It's where we learn the patterns. We recognize the data automatically for optimal access. Layer two is the things that are kind of the more you hear about them more this photon and the query optimizer. And layer three is this intelligent autoscaling layer where we can predict workloads.
We can add remove compute quickly. So we can s self-correct continuously. And just to kind of go through a little bit of how well this is working for us so far, you know, this is what showed on the keynote earlier today. Uh DBSQL's, you know, faster, less expensive across all scale factors, one tabyte and above. And as it gets bigger, as the data gets bigger, DBSQL is made to get more efficient as time goes. And that expands across all types of workloads.
So you can see here uh this is what we we have something called the performance index internally. It's where the data scientists actually look and look at repeat queries over time to see if they're getting faster or slower. And what we can see is that across all workload types BI 28% faster doing nothing like the uh query just gets faster exploratory 31% faster across all workload types. The concurrency is getting better and better. uh that DBSQL is made to go from go all the way up to 10,000 users uh and still continue to climb uh keep steady the entire time.
And then this is the model for uh how we scale as I mentioned sublinearly before. This is that same benchmark I showed earlier. You can see DBS SQL is continuing to scale horizontally while competitors are are going vertical uh 10x higher TCO at that uh in that benchmark. And now we've even expanded to AI functions and AI functions are also we're continuing to increase the throughput. We've doubled down on that in the past year.
3x more throughput for the SQL AI functions than competitive warehouse. But today there's more. And this is what we announced earlier today. We're rolling out this 25% faster uh version of the warehouse and it's uh new features that are doing I'm going to go into those but it's the same skew it's the same price you do nothing and you're just going to get 25% faster on computebound workloads and we did that through two breakthrough uh improvements that we launched uh today the first one is something called predictive query execution and the second one is photon vectorz shuffle um and I'll go through the details of it, but the high level here is that predictive query execution, it transforms these static query plans into continuously adapting systems.

4:52 Performance innovations

And photon vectorzed shuffle is a new colmer shuffle engine. And so PQE um all right so the way PQE works just one second sorry. Okay. The way PQE works is that uh it continuously monitors the plan in real time. Um, sorry, just one sec. I think I'm missing a slide here. Some reason it's not loading the slide. Uh, but uh the way this works is PQE continuously monitors uh at a at a task level rather than just a stage level.
This was previously we were we used AQE. AQE is a uh way that we can ad can adjust the query plan in real time. And the new way that we do this with PQE is that we can actually look asynchronously at the task level. We can adjust in real time and we use an ML model, this intelligent evaluation section here to replan the task as soon as we start noticing that something is going ary, something's not working the way it should.
And the result of that is we get reduced compute waste and faster and more stable queries. And photon vectorzed shuffle is uh this is one where I had a lot of details on here but the engineers really didn't want me to go to into too much depth here and talk about the algorithms or anything but uh as most of you probably know shuffle is often the largest bottleneck and if you think about it shuffle is CPUs are made to be extremely fast now the latest uh the latest CPUs um are very quick they're very parallel and when the data has to move the CPU stops.
So this very fast engine suddenly has to stop to go get the data. And so the real insight here is that we drive the SIMD lanes. We we skip the RAM. We uh keep the data as much as possible in the CPU. And this leads to us being very parallel, very fast, about 1. 5x faster from our our tests that we've so now getting into the best practices. Uh the best practices for the best performance are really very simple. Now, we've we've made a lot of it default.

7:33 DBSQL best practices

And if you're using DBSQL serverless, a lot of this is just going to work out of the box. You don't have to do anything at all. But this command right here, create table cluster by auto, that is the number one best practice that you can do. If you create the table um and like this, then you're going to be in a managed table. And I'm going to go into all the details of the wise and the watts, but create table will create a managed table.
It's the fastest table type we have at data bricks clust if you use cluster by auto that will take care of all your partitioning all your clustering out of the box. No, you don't have to do anything else. Predictive optimization is also rolling out by default now. So there's nothing to do on that side. All of this is just taken care of for you. So you see managed tables. A lot of people ask okay well why why are managed tables important?
And the real insight here is that there's one metadata log in UC. And because there's one metadata log, every read and write, it hits that same central UC metadata log. And so the queries begin instantly. They don't have to call external systems. They don't have to do any checks. There's no chatter back and forth between them the systems. And so it's about 80 to 90% less calls to external cloud storage when you go with a managed tables.
It's the fastest way. all of our data that we look at internally uh shows managed tables are are the fastest way to have your data. So there's the slide that was missing from earlier. Somehow it got moved. I'll just skip that. Um so the uh auto liquid clustering is the next best practice. Um the data reorganizes itself. Uh that is the real insight. Now we auto liquid clustering by default later in the year. Right now you still have to add that uh extra tag cluster by auto.
And what this does is it queries as your queries go to data bicks. Data bicks looks for the common query patterns and then data bricks organizes your data. It automatically shuffles or it automatically clusters it in the right way. And this is really good. I often hear uh when we talk to customers, customers will say hey look I know the right layout for my table. And for a few tables or 10 tables even 20 30 tables that is the case.
But once you start getting into lots of tables, data bricks, you know, this is the type of thing that an ML model is good for. And data bricks is very good at looking at all your tables and clustering correctly. Next, I mentioned this earlier about predictive optimization. Uh AI maintenance, uh it's just AI maintenance, right? It's it it just works out of the box. It's 20x faster for these uh point lookup queries and it can be up to 50x 50% cheaper.
Um, and the way this works is PO looks at uh every read and write. Uh, it then autoclusters, it compacts, it vacuums. You don't have to do any manual optimize anymore. The there's no scheduled maintenance windows. And this is really one of the big most important pieces to having good performance because once PO runs, it grabs the statistics of your data. When the statistics of your data are are stored correctly, then the optimizers and photon they know what data they can skip.
They know how the join should be done and so the queries get much faster as soon as you enable PO. It takes it speeds up because you have less file or you have file compaction where you can skip uh where sorry where you can trim the scans, you can cut storage and then this only works now on high return on investment uh tables. So it doesn't uh it doesn't just run if you never query the table. PO will only run when there's a high return for you.

11:16 Troubleshooting with AI

Now Mustafa is going to do a demo on perform on uh query profiles. Hello. Hey everybody. Uh. I'm going to walk you guys through a demo. Also luckily we're ahead of time so I'm going to have plenty of time for questions more than I thought. So uh also if any of you guys want to ask me a question while I'm doing this like feel free since we're almost like going to be wrapping up soon. So uh let me hide this. So one one of the key things around basically uh performance.
So how do we how do you guys ensure like you get good performance all the time. So uh someone like myself I'm pretty sure many of you guys I spend a lot of time authoring queries. I write a query trying to answer a business question and sometimes basically this query is slow or basically behaving like not the way that I expected. So in in DBSQL also in DT and as well as jobs we have what we refer to as query profiles.
I'm going to give you guys a tour about what query profile is. So the main idea is basically you run a query. A query is trying to ask a business uh question and in order to basically answer this this business question the query needs to go and read data process rows read partitions etc etc and basically the query profiles hello yeah think of the query profile as basically an MRI for your query it allows you to basically out where every second uh basically goes so here it's showing uh some stats on IO it's showing some stats on files on partitions it's also saying that hey this query has spilled 26 six gigabytes which is quite quite a quite a bit large amount of data.
And then if you see this chart kind of looks like a query plan. You can kind of click on it. It's going to show you uh what we refer to as the DAG. But it's somewhat complex. It has a lot of boxes here and there. If you're trying to find what the bottleneck is, we try to basically simplify this as much as possible. You see this section here, see longest operation for this query and it says like this grouping act took equivalent of 11 hours.
So that we have we have uh threading in our system. So these are not actual work hours. These are actual work hours. Hello. Okay. So uh why is this taking a long time? And that's kind of quite a difficult question. Uh like I'm a performance engineer. I've been doing this for two decades. It's hard to expect every person to kind of know exactly how to read these and how how to interpret them. But if I tried looking a little bit here, it shows well this is like processing 500 billion rows.
Although it was basically reading from both sides around like 1. 6 million and four 4. 8 million here. So what are we doing at data bricks to make this easier and kind of like to make these uh debugging journey a little bit uh much much uh much easier. So we added this cool uh button which which we refer to as optimize and basically all you have to do after your query finishes or or even after your query is running click optimize.
And basically what ends up happening here is that we have an AI agent uh that basically think of it as your own performance engineer that's basically going to be looking at the query profile that I just showed you and it's going to be looking for what the bottlenecks are and basically summarize it in plain English. So basically here it says like you're reading that many files pruning that many files. It's telling me like okay there's a very expensive operator that's uh doing the join and a and it's also uh suggesting that there's a join condition that I'm missing.
So here it will show me uh the following like it's showing that I was missing a join condition causing this this explosion of rows. So let's kind of try uh uh accepting this suggestion and see what happens. So the previous query I was running in 13 minutes. I'm going to basically hit that and now I have my new query with the joint condition I was missing. So in many cases I author queries and sometimes I missed the right joint condition or the right predicate.
Let's run it. So previous one was 30 minutes. Let's run this. Sorry. Okay, that's not going to work. Uh, yeah, that's not going to work. Okay, this one this one looks like it works. But basically, the main idea here is is is basically the AI agent would be able to uh modify the query and basically give me a better query. And then u one of the other things we could also uh basically the with this AI it's also able to site the profile.
So basically we here have citation if you click at the query profile it's going to basically help you explain like bas bas these these uh discussions like basically where where is it coming from. Uh also similarly uh it's able also to basically say uh the there's a table which is line item which is partitioned by lship date and it's missing it's basically missing uh hold on where I go that's not good. it's basically missing this lship date filter.
So the main goal here or the main the main idea here is basically we want to empower every person that's authoring queries with these AI agents in order to basically make their queries faster and more efficient. Uh but and also like including some simple basic English explanation how to basically uh apply these

Learn more about the Databricks Data and AI platform.