A Little More About Parameter Sensitive Plan Optimizations In SQL Server 2022
Video Summary
In this video, I revisit a subject that I explored in an earlier recording—parameter-sensitive plan optimization introduced in SQL Server 2022. Despite initial excitement about potential improvements and changes, my recent re-examination revealed that Microsoft did not make significant advancements to address the issues I highlighted previously. The feature still faces challenges, such as its reliance on a specific skewness threshold for histograms, which can lead to unexpected results. To help viewers understand these nuances better, I also discuss new insights shared by Paul White in his Twitter article, providing additional context and detail that were not covered in my initial video.
Full Transcript
Erik Darling here with Darling Data, and I am revisiting a subject. Of course, Intel likes to revisit checking for drivers, but I’m going to revisit a subject that I recorded a video about. I forget when, but I was very excited because it was about the parameter sensitive plan optimization that Microsoft introduced in SQL Server 2022. And, you know, I’ve had some, you know, general complaints about it. Stuff like the extended events for it not being so great. It not kicking in in situations where I really thought it should. And, you know, like, a lot of the stuff around the way that it decides which query should use which plans and the way that it, like, detaches the queries from the dynamic, from the SQL SQL, they call them. So, like, like, like, like, like, usually with parameter sniffing, you know, like, my, like, my go-to for many years has been to use dynamic SQL, which, when you use dynamic SQL in the context of the stored procedure, that SQL is no longer, like, closely associated with that stored procedure. There’s nothing in the resulting plan XML for the dynamic SQL execution, the execution of that, that ties it to the stored procedure. That’s why you always helps to put a little comment in there that says, I came from the stored procedure.
And, you know, you would think that, you know, Microsoft being in charge of everything involved in it could do something crazy, like, you know, overcome that. But no, they stuck an object ID in there. But, like, you have to parse out a bunch of XML and get that. It’s a nightmare. So, like, Microsoft really dropped the ball on that part. But I got excited because, you know, one thing that Microsoft says quite a bit is, like, well, we’re not fully documenting this because we want to be able to make changes to it and, like, you know, cumulative updates. It’s, they don’t, they don’t do service packs anymore, because that was too hard, I guess. But, like, they want to be able to, like, like, make tweaks to things. Not that I’ve ever seen much evidence that they actually make, like, a lot of tweaks to things without having to, like, redocument stuff or have, like, you know, oh, this documentation applies to SQL Server 2022 up to cumulative update nine. But after that, it’s this different. It’s this. Like, so I understand why they don’t want to do that.
But, like, here I was thinking, wow, hey, maybe they fixed something, like, an accumulative update that made this better. But they didn’t. It turns out that Erik Darling of Darling Data left some dangling data in one of his tables. And that is why we got the parameter-sensitive plan optimization. Of course, this, some of the, I’m also re-recording this because there was some new information that my dear friend Paul White blogged about, or, I don’t know, wrote an article on Twitter about, or whatever you want to call it, and documented a little bit of the internals of it that I, like, I wasn’t aware of. So we got that stuff to talk about. So before we do all that, let’s get into our relationship, how you and I can successfully coexist and cohabitate this plane together.
I forgot to, I forgot to move on to that slide, didn’t I? I’m not re-recording all that. Sorry. We’re just going to have to deal with that. If you would like to support my efforts in bringing this incredibly high-quality SQL Server content to you on YouTube, you can become a member of the channel and join the 50-some-odd other folks who are giving me $4 a month, or sometimes more, by going to the video description right down here.
If, you know, you ran out of money, I don’t know what happened, maybe the recent downturn in the stock market has taken its toll on your finances, and you no longer have $4 a month to spare, you can do all sorts of free stuff like like and comment and subscribe. If you would like to ask me questions that I will answer on this YouTube channel, I just started collecting them, so I haven’t done one of these yet, but if you would like to ask me questions, you can go to this link, which is also in the video description, and submit a question, and when I’ve piled up enough of them, I’ve already got a few good ones in there, right?
When I piled up enough of them, I will record answers to them, and I will answer your questions. Just remember that these are being asked privately, but answered publicly, so don’t put anything in there that might make you feel bad at a later date. If you enjoy the things that I talk about here so much, and they hit so close to home for you, that you’re like, wow, we could sure use the kind of consulting this Erik Darling does, well, you can hire me to do any of these things, and as always, my rates are reasonable.
If you would like to get some training content from me, you can do that too. Again, all of these links are way right down there, all you have to do is scroll and click a little bit. You can get all 24 hours of my performance tuning content for about $150 US dollars.
Try beating that. It’s a tough one. Upcoming events, we have SQL Saturday in New York City on May the 10th of 2025. Exciting times ahead.
You can stay in a business class hotel, like a Hampton Inn, or I don’t even know what else is over there. Like a Doubletree? I don’t know. Whatever. There’s lots of hotels.
It’s at the Microsoft thing in Times Square. I don’t know. If you can’t afford a hotel room, you can always sleep in the Port Authority bus station or any subway station. Everyone sleeps in the subways in New York.
It’s very safe, very accommodating. With that out of the way, let’s party. Let’s talk about this thing here. This was the demo that I was really pumped on because it’s one that when I was first looking at the parameter sensitive plan stuff, I was like, it’s going to be great if we can fix this store procedure, and it never did.
This one came along, and I was like, nah, not doing this. But then I installed the cumulative update, and all of a sudden, this started working, and I was like, they did it. They listened to me.
They did a thing. They didn’t. They didn’t. Microsoft continues to not listen to anyone except themselves, whatever burned-out, toasted-bud executives are just like, yeah, more AI. Really, really, really doing your job there.
Good job. All right. Okay. So what I had done was I had – there’s a demo that I do where I show a deadlock between a read query and a write query. And I have a reset because, like, one of the things I do is I have to do an update in there.
So I do this update, and then I have another update at the end that’s supposed to reset things back to where they were. The thing is, apparently, I didn’t run that. So here’s what happened.
And it’s kind of funny. And this ties back to some of the stuff that Paul White talked about in his Twixtr article, is that the skewness of – that has to be met, the threshold that has to be met for the parameter-sensitive plan optimization to kick in is if you look at the histogram for a table.
So, like, this is the histogram for the votes table. This is a full scan update, full scan, like, index creation, like, quality histogram. The lowest value in the histogram, I believe for a quality rose, has to have 100,000 times that number by the highest one.
But in this – in the votes table for the vote type ID column, the highest one is 37 million. Right? That’s what this number is here.
3.7 plus E – sorry, E plus 07. That is 37 million. That’s 3.7 million. That’s 37 million. So this is the lowest one.
We don’t have 100,000 times that. If we – if we had 100,000 times that, we would need something with 73,300,000 in there. So I’m going to – I’m going to show you what happened.
Right? So when I was – when I was doing my deadlock demo, one of the things that I do is, like, flip the vote type ID column around. And I do that for this one particular ID.
Right? So now if I run this and we look at the statistics, of course, we’re not going to see anything useful. Right? There’s going to be nothing good in here. I’m going to update these statistics.
Now, I’m using a full scan just because I don’t want to leave anything weird to chance. So, like, normally when I finished – when I finish my, you know, read query, write query, deadlock demo, I’ll run this update down here to reset stuff. This is what I forgot to do.
So what happens is when I – when I had this row in here where vote type ID was zero. So I had one row with one equality value sitting in there. So when I created my indexes up here and did all this stuff, what SQL Server saw in the histogram was this.
Oh, I’m updating statistics again. I’m not showing. That was supposed to be DVCC show statistics.
Let’s do that. So what I – what happened when I created the index on vote type ID was I ended up with this one row with the range high key of zero with one equality row. You can imagine that it’s a lot easier to get – hit that skewness threshold when you have something with – why did that take two seconds to run?
That’s bizarre. All right. I guess format really does suck.
So we would only need one other value with 100,000 rows in there. Of course, we have lots of that stuff going on, right? If you look at – shut up. So if you look at, like, this, like, that one equality, we have lots of stuff in there with over 100,000 rows.
And that’s exactly why it worked. Microsoft didn’t actually improve upon the feature. They didn’t relax anything.
They didn’t, like, do anything better. They’re just letting this thing lie. I don’t know why. But they didn’t actually improve it. They didn’t actually fix anything. They just said, yeah, go ahead. So when you have that one row in there, you do get the parameter-sensitive plan optimization, right?
So, like, when you get the parameter-sensitive plan optimization, you know it because you get this additional stuff at the end of your query that you didn’t put in there that shows you, like, the minimum and the maximum stuff and then, like, which column and parameter match they chose to give you the parameter-sensitive plan optimization for, that they’re going to generate multiple plans, like, depending on which bucket this parameter falls into based on the histogram.
And the thing that will come up… Oh, boy. What’s all this?
Oh, we got a lot of stuff in there now. Oh, okay. Apparently, I’ve just been letting this run for a while. That’s my fault. Well, but the original one that we hit into, that I hit, was this skewness threshold not met, right? That’s this thing.
Zoomit is not listening to me once again. So when I don’t have that row, this is the message that I get, right? We did not have enough skew in the histogram in order for the parameter-sensitive plan optimization to be triggered. Do I think this is good?
No. If you have a value with 733 records and a value with 37 million records, you sure as all get-out have skewness in your histogram. You just don’t have 100,000 times the skewness.
You don’t have 73.3 million. You have 37-point-something million. So you’re, like, halfway there? About?
Not, you know, somewhere in that fuzzy approximate count distinct area. You’re about halfway there. But you are not there there. So Microsoft did not fix anything.
It did not give us any joy or love. I’m going to close this because that is just weird. But I just want to show you the difference real quick here. And that’s going to be for this store procedure, right?
So if we run this and we execute this just for vote type ID 4. I don’t need to show you the whole parameter sniffing thing for it. This is with that additional row in there.
With that additional row in the table for where I accidentally left a vote type ID set to zero, we get the whole parameter sensitivity thing. Kicks right in immediately. Wonderful.
You did a great job. You worked. But, you know, if we come back up here and we did what I should have done when I finished the reader writer deadlock and we set that back to eight and then we update statistics, we’re not going to get that anymore. Because SQL Server is not going to see that one row with the one equality value for vote type ID zero.
It’s just going to see that lowest one for vote type ID 4 that has 733 rows. And we’re not going to get it anymore, right? We’re going to lose that whole thing.
So if I go and I rerun this procedure, I just like to do the creator alter just to make sure that we’re getting fresh plans for everything. But now if I run that for vote type ID 4 and we look at the execution plan, we don’t have that whole, we don’t have all that stuff at the end. We just have me forcing the compat level to 160 so that I would be able to use the parameter sensitive plan optimization.
So first I apologize. One, to you because I got this wrong. I was incorrect about this in my video.
Two, I’d like to apologize to Microsoft for accusing them of getting something right. Big sorry there, all of you. Your record remains untarnished.
And I’d like to thank Paul White for publishing a nice article on Twitter, Twixtr, that I will put a link to in this video description. So you can go and read it with all the great joy that I read it and you can learn more about this stuff. And yeah, I think that’s about it.
All right. It is time to go to the gymnasium so that I can, I don’t know, I think squat and deadlift today. So that’s a fun combo.
Anyone wants to see workout videos, maybe it will be more popular than the SQL videos. Who knows? Right? I don’t know.
If you’re tired of seeing my face and you want to see my butt instead, the squat and deadlift videos are pretty good for that. So anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. I hope that I learned something. Don’t count on Microsoft to make stuff better. It’s the first one. And yeah, all right.
We’re going to hit the gym now. Anyway, thank you for watching. Thank you.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.