DBSQL Warehouse Advisor: SLA-Based Optimization & Cost Tuning

0:05hey today I have the pleasure to host Cody Davis hi Cody hi how's it going all good all good uh can you please introduce yourself to the community sure um so my name is Cody I've been a uh an sa Databricks for almost four years now um I've been leading our Databricks seel data warehousing smei group um in doing that I've worked with customers

0:28throughout the company helping them build tools and do data warehousing on the lake house with Databricks amazing and for people who doesn't know what means means subject matter expert and cod is part of a group of people and they are behind and know some blog I will I will add the link in the description of the recording but you can see many articles about how you can improve the performance and a lot of

0:51like very technical things and one of the Articles I've SE recently was about the DB SQL warehouse at ADV visor dashboard can you tell us more about this dashboard Codi like what was the reason of building it and then how I can leverage it yeah um so I built this Warehouse advisor dashboard because uh data warehousing is complex and managing

1:14data warehouses and observing data warehouses are hard enough they're even harder on the lake housee so um I built a dashboard that helps customers measure the performance of their entire Warehouse portfolio out of the box so they don't have to guess on what performance is what TC looks like as well as be able to quickly dissect problems and find issues so they don't have to spend a lot of time and energy

1:37um looking for needles in the H stack yeah and what I love the most about this dashboard is that you did keep up with with the all the latest Innovations in in AI dashboards you added the tabs like whenever you release a new feature and so which means this dashboard is not a legacy you keep adding uh features every time and like a lot of every time we keep pushing stuff absolutely yeah

2:01um I always try to stay up to date with aibi dashboards because I think datab bricks has taken that product especially a really long way really really fast um so I definitely consider myself a power user of the dashboard um I can even show uh show some of you guys what some of the my favorite features are like cascading filters drill throughs as well as custom formatting and tables there's tons of stuff I love doing um but yeah

2:24it's really becoming a Powerhouse of a a lightweight VI tool yeah and can you show us like an example of this warehousing advisor dashboard and sure how we can maybe start using it to optimize or get the most of the platform yeah absolutely um I'm happy to walk through what the dashboard looks like um but more importantly I'm happy to walk

2:46through also how to apply it and use it so let me know if you can see my screen yes great so uh this is the warehouse advisor dashboard so deploying this dashboard is really really simple um all you have to do is go to the git repo and blog link that um that we can link here in the uh in in the in the chat somewhere um then once you deploy it

3:08there's really two steps you run a SQL script that SQL script creates a materialized view or table data model that does all this analytics for you measures query performance builds the data model so you don't have to calculate things manually yourself um and if you use the materialized view version it automatically schedules that model to stay up to date without you having to think about it at all so once

3:30you do that takes less than a couple minutes to run um and then all you have to do is import the Json dashboard template and then you're off to the races so there's no manual coding you don't have to dig through the queries if you don't want to understand like all the nuances of the dashboard you definitely don't have to you should be able to get value out of this and start using it within five minutes of deploying it so once you do that you'll

3:53be taken to a readme screen which just describes the actual Warehouse uh advisor dashboard itself zoom in a little bit here um and then once you're here the dashboard describes four different tabs tab number one is the warehouse usage summary all this is doing is taking a subset of the broader cost dashboard and zooming into

4:15specifically DB SQL warehouses so it allows you to Trend overall costs of your DB SQL warehouses over time um break out those costs by the type of Warehouse that it is the workspace um any tag combinations you have on your Warehouse um as well as being able to choose um specific warehouses and see edits to those warehouses over time so

4:37if you've had a warehouse that's running in production for a long time and someone makes a config change this allows you to highlight and very very quickly discover and identify any of those changes what was made what changed who made the change so very critical to be able to do and then these detailed Warehouse visuals just show the proportion of total cost of your Warehouse spend that you have selected

4:59over time so here we can see you know um let's see our shared endpoint represents about 12% of our total Warehouse cost in this given day and that's fluctuated by quite a bit over over time here so that's really it this is a very very simple section just to give you a quick One-Stop shop temperature check of all of your warehouses from a cost perspective any questions here so far um

