Azure SQL Data Warehouse Gen 2: Adaptive, Concurrency and Scale

You’ve probably heard of the release of Azure SQL Data Warehouse Gen 2. In this post I’d like to talk about 3 exciting benefits that we get with Gen 2: adaptive caching, increase in concurrency and more predictable performance through scaling.

Let’s take a look:

1. Adaptive Caching – One of the biggest pain points whenever we access data from our SQL Data Warehouse (or even on premises DW) is accessing the data from disc. At some point it’s going to hit disc and we know when it does, that it can be a huge bottleneck.

But with Gen 2 we now have adaptive caching, which automatically takes our workload characteristics and our query access pattern and bring them into this adaptive caching. Think of this as a mix of in-memory and disc deploy caches, so when it examines our workloads or query access patterns, it will determine the data we’ve accessed recently, and it will keep it close to the compute power, thus decreasing the time that it takes to return that data to us.

This is huge for any data scientists or the people who are pulling back lots of data from our SQL Data WH. If you’re doing large aggregations and need to run reports against this, adaptive caching will really help there.

Another benefit of Gen 2 adaptive caching is the tier now leverages some of the latest hardware innovations within our Azure data centers; a big benefit to migrating to the new Gen 2 SQL Data Warehouses. Businesses already utilizing Gen 2 are seeing a 5X increase in performance.

2. Increased Concurrency – I’m excited to say we now have 4X the number of concurrent queries than we had previously. The 32 concurrent queries in Gen 1 was fine and there were ways we could make that perform well, but Microsoft heard and listened to consumers. So, with Gen 2 we have 128 concurrent queries within our SQL Data WH.

This allows us to concurrently get live data real-time and we can throw some Power BI reports directly against our SQL Data WH. This is great for the data scientist who may need data real-time to make their predictions or analysis more valid.

Keep in mind that as you increase your service level objective (SLO), you’ll not start with 128 concurrent queries; you’ll need to increase your SLO to reach that. When you do scale up and need those additional queries, this all happens transparently behind the scenes without setting any configuration.

3. Predictable Performance Through Scaling – We now have a better way to do this. Our data stores are consistently growing. Over time these data sets get very large and it’s a challenge to execute and aggregate and report against that data. Gen 1 initially solved this when they separated compute and storage, allowing us to scale as needed.

Gen 2 steps in with predictable performance through scaling. We have new SLOs with an additional 5x the compute capacity. We also can store unlimited data. The question often asked is, how do we compute against this? What Microsoft has done within the Gen 2 environment is they allow us to have SLOs all the way to a DW 30,000C, that’s 5x what it used to be!

I hope you found this post informative and that you’re excited as I am about these advantages of Azure SQL Data Warehouse Gen 2. I recommend you take time and look more into Gen 2 to take advantage of these benefits for your production needs. If you have any other questions about Gen 2, you’re in the right place. Click the link below or contact us—we’re here to help.