Performance Demos of SQL’s Intelligent Query Processing Feedback capabilities | Data Exposed

Key Takeaways

maybe you've been hearing about the intelligent query processing capabilities related to feedback in SQL Server 2022 and Azure SQL but have you seen all of them in action in this episode we will dive into a ton of demos so you can see all the benefits of memory Grant feedback degree of parallelism feedback and cardinality estimation feedback this week on data exposed [Music] hi I'm Anna Hoffman and welcome to this episode of data exposed today I'm joined once again by Kate Smith us pm on the SQL team Kate thanks so much for coming back on the show absolutely I love being here Anna thank you we'd love to have you and today's a super exciting episode because we are doing demos of performance for our iqp feedback related capabilities so we're just gonna kind of go for it um and I think the first one we're going to talk about is related to memory grants so I'll pass it to you let's take a look all right let's have a look at memory Grant feedback thank you okay um so here's my demo I've got SQL Server 2022 or the latest version with compat 160 installed um and basically I'm going to demonstrate the um yeah in the databases worldwide importers I'm going to demonstrate sort of an oscillation situation this is where percentile Grant feedback really buys you a ton because what we do is um we uh Val we when a stored procedure has a lot of variation in the values given to it as parameters you you can create a problem situation for memory Grant feedback so I've created a short procedure and let's look at the actual counts for each for example we've got 200 000 values whereas bag we only have like a thousand right so if we execute this stored procedure first for bag right it's going to actually give us a warning that we gave it an excessive Grant that's because that this value for bag is below the average for the overall in the table but then when we execute with each and we look at the execution plan what we're going to see oh see it took so long um this sort it had to Spill the disk right we see here it spilled disk to disk during execution that's really painful but if we go back and we execute with bag again we no longer get that warning that we gave it excessive Grant because we've already learned and adapted right and so if we alternate back and forth a few times so here we did the sort it still spilled the disc um but um but it's it's going to start spilling less and less as we adjust and adapt so let's see how we did this time uh yep still spilling let's execute with bag this always executes quickly um no warnings everything's happy uh let's go back it takes a few times right because remember we're learning we're adapting so it can't just happen like automatically but now look we've gotten to this point where we no longer spill the disc how cool is that like we've learned we've adjusted over the period of execution well before wouldn't happen that way and now like let's look at queer storehands so query store hints is one way in which feedback features can provide um insights into what what it's doing but memory Grant feedback is not one such feature so you won't see queer store hints showing up for memory Grant feedback but the planned feedback table will show hey look we generated mammogram feedback we needed additional memory and we validated it that's really cool right so now we're just on the Fly learned how much memory your query needs adjusted so that um not just based on the prior execution but on a history of execution and we're doing the right thing wow that's pretty cool so just to make sure I understand what happened here is like it's it's taking like it's telling like okay we ran like this and it you know we spilled the disc it ran like this and we used too much memory and so is it at a new value that's not eat your bag like what would it do it will adjust so like or even if your data changes or your execution patterns change over time it will adjust so it'll sort of like record history for a while and like adjust on that but temporality is uh taken into account so that like let's say you require this amount of memory and you execute for a while and things are good and then a new value gets added that's like not each but it's like by peace or something right I don't know what would be more individual and and that has a lot more values we would over time we would learn and we would adjust up or similarly we could adjust down if the way that you execute the query the usage patterns change nice that that is super cool okay we're moving right along because I know there's a bunch here like what what's the next one the um cardinality estimation feedback I'm going to only show you one of the three model variants that we support with cardinality estimation feedback um but I hope that it'll be really insightful and you'll learn a lot from this just this one all right let's go to it like I said CE feedback demo we're going to talk about correlation so we've got three different models of correlation when you include CE feedback in um in our product right we've got the default which is sort of partial correlation or exponential back off we've got um I'm just going to talk over all this setup work that I'm doing to set up the query store make sure I've got the database installed Etc um we've also got it full and fully independent so if you've got conjunctive predicates Like A and B um uh then uh we can we can actually uh look at when they're correlated or when they're not so I've also I've set up an X event session here and we're going to watch that data live on that X event session while we execute our query so we can see what C feedback is doing in real time so here again I said we've got two predicates um where State providence province ID is 79 and City equals REM remember this conjunctive predicates in a where clause are these correlated are they independent or are they partially correlated right so we're going to run the query a number of times and CE feedback only works for repeated queries so it's not going to take place for every ad hoc query that you do it's a lot of analysis to do for every single little ad hoc query to make this work correctly so we execute it and then um we're just gonna uh flush the plan and all that information to the query store so that we can look at the query plan that was executed so we bring it up here by doing a query against query store we get the show plan and we can just look at it here and you see we just did a scan of a table with this where cause involved but as you can see um is that we estimated uh 44 rows but we actually got 121. what that means is that the data is more correlated than we originally expected because we assume some level of correlation and completely correlated data is going to be more rows because we'll just take the one predicate that was the most selective and use that instead of incorporating data from or information from other predicates and here we do get a hint and says assume in selectivity that um that that hint means that we take that minimum selectivity predicate and we use that for the whole thing so like let's say Redmond was more selective than the state right we would just use the selectivity generated for that one um that one predicate and so then before we persist to this we validate it does it make a difference so first let's look at how we did in practice all right we're bang on right we went from thinking we were going to get like 44 rows to actually the actual number that we got which is 121. but if we look at the validation and this is really interesting and this is why this is such a fun demo for me to show is that the plan hash didn't change so we didn't change the plan and because of that we actually um default the original CPU time to zero so that the feedback will not be persisted so the original time wasn't zero in practice but we assumed that it was as a way of preventing this feedback from being persisted we only want to persist feedback that is of high value and if we're not changing the plan maybe we're not doing enough for your query does that make sense yeah super interesting clever we'll we'll we can run it one more time and we can see here we're back to the old way right we reverted that feedback so we really operate on a Do no harm right a Do no harm principle um and so we reverted that feedback and um we're not going to provide it so normally CE feedback would show up in this queer store hints table but it's not there because we actually reverted the feedback you can also see in question plan feedback see feedback tried to do something right it and it provided no hint that's why that feedback hint is empty um and we just said we regressed the verification we did not persist it so should we have a record that we tried something and that we removed it from the the hints we don't we don't actually want to keep it and that's how CE feedback works gotcha okay so then just that last part I didn't quite understand um so why did we regress it and was that a good thing um so it is a good thing in the sense that like we didn't make the query run slower right our estimate got better but we didn't change the plan right and if you look at this from a holistic view of query optimization right the whole point of making these adjustments is to get better plans right better plans better performance but right the situation it's really focused on better plans and if you aren't changing the plan that model variant doesn't do enough to make a difference for your overall workload it's still the same plan our estimates right but the way the query executes didn't change okay got it that makes a ton more sense when we could bring in other things that could do more right right right yep makes sense Okay cool so we've got memory Grant feedback Cardinals cardinality estimation feedback I think we got one more we got one more you ready for degree of parallelism feedback yeah all right let's do it yes so degree of parallelism feedback is super interesting here we have a stored procedure we've executed with a parallel operator and you can see in the plan properties we had 32 degrees of parallelism this is actually of synopsis a time lapse if you will of running this same query over and over again with degree of parallelism feedback turned on and you can see that the degree of parallelism went down started the query feedback was 32 we suggested 20 we tried it with 20 we verify we validate three times between each and every new adjustment yeah that's so cool I know right like it we just validate validate validity and then eventually we get to stabilization and notice we're not going down in big leaps we're just going down 32 20 16 12. we we actually tried eight if you saw that there we tried eight and then we went back um and you can even look in query store plan feedback we were just looking at this for CE feedback and wow look at this not only did the degree of parallelism feedback kick in but so did memory Grant feedback which is really cool the same query is getting both of these all these features work together they don't work in isolation so they all work together which is like a great I don't know it's like a symphony I just I love it so much um but you can see in degree of parallelism we had a last good feedback of 12. so now we can even look at the statistics of this query running over time right we started out with a like longer duration and as we decreased the parallelism we actually got faster that's that parallelism efficiency concept I'm talking about less parallelism is faster you can see it here too when we look at just CPU time we're just getting faster and faster and then we kind of stabilize right and that stabilization point where the data points kind of level out that's parallel efficiency wow that's super cool I think the the first few that you showed where it was like oh we're at 20 now 16 now 12 and we're kind of going to stay there and see if it's stable and then we're going to try eight and then oh eight doesn't really give us much so we go back to 12. again the Do no harm principle is really critical for these features right we really try to make sure that what we're doing is positively impacting because we're learning just from your workload trying with just this one query right like it we just we just really work hard to make sure that it's all positive all good um right oh I got it um so here's the deal is degree of parallelism feedback right that in that works for every single query in your workload right so imagine being a DBA and trying to like find that perfect parallel efficiency point for every single query in your workload yeah that sounds overwhelming right like but instead we'll just do it which is yeah pretty cool no this is awesome um Kate I can't thank you enough like I learned so much it was so cool to see all these things in action I feel like I learned a lot more by seeing how they actually work and seeing it in reality versus you know seeing it on a slide uh so Kate thanks so much for coming on sharing these awesome demos with us uh to our viewers we'll put some links in the description so you can learn more about these feedback capabilities how it can just help you run faster if you like this episode give it a like leave us a comment and let us know what you think and we hope to see you next time on data exposed [Music] thank you