5:23I really want to jump into the actual performance section because this is usually where customers have the most questions because there's a lot metrics that go with managing Warehouse performance and I think just something to remind folks need to make sure that you have the latest tables in system tables because all the data that this dashboard is G is are from those tables so you need to make sure to have them all enabled to benefit from this

5:46dashboard so this dashboard um uses the billing query history Warehouse events as well as the warehouses system tables so you need at least those to run this dashboard so it's a good call out all right so the next tab here really shows how the warehouse scales so as you size a warehouse especially if you have high concurrency workloads this shows you um how much the

6:10warehouse is spent doing any work like are people running lots of queries in the warehouse is sitting idle a lot of the time um but more importantly it how how it scales for concurrency over time so how much time it spends uh scaled up to 0 1 2 3 4 in number of warehouses this helps you get a Bottoms Up understanding of what the concurrency

6:33workload shape looks like uh it is a very very common mistake to overestimate the concurrency so I've seen this happen you know customers over and over again say Well it scales between zero and four warehouses so let's say on average we're paying for two two concurrent Warehouse um scaling you know over time that is that is a vast overestimation because of

6:55this like shape it's actually spending most of its time not even on at all um so that this really helps you show exactly that um but even more show you what that looks like over time so at a given daily or hourly grain you can see the actual Peak uh time periods in which the warehous is scaling a little bit more than usual so if this particular

7:17day and you can even sort by hour as well um is particularly high stress or high intensity for the warehouse you can see what that looks like and adjust your query workloads or Warehouse configurations accordingly so um the main section here is this performance advisor section so this is the uh this is the time this is the tab that I spend

7:40like most uh with most customers on figuring out how to you know understand all these metrics um to actually get to recommendations for how to make your Warehouse more cost effective and more performant so we'll go through some of these um but it really just boils down to a couple things in uh DB SQL warehouses you you only have so many knobs and you really don't want to have

8:03to we Databricks don't want you to have to tune a bunch of knobs so we want this to be as simple as possible for you that being said if you're want if you're running serious workloads and production on these warehouses you still want some semblance of control and visibility and observability into what performance looks like knobs or not so here are the metrics that are most commonly used to

8:25help decide is my warehouse the right size is it hitting the required performance that I even want and is it doing that in the most cost- effective way possible those are the primary questions that we have to answer um and I like to start um always with the SLA what is the uh query runtime and throughput that you need um to justify

8:47and successfully run your workloads and then everything else in this dashboard is based on minimizing costs for that given SLA so that's the optimization problem um that we're solving here in this dashboard any questions or thoughts so far then we can kind of jump into the uh the metrics themselves and show you how I kind of relate them um to find recommendations it's very clear so far

9:09awesome so um here uh we can see the query runtime so this is our SLA this is expressed in P95 P99 even you know p100 which is technically the max query runtime these three metrics here get us an idea for the shape of our runtime distribution so let's assume that uh 16 seconds our P95 um is too long 16

9:34seconds is too long we need it to be under 10 seconds um what do I do uh how do I make it go faster What specifically do I need to change and how do I change things without you know wasting a bunch of money so that's what we'll figure out here always start with the SLA minimize cost and Warehouse size based on that SLA that's the problem we're solving from here uh you have two knobs you can

9:58control the T-shirt size of the warehouse and you can control the concurrency and the concurrency simply means um what are the number of parallel clusters that are running in the warehouse to increase query throughput um that's all that means so um first is Warehouse size over on the right hand side here you can see uh metrics for data intensity and data throughput as

10:22well as uh data spill rates so data throughput data intensity is just a you know a fancy term for how much data are my queries reading and writing on average and what are the sort of limits of those so what's my P99 of my data intensity so this shows the average query reads about10 gigabytes which is very low 100 megabytes no problem at all

10:44you don't even need you know probably a two extra small Warehouse to do something like that um our most data intensive queries um are P99 in data intensity is about 1 gig so these are very tiny queries these are not data intensive we're not reading and writing a bunch of data we almost never spill data to disk which is what this metric means here and what that means is when

