Sign-up now and get instant access
Leave a comment
Customized training to master new skills and grow your business.
Beginner to advanced classes taught by Microsoft MVPs and Authors.
In-depth boot camps take you from a novice to mastery in less than a week.
Season Learning Pass
Get access to our very best training offerings for successful up-skilling.
Stream Pro Plus
Combine On-Demand Learning platform with face-to-face Virtual Mentoring.
Quick references for when you need a little guidance.
Summaries developed in conjunction with our Learn with the Nerds sessions.
Digital goodies - code samples, student files, and other must have files.
Stay up-to-date on all things Power BI, Power Apps, Microsoft 365 and Azure.
Earn money by driving sales through the Pragmatic Works' Training Affiliate Program.
It's time to address your client's training needs.
Learn how to get into IT with free training and mentorship.
Discover the faces behind our success: Meet our dedicated team
How can we help? Connect with Our Team Today!
Find all the information you’re looking for. We’re happy to help.
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.
Join other Azure, Power Platform and SQL Server pros by subscribing to our blog.