Azure SQL Database Hyperscale: Elevating Performance with Continuous Priming | Data Exposed

Key Takeaways

learn about three new key enhancements to Azure SQL database hypers skill in the areas of performance size and something called continuous priming learn all about it this week on data [Music] exposed hi I'm Anna Hoffman and welcome to this episode of data Expos today I'm joined by ad Didia who is a product manager on the aure SQL database team uh I'm pretty sure largely focused on hypers scale ad Didia welcome back to data expose hey now how about you I'm good it's great to have you back on the show I think we had you on to talk about hyperscale several months ago but it's great to have you back and um you know as we know hyper sales been generally available for several years now uh can you tell us a little bit about you know some of the Investments that Microsoft is making in azrial hyperscale sure as you know like we are really betting big on hypers scale as a as a technology particularly for relational databases um we are betting big on it um uh as a as a company we betting big on this technology because we believe this database will fit in all types of workloads all types of St all types of sizes everything so that's why we are really bitting big on it and the features that you you are going to see we which we kind of announced in pass Sumit are actually a testimony to say that we are really investing a lot on this technology specifically isql hypers hypers skill datab awesome cool that's great to hear and I know I've been seeing it firsthand but I just want to make sure everybody else is new too um and yeah exactly today we're going to talk about some of the latest things that have landed uh by the time you're watching this video maybe a couple weeks ago maybe a month or two ago uh but they're there for you to go check out uh I think there are three big things we want to touch on but um Didia why don't you walk us through what's new sure so let me share my screen uh yeah hopefully you can see my screen uh and yeah so we we in hyperscale database the biggest thing that we are doing is we are kind of listening to the feedback from the customers and we are saying what is the right size that we would have and there are multiple Enterprise customers and came to us and said hey we are doing good but we would want to have more size and to really make sure that we are equalent to all our compet databases we made hyperscale database right now to 128 terabytes previously we are at 100 terab now it is going to it it's going to be 128 terab it's right now generally available for all the hyperscale single databases so you can you can essentially grow grow your database to 128 terabytes for elastic pool you are going to get in uh uh later but for all the single database it's all at General level and and there is one other feature that which is which is of a popular demand that is the increase in log generation rate to really get this maximum log generation rate we increase from 100 Mbps to 150 MPS which means you can load a lot of data so faster into these databases 1 15 MVPs by the way is one of the highest uh injection rate that you are seeing in the market compared to other competes also so this is another another thing that we kind of did now this one is in the limited preview as we are speaking which means any customer who already have the hyperscale database if they want to get enrolled into it please go into ak. ms/ HS enhancements and uh please submit the form so that we can enable 150 MPS for you folks cool I mean that's a huge one I mean that's 50% higher and um like this this was already pretty high compared to some of the other uh database offering so 150 that's that's awesome to see that yeah yeah and and we have worked with number of Enterprise customers with 150 MPS in the private preview Anna and I'm really happy to tell you that it is really satisfying majority of the workloads uh which with 150 MPS we were able to ingest a lot of mission critical data into into the uh database this is super fast with this uh 150 MPS and we are not stopping here we are working to dial this uh dial this more and I'm sure there are there are customers who would want more than this we are not stopping ourselves here we are trying to make our technology to get better but right now we are at 150 MVPs great great okay so that's either one update or that's two it seems like two pretty big updates to me what what else is there yeah so this this one so let let's see how how you can monitor this in the database Watcher so if you go into database Watcher you can see right now the maximum data storage is 128 terabytes and the cool part is you need not provision anything when you create a hyperscale database you getting a 128 terabytes worth of database all you need to do is go on add your data and we will expand your storage automatically so you need not worry about hey do I need to provision anything or such no it is automatically available and it is growing as your workload grows till 128 terabytes and the same is with the log generation rate to which means whenever whenever you have a when whenever you have a database which is asking you for a lot lot of lot of log generation rate you can essentially go and injest data with 150 M here in database Watcher essentially you can see that as a proof where you can essentially ingest 150 MPS of data nice yeah and there is one other feature that I'm really really interested and proud of which is called continuous priming this is the uh General hyperscale architecture radana like everyone knows about it you have a read write copy you have read only copies all of this data is stored in something called PID servers which is the cach layer and there is a permanent storage called data storage layer this is what is the general hyperscale architecture is but there is a one single problem that all our customers used to tell us that is you have a primary replica and you have a h replica right whenever you fail over from primary replica to H replica you for you to get the same kind of performance as primary replica you need to wait for some time such that all the data is coming back to H replicas buffer pool and SSD cache this is not really a uh valid way for Mission critical workloads where immediately after failover nobody is nobody wants to go through those performance about next and hence we have introduced this feature called continuous priming in continuous priming what we essentially do is we kind of look at all the active Pages list which means let's say you have a table with 10 records and you are frequently carrying gr We call we nominate that as the hot Pages we push that active pages to uh paid servers get the we have we have a record of what do the hotness levels and we can of sort it bya hotness which means how much how much each page is touched and we kind of get all that hot Pages back onto all the H replicas which means when you fail over your H replica will be almost ready as the primary replica and will not have the any kind of performance portal next got it okay so if I understand this correctly help me uh like basically what you're doing is you're making it so that when I do a failover it's not like I'm starting with a cold cash it's like I have this kind of hot cash based on my actual usage ready to go yep you got it right you got it right so we we kind of did a test uh for that so we wrot we have pushed 10,000 transactions per second uh into the database and we did a fail over at this uh 232 p.m. and when we did that fail over at 232 p.m. for this s for the ha primary to catch that workload we it took almost four minutes to catch that workload and start doing again 10,000 transactions per second all through this while the amount of Weights that you see are page a l such that is IO in it which means it's going and fetching the record and putting back onto the buffer pole and trying to do it so it is that there is a performance bottleneck for 4 minutes altoe but after we turned on continuous priming this became only one minute less than a minute and pay latch dropped to 1% of what you were seeing previously which means your workload immediately after fill over within no time is ready to serve for the prime time which means no performance B next whatsoever so all this is is only possible because of the architecture with which we have we have all the paid servers and the disaggregated comput and storage layer is essentially making this possible to to bring such technologist for you folks amazing and one question I have for you uh probably we could do a whole episode just so we understand what's happening here but one question I have for you is is there any impact on the primary uh when I have continuous priming turned on no no no no no no impact at all because it is we are doing everything on the back end all the data that is stored is in the back end of the page servers and everything and the right replica or the the primary replica that you have is essentially is doing the work that it is always doing it is writing into buffer pool and writing into arbex which is always doing we change the algorithms to understand what is the hotness and bringing back to the H replicas immediately after we understand the hotness I see and is it the log service that's bringing the data back to the no no it's in the page service the data pages each each page server is 128 GB right let's say page server one has data of students page server 2 has the data of teachers the hot and you are touching 10 pages of students and 10 pages of teachers those were stored in those respective page servers so which means log service it's not touched at all servers and the page server is sending the data back back to the H replica amazing cool okay cool awesome okay this is super exciting uh and huge Improvement uh and from a customer perspective once we I'm assuming once we light this up for everybody right now it's unlimited preview you'll just run faster right yep yep that's that's the that's the hope and because of this many many customers asked for this and uh no no customer even when there is a planned maintenance window or fixed maintenance window whenever their databases goes from one one place to another they will not be seeing any performance impact because of those fail hours amazing awesome well ad Didia thanks so much uh for coming on the show personally I learned a lot uh so appreciate your patience with my questions I'm sure our viewers also learned a lot uh to our viewers check out the aka.ms slhs enhancements link we'll put it in the description for you to learn more leave us a comment and let us know which enhancement you're most excited about uh and we hope to see you next time on data exposed [Music]