11:07you spill a dis that means the cluster doesn't have enough memory to process the query um for a multitude of reasons but in general not always It generally means making the warehouse bigger can help that so when this spill frequency is really low it means the warehouse is properly sized or even too big oversized you don't need that big of a warehouse so let's say uh our warehouse is a

11:31medium so it doesn't show it here because we can actually select many warehouses at once to measure performance but if we pick on one warehouse at a time um we can go back to the usage summary and scaling patterns for that warehouse and see okay this is a medium scaled one to four clusters during the time period we're analyzing how can we make the decision to adjust accordingly from there so we have data

11:53intensity we see that it's really really low um which means you know directionally we need a a very small Warehouse to accomplish this so Warehouse uh sizing is a nuanced exercise but there are very very basic sort of directional templates you can start with in general we recommend starting with the medium medium is great for processing tens of gigabytes to

12:16hundreds of gigabytes um in a in a really strong you know in a really strong manner if you have more than hundreds of gigabytes you're reading in a given query you know slowly stepping up into the large uh space and then if you have terabytes or pedabytes extra large to extra large plus but um one thing customers do pretty often is they start too big um a large Warehouse

12:38compared to a medium is not going to get you much more throughput and if your queries are small it's actually not going to get you better performance because you're not even taking up the size of the warehouse so it's important to make the warehouse smaller when queries are small just as much as it's important to make the warehouse bigger when queries need more power to Pro process but it all goes back to SLA so

13:01if the warehouse is spilling data everywhere writing everything to dis it's too small but you're hitting your slas there's no point in increasing it usually so that's the first half of the coin we've discovered maybe a small even a two extra small Warehouse would be plenty for these types of workloads that are not reading that much data um our query throughput our queries per second

13:23queries per minute is not super high on average so um it's not a very data intensive Warehouse uh however there do seem to be spikes in concurrency and so this is the more nuanced interesting problem um sorry was there was there a question I want to make sure I no no cool um so this is the more nuanced

13:46interesting part of the problem where um concurrency usually is not static it's very spiky and that's what serverless is all about optimizing for elastically scaling for those types of spiky workloads so um we have these metrics here um to measure what that sort of concurrency looks like in the distribution of concurrency intensity so all that means

14:10is we have our P95 of Pak cued as well as our p50 um same thing for Peak Running so these are this is the P95 of our most intensive parallel running queries so our most intensive parallel running queries in our more intense States we're running 10 queries at a time um in our most intent States we are uh queuing 14 queries at a time you know

14:33directionally 10 + 14 25ish queries that we have to deal with concurrently at its peak so these are all you know sort of directional in nature it's not an exact science but it does help give you um some decisions directly it's not enough to be able to say okay a warehouse can process 10 or 20 or 30 queries at once so if we're under 20 then don't scale up

14:56if we're over 20 then scale up each query has different CPU and processing needs so when when users and customers ask me like how many concurrent warehouses should I use should I just you know finger in the wind like one Warehouse per 10 concurrent queries the answer is no don't really do that you can do that as a starting point um but the number of parallel queries actually

15:18is increased quite a bit it can be like well over 20 sometimes now um or even higher than that per cluster um it can also be less if if the queries are really really intensive so uh the datab brick SQL AI workload management algorithm figures out how to distribute the work accordingly relative to the quiry intensity for each so all that to

15:40say go back to the SLA if your concurrency is let's say scale zero to two concurrent warehouses and the concurrency isn't that high but you're not hitting your SLA it's possible you have some bottlenecks or one or two expensive queries that we can find easily in this dashboard and that increasing concurrency might help how do we know it might help uh well it doesn't

16:02just matter how many queries are cued but it also matters how much time is wasted of the query's life sitting in a queue so that's this percent time in queue this is sort of the most awkward one to understand but um in my opinion from my experience the most helpful um to help me make decisions so this shows the p50 and P95 of the percentage of

16:24time wasted in the queue of a query's lifespan so for for example if a query spends 10 seconds in the queue but takes 10 minutes to run that's a very low proportion of its life it's spending waiting around doing nothing that's not bad but if a query takes 10 seconds sitting in the queue and runs in 1 second it wasted over 90% of its life doing nothing sitting and waiting around

