A Little About Parameter Sensitive Plan Optimizations In SQL Server 2022
This week, I’ve got a coupon that offers an even steeper discount on my training than the one at the bottom of the post.
It brings the total cost on 24 hours of SQL Server performance tuning content down to just about $100 USD even. Good luck finding that price on those other Black Friday sales.
It’s good from December 23rd-27th, or until I remember to turn it off. Hurry along now.
Thanks for watching!
Video Summary
In this video, I dive into the world of parameter-sensitive plan optimizations in SQL Server, specifically focusing on how Microsoft has been addressing the challenges posed by parameter sniffing through intelligent query processing features. I discuss the evolution of these features from earlier versions to the introduction of parameter sensitive plan optimization in SQL Server 2022, highlighting its purpose and functionality. I also touch on the upcoming vNext version, speculating about potential new features like vector search and regex support for T-SQL, which are sure to bring both excitement and challenges for database administrators and developers alike. Throughout the video, I share insights into how indexing can significantly impact query performance and plan choice, emphasizing that while a perfect index might not solve every parameter sensitivity issue, it’s crucial in many cases.
Full Transcript
Erik Darling here with Darling Data. And today, me and Bats are going to talk to you about a little bit, a little bit in, a little bit about, I guess, I guess this is going to be a little bit about video, a little bit about SQL Server’s parameter sensitive plan optimization. Now, I realize SQL Server 2022 is now, I don’t know, two, almost three years old, I guess. Getting close to time. So, I guess, the next to 2025? We’re getting close to that, I think, that vNext feeling. And the reason I say that is because the last few SQL Server 2022 cumulative updates, they haven’t really had a lot of meat to them. They’re starting to ease off the big changes in there. So, I get this feeling, deep in my bones, that we’re going to start seeing some vNext action soon.
Part of that is also because the, what do you call it, the vector search stuff in Azure SQL DB recently went from private preview to public preview. Ooh la la. I still don’t have such like, you know, whatever ANN type indexes available for that yet. But the fact that it went to public preview also kind of indicates to me that there will be, there will be blood soon. Some vNext blood will be shed. And I’m excited about that. I mean, the vector stuff, I’m sure a lot of people are going to want to do it.
I don’t know how good it’s going to be yet, but we’ll see. I do have some confidence in the PMs involved there. But really what I’m excited about is that Regex is finally coming to T-SQL. Because that was also an Azure preview thing that I think recently became public. And what’s going to be fun about that is all the people who start putting that in where clauses. I can’t wait. I am so excited for regex where clauses where I get to tell people you’re going to have a bad time.
So, without further ado, let’s talk a little bit about me and you. If you feel, if you’re feeling like the pre-Christmas spirit, if you’re just like, it’s getting close to Black Friday and I just want to spend money, the very first link in the video description next to the words, become a member, will take you to a page where you can donate $4 a month to the Erik Darling brunch fund. It’s not going to go very far. Eggs in New York are for some reason $25. But you can do that if you’re feeling real spendy. If you’re not feeling real spendy, there are other numbers that make me happy to keep producing this content, like the likes and the comments and the subscribes. So, those things, wonderful. Beautiful. If you are feeling extra spendy and you’re like, wow, that Erik Darling sure does know a lot about SQL and, or boy, he sure does know a lot more than me about SQL.
And I’ve got this SQL Server causing all sorts of problems. Boy, I bet Erik Darling would be good at some of this stuff. You can also hire me to do some consulting and I can show up just wearing this same shirt, just sitting down at my desk over there instead. And we can talk about your SQL Server specifically and we can make that thing go faster. And as always, my rates are reasonable.
Ding. Ding. Ding.
Ding. Ding. Alright, cool. If you would like some high quality, low cost training content, at a much better discount than those other Black Friday sales, you can get all of mine for about 150 USD and that is for life. That is not just for a year. So, you could enjoy that. And, you know, like I always say, the longer you live, the less it costs because that cost that’s amortized or whatever the word is for that, where the longer you have it, the, like the more that initial investment spreads out over time.
So, like $150 for the first year is $150 and then for the second year, it’s $75 a year. And then for the third year, I don’t know anymore. Okay, give up. So, upcoming events, let’s be honest, I’m not doing anything until the new year anyway. If you’ve got any events coming up in, like, spring of 2025 or summer of 2025, let me know. It was announced at Pass that Redgate, a cool bunch of cats they are, are going to do a few mini-Passes this year. They’re going to be doing Dallas, New York, and Amsterdam.
I hope that I can make it to the New York one since that’s my home turf, but we’ll see if I can get DAL and AMS on the list too. That’d be nice. I do like to travel, so that’d be cool. So, you know, hopefully show up at those, but if you’ve got another local event that you’re thinking, wow, I sure would like Erik Darling to show up and say, All Day I Dream About Sets. Wait, All Day I Dream About Sets? That was a backwards, backwards grope at my local conference.
Boy, how do I get that? Well, just let me know and I’ll be there wearing my Adidas. With that out of the way, though, let’s talk about these parameter-sensitive plan optimizations, because that’s fun. Good stuff to do. So, as far as I’m concerned, this sort of thing has been a long time coming.
When Microsoft first started adding these intelligent query processing features, I think a lot of them were sort of geared towards dealing with parameter sensitivity, like side effects, like adaptive joins, where you were just like, whoa, crap, I chose a nested loops join, and I should have used a hash join, or vice versa. The merge join isn’t in there. Good job. Good job not including that turd.
And then stuff like the memory grant feedback, where it’s just like, hey, this memory grant, that was a good guess. And then they’re like, wait a minute, that memory grant was a really bad guess. Now, granted, the memory grant feedback isn’t a runtime adaptive thing like adaptive joins are.
That’s like after the query executes. So, you still have like the query that runs, and you’re like, wow, that was way slow. That spilled everything to disk.
And then, like the next time it runs, as long as it was like a big plan again, that bigger memory grant would make sense, because the memory grant would get adjusted up. But if it was a small plan again, TQL Server would be like, oh, wait a minute.
It was just that grant back down. And then a big one in the run, and you’d be like, oh, that sucked. And then back it up and down. So, you know, that one is not as great, but whatever. Let’s not get carried away with ourselves here.
But, yeah, like a lot of, like, I think a fair amount of the intelligent query processing stuff was to deal with sort of like the side effects of parameter sniffing. Then in 2022, we got this feature, the parameter sensitive plan optimization, which was designed to more directly deal with parameter sniffing by assigning different query plans, three different, up to three different query plans.
Well, you probably get three, right? You get three. Like, like, SQL Server decides what they are when they, like, get compiled.
But you’ll get, it has like three plans kind of in mind, I guess, based on a single equality predicate parameter. So no inequality predicates, no, like, double parameters. It doesn’t matter if they’re on different tables.
One parameter, one equality, three plans. So that’s what you guess. But what’s funny is, well, actually, what I think is good is that the feature is called the parameter sensitive plan optimization. Because when you have parameter, oh, ZoomIt is just being an absolute turd goblin on this one.
There we go. So when you have parameter sniffing, this is when the optimizer creates and caches a plan based on a set of parameters that the query is initially compiled with. And that’s just parameter sniffing.
And that’s something that you generally want. You don’t want SQL Server coming up with a new plan every single time. I’m not allowed. I don’t know if I’m allowed to say the person who I would normally attribute this to.
I don’t know if I’m allowed to attach their name to this. But I’ll say someone I know who does a lot of work with really highly concurrent workloads was complaining about Postgres migration because Postgres does not offer this. Like you can sort of do like a plan per connection.
But you can’t like you don’t get like a global plan cache like SQL Server has where you cache a plan and any query can reuse it. And sometimes the cost of creating like if you have a very highly concurrent workload where queries are always coming in and blah, blah, blah, blah, blah. The fact that you’re, you know, you used to go from like, you know, zero milliseconds because you were reusing a cache plan to now like 10, 20, maybe more milliseconds because you’re coming up with a plan every time.
That introduces a level of latency to your workload that is unkind. So that’s not fun there. And then parameter sensitivity is a different issue because parameter sniffing is a generally good thing that you want happening.
And then parameter sensitivity and wow, ZoomIt is just having all sorts of bad problems today. Thanks. Thanks, sysinternals.
Parameter sensitivity is when you when parameter sniffing happens, but then the parameter that you pass in, it does not create a good plan. That’s a rather does not cache a plan that is a generally good set of choices for other sets of parameters. So the easiest way to think about it is like, you know, you cache a plan for Rhode Island, but then the next query that runs is like Texas or California, or something like that, where, you know, you have a much, much bigger population of data than Rhode Island or Connecticut or what’s another small state in there?
No, Maryland or something. I don’t know. I actually know.
I don’t actually know what Maryland looks like. I don’t know. Is it Baltimore? Is it Delaware? I don’t know. I couldn’t draw Delaware. All right.
The ramparts we watched. But the first one is usually a good thing. The second one can pose problems. You know, but I think that the parameter sensitive plan optimization was a pretty good fit for the intelligent query processing set of features. Because, you know, like a lot of what I had to do as a consultant to deal with parameter sniffing was dynamic SQL.
I know it sounds counterintuitive. I’m not going to lie. There are a lot of recompile hints involved in those fixes, too.
No BS there. Recompile saves a day most of the time in a way that most people are okay with dealing with. Writing a lot of dynamic SQL, especially like branch dynamic SQL.
I actually have a whole video called defeating parameter sniffing with dynamic SQL on this very channel that you can watch. That goes into some of the ways that I some of the things that I did over with clients to help them with their problems. Now, a lot of the reason why this happens, why you have like parameter sensitivity issues, will come down to indexing.
Like that’s not the only thing, but indexing is going to be a big part of it. Like just like to give you like an idea of like the number of times where the plan chosen for a small amount of data was like little index seek, little key lookup, bam, everything was done really quickly. Then for a large amount of data turned into like a much larger index seek and then a much greater number of key lookups was like the parameter sniffing problem was huge, right?
There were like there were all sorts of other things, of course, like serial plan versus parallel plan, stuff like that. But in general, like but in general, like it was almost always like a costing choice between like like seek, little seek lookup and then like clustered index scan. So there was a lot of there were a lot of times in like indexing would be a way to give SQL Server one like much better plan choice than like several icky plan choices or one or rather a good one good plan choice.
And like several other icky plan choices for like other parameters, other predicates passed in as parameters. Now, even a perfect index like isn’t going to solve every single problem. That is the absolute that is like that is like my version of like the classic parameter sensitivity issue.
But there are all sorts of other things that can happen in a query plan based on parameters and cardinality estimation and all that other stuff. The types of joins you use throughout the query, the order of those joins, memory grants, whether the query goes parallel or not, types of aggregates, the placement of aggregates. Sometimes SQL Server is really good about like pre aggregating data, like for a join column before a join.
Other times it’s just like, oh, what’s the point? There’s only like a thousand rows. And then it’s like, wait a minute. There were like two million rows this time. Oh, we should have crunched those down.
And of course, another big one is especially for newer versions of SQL Server is whether or not batch mode gets engaged. There are really a lot of possibilities. There are so many things that can happen while SQL Server is coming up with a plan because of costing cardinality estimation, all that stuff that you really like would have a hard time accounting for all of them in a simple video like this.
And this doesn’t even get into all of the crappy things that you do to SQL Server when you write your queries, table variables, local variables, optimize for unknown, writing non-targable predicates, using the wrong cardinality estimation model, row goals, out of date statistics. And like, I don’t know, like CTE that you join to 70 times and like joins with or clauses. And this list goes on.
The mind continues to boggle. So let’s talk a little bit about what exactly you see when a plan gets the parameter sensitive plan optimization. If you look at the query text, you will see now that I formatted this a bit because I really dislike that it’s just one big long string at the end of the query.
Like if you look at it. But so I formatted this a bit. But this is what you’ll see.
This is not an option hint that you can specify with your query. At least not yet. Maybe someday Microsoft will be like, oh, yeah, you can do that because we trust you. Just kidding.
I don’t think that’ll happen. But you’ll see this option plan per value. You’ll see query variant ID with the number one through three in there. This just happens to be three, but you’ll see one, two, or three.
And then you’ll see this thing says predicate range. And this will give you the column and the parameter that was chosen for the parameter sensitive plan optimization. This is the cardinality that will guide which query variant you get.
Now, there are all sorts of reasons why the parameter sensitive plan optimization won’t actually kick it. At least as of SQL Server 2022. I’m on CU 16 right now.
And there are 40 reasons for that. Some of them more obvious than others. Things like no parameter with recompile or unrecompile. I don’t even know what unrecompileable means.
You turn off the database scope configuration. You have a query hint that would make different plan choices impossible like option loop join or hash join. There’s other stuff in here.
Query text too large. All right. Okay. I don’t know. Non-inline UDF. Skewness threshold not met. Compat level below 160. Other threshold not met.
I don’t know. Who knows what that means. System DB unsupported objects. I don’t know. There’s all sorts of stuff in here. But you can use extended events to figure out maybe why your query is not getting this optimization if you are particularly keen on it. So what’s interesting to me is like not only how SQL Server chooses which query or rather sorry which parameter will be the focus of its parameter sensitive plan optimization.
So I have two columns in the post table that are fairly highly skewed. This is the parent ID column and I’m only returning the top 10 from this because like you can see that parent ID 0 has 6 million rows and like the second highest one has 518. And then everything below that is just a dwindling number of parent IDs.
Right. We don’t have that many of them. The parent ID 0 is for of course for questions because they don’t have a parent. So there’s 6 million questions and then there’s like you know 11 million answers to those questions and the highest at least for the Stack Over 2013 database this post ID has 518 answers.
Everything below that is a much lower number. Then there’s this post type ID column which you know has a pretty high spread across like eight values. So there again there’s post type ID 2 which is answers.
There’s 11 million of those. There’s post type ID 1 and if we zoom out if we frame this up a little bit better. Like you’ll see well so there’s there’s a few other types of posts in the post table that also have a parent ID of 0.
So like like like straight up questions on the site there are 6 million 220 223 but there are a bunch of other types of posts that add another like 500,000 or 50,000 to that. That’s most likely going to be like these two added in there right. So there are two these two columns are both fairly highly skewed.
But in this in this for this particular query. So that should be post type ID of 1 for a question and parent ID of 0. SQL Server will choose the parent ID column is the one that it thinks is easiest to parameter sensitivize.
Right. So this one chooses parent ID over here. For some reason I can never get it to choose post ID or sorry post type ID.
Even if I you know like like I tried recompiling this and I was like okay well we’re going to look for post type ID 2 with 1 8 4 6 1 8. This thing just you know even like again like I said even if I recompile the SQL Server will consistently choose the parent ID. So but for this query it doesn’t matter.
We’re just getting that we’re getting the top 5,000 and we have a good index in place for this one. So it’s not really that big a deal. So what I so what it was kind of fun is during my talk the SQL Server performance tuning tasting menu. This is one of my favorite demos and the way that I leave this demo off is this will either be a really great parameter sniffing demo or a really great demo that shows off the parameter sensitive plan optimization.
You up until like this most recent cumulative update this thing would never get the parameter sensitive plan optimization. So see you 16 someone tweaked something or torqued something in there and this thing started getting it. This thing only has one parameter vote type ID and this thing used to never get the parameter sensitive plan optimization.
I used to have to show off using a temp table for this one but this is the where that parameter gets used and I just have the hint in here to make sure that no matter what compat level my database is in this query is gets is available to get the parameter sensitive plan optimization. Now the vote type ID column is skewed just like the post type ID column is there are just a few more vote type IDs. But you’ll see a vote type ID of two that’s an upvote there’s 37 million of those and then there’s like you know a few million of some others and then like high to low hundred thousands down to nothing of all these other ones.
So what I used to show people I’m going to show people was uh look at this this is so stupid when I run this for for for vote type ID for I get this query plan. And then when I run this for vote type ID to I get the same query plan and it takes like five hours to run. But the good news is this doesn’t happen anymore and we get the parameter sensitive plan optimization.
So if I run these two back to back and I realize I probably should have done that from the get-go but it only takes about four seconds. So you’ve already been here for 21 minutes. So what’s another few seconds do you we get two very different query plans here.
And if we look at the text that this thing gets will have this option plan per value thing like I showed you before terribly unformatted. This is why I formatted it before but here’s query variant ID 2 and down here if we scroll there’s query variant ID 3. All right cool.
The thing that kind of stinks and this is this is where we go back to like you know the good and bad of these things. All right because there’s always good and bad. What I used to complain about was that you know two and four four would run and we’d use the plan for two and four would run and we’d run it for vote type ID one and would use the plan for four and like that was no good.
But what I what I what I was hoping for was that like if I ever got the parameter sensitive plan optimization for this that SQL Server might be like hey this number of rows down here is not really similar to this number of rows. This number of rows is way more similar to this number of rows right like even though like they’re like they are far apart. Like I was hoping that SQL Server would be a little bit smarter about what it did for stuff like in this region.
But now when I run this for vote type ID one. That doesn’t quite happen. The parameter sensitive plan optimization did kick in and it did do something but the plan that we get is the same plan that we got for for vote type ID.
What was that for? All right. We get the same plan.
If we go and look at the query text, it’ll be query variant to whatever it was. But yeah, this thing actually reuses the plan for for for vote type ID one, even though vote type ID one has 3.7 million rows up here and vote type ID four has 733 rows down here. So, uh, excite like progress.
It did kick in. It did start working for for this query. So that’s exciting. But why wouldn’t you use like that middle ground plan for all this stuff that’s like in here?
Anyway, cool. Well, uh, that’s it for me. Uh, this, this video dragged on a bit longer than I thought it would, you know, I’m a rambling man.
Uh, but anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in a video hopefully with, uh, I don’t know, a happier outcome. Here, here at Darling Data, we love a happy ending.
Now, we’re doing a nice differing journey. Appreciate it.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.