16:47and that um adds you know pressure backlog pressure this 89 means that 5% of your queries um actually spend a good majority of their life in the Q so there's probably some spiky workloads that come on suddenly during the day and we need a bit more concurrency for those specific time periods but the workload is pretty elastic because on average um

17:10it's actually not that high concurrency our throughputs not very high so it doesn't stay scaled up very high so scaling back down to zero should be very very common here these are kind of how we use those metrics together in tandem so overall if I'm running a medium Warehouse scaled one to two clusters and I need to get below my SLA of 10 seconds

17:32I might actually choose a small Warehouse scaled 1 to three clusters and see if that gets me increased query throughput and uh decreased queuing time uh in my future subsequent workloads the cool thing about this dashboard is you can run those load tests and compare the results before and after and just take snapshots of these dashboards to show so this is actually how we run like a lot

17:54of our evaluations for sizing workloads for figuring out what the best configur ation is so that's a highle description of the metrics not all the dashboard is built around those metrics so the rest of the dashboard just gets to show you more intelligence around what's going on in the warehouse so this shows where your quer is coming from um you can dynamically choose the dimension that

18:16you want to see so if you want to look at you know your queries by statement type your query volumes by Warehouse workspace even your query failure rates by execution status you can do that um you can also Zoom in or out of your time grain sorry is there a question no no so just aggregation yet super super interesting to understand how how it evolved within the day hour month or

18:38year yep and this is one of my uh you know favorite features of the aii dashboards is because they're SQL native there's not this proprietary custom like Dax like language that you have to learn um you can use uh and make these Dynamic visuals um just in SQL so with SQL plus these dashboard parameters um you can make dashboards really really um

19:00flexible and dynamic that way so I I put these all over my dashboards because it makes the number of visuals a user has to digest a little bit less as you see that's important in this dashboard because there's a lot of visuals um makes it a little simpler to the eye so we can see our query volumes we can Trend them over time sort of see when the spiky days or hours or months may be

19:23but we can also Trend our runtime slas over time and break it out by Warehouse so we can see that that uh this Warehouse uh is a bit more uh data intensive and has higher um higher p99s and P95 in this you know shared Warehouse different workloads are running on it and this type of view can be trended over time as well this is important to see for multiple warehouses

19:46because it is a very common uh use case for uh users and customers to have a fleet of warehouses um that drive a specific workload in production this can be a data app this can be anything that are powered by many warehouses behind the scenes and this lets you gauge the performance of all of them in one place so that's really really helpful to sort of um understand where your sort of

20:08strong and weak spots are in your Fleet of warehouses here so going to the details um we did discuss like sort of peak cued and Peak Running this describes those distributions um from the summary charts just in a more visual way so you can sort of see the relationship between the median uh Peak cued counts and the median Peak Running counts as well as the you know p00 the

20:32maximum cued so at its Max we queed 82 queries so as we suspected there's a huge Spike all of the sudden that happens that we need to scale up elas elastically four to decrease our runtime but the average quer throughput is not high so these don't stay around for long that's good to see same thing for this percent Q time so we can see that the

20:55median uh Q time is zero um queries the majority of the queries don't waste any time in the queue maybe a very little bit but the longtail 5% of our queries waste 90 plus per of their life in the queue that's the part we want to solve for and that's probably because of these big giant spikes so if we don't have enough concurrency for 82 queries it's going to take you know a couple seconds

21:18for those other warehouses to spin up and you know run those and burn down through the queue so increasing concurrency can help burn that down faster um yeah by increasing concurrency you need to add more clusters and not to increase the size of the SQL Warehouse yes yeah so increasing the T-shirt size will not really help you on increasing your query uh your CU burndown rate so

21:42that's a very common mistake I see is a customer will just create an extra- large Warehouse because they're like I need a lot I got to do a lot of work um that's not how it works so um if you have high high data intensity really complex queries in your Warehouse is spilling increase the size of the warehouse um if you have lots of queries to burn through increase concurrency do not increase the size it does not help

22:05you in most cases very very good all right so what happens um if the warehouse and this this shows sort of the disc spilling and data intensity similar to the summary metrics we showed above uh basically if it's red it's spilling to dis um which means it's not enough memory if it's green it's not so very very simply if you see mostly green

22:27here your Warehouse is good good it's it's either too big or um perfectly sized for your workload and you can then see your data intensity so your P99 of data reads is you know one gigabyte but there is maybe one or two queries that read 100 gigs so that matters um so that's that's the sort of trade-off you want to see and the distributions you

22:49want to highlight um and you can even break that out by Warehouse so we can see this Warehouse had that 100 GB query um so maybe we should split out the workload loads um to make sure that maybe we have a higher concurrency configuration here and maybe we make this one a little bit bigger t-shirt sizewise um but we don't increase the concurrency so you know making the decision between splitting and combining

23:13workloads on a warehouse um is also really important as well and that's going to matter based on concurrency needs and data intensity needs uh the best way I can describe uh deciding when to split versus when to combine workloads is just the uh the heterogene heterogeneity of the workloads so if they look and feel very

23:35different if you have a bi workload that's really high concurrency lots of small queries running on the same warehouse is an ETL workload that's not concurrent you know it has no concurrent queries but has really really beefy pipelines you probably want to split the warehouse because you don't want the T-shirt size to be you know sized for your most intense ETL pipeline but the

23:57currency to be sized for your most intense concurrency scaling your lowest common denominator is high in both scenarios so that's a good example of um when to split versus when to combine and that's really it so those are the metrics we have to size and then um know then then the remaining question comes if my warehouse is size pretty well um

24:20and things are still not going perfectly or maybe they're going well for a while but my warehouse is getting worse over time something's going wrong this quer performance detail section helps you actually find bad queries um and figure them out and root cause analysis them very very quickly so there's only a fixed number of reasons why queries are bad um they take too long to run they

24:43spill a dis they read and write too much data they queued too much um or they errored out and that's about it so we codified others into Flags um into basically just red green bars so all you have to do here is click on the uh click on the red bars click on the long running queries click on the data intensive queries and then you get your

25:05problem queries and these are your individual queries themselves um these show you the specific you know query ID the link to specific dashboards or SQL queries that drove it as well as the actual people and uh statements that ran on what happened um in all the metrics associated with that so in a couple clicks we're able to then further refine

25:27the weakest parts of our query profiles and our query run times here so we can do top down sizing of our warehouses so we don't have to do this needle in the Hast stack exercise from the beginning that's what a lot of customers do they will try to optimize a warehouse by tuning one query at a time and they're just forever in that Loop of doing that but here we size our

25:50warehouses from a top down perspective with you know Warehouse level metrics and then we can dig into the needles that we have remaining um to solve so um yeah that's that's really the dashboard um I hope this helps sort of structure um how you walk through this to get value out of it um and yeah any other questions or thoughts on on the dashboard no I I love it it

26:13just just perfect and that's for the folks who uh are watching us if you want to add an additional metrics you can just go and edit the uh the dashboard and add your custom metrics Cod it try to think about everything like generic but you can add your own if you want but I think like bonus is super useful and you thought about almost everything I don't see anything else we can add or

26:36maybe wait if we have additional system tables that can get give us more information that that can be leveraged too yeah and um I'll try to be the first one to incorporate the new features as they come out so that um this dashboard is as up to date as possible um and then yeah one one last thing that is a really common ask is like query tagging so if users have a lot of workload loads um on

27:00on the warehouse let's say their DBT jobs or their actual application jobs that run um uh you can actually custom create a signature um like like a templated custom comment and put that in the actual data model of this uh and then it'll pull that up and parse that so uh for example by default this

27:24dashboard understands all the DBT metadata so if you're running DBT jobs on your Warehouse you can actually find uh there's no DBT workloads on this warehouse but it automatically parses the DBT metadata like Target profile as well as the individual node ID models themselves so you can gauge performance of specific subsets of DBT jobs um and

27:47then you know if you want to customize the actual tagging of the metadata you can do that yourself and create your own um templated comment to categorize queries into very very common ask so that that framework is embedded into the dashboard in the data model itself uh perfect U thank you C thank you so much and we'll talk soon about another

28:09dashboard if I'm not wrong all right that sounds good