Parameter Sniffing, Predicate Selectivity, And Index Key Column Order In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into how predicate selectivity can significantly influence SQL Server’s execution plans and decision-making processes. We explore a variety of scenarios where different selective predicates lead to unexpected missing index requests and confusing parameter sniffing issues. By examining specific queries and their corresponding execution plans, I highlight the importance of understanding both predicate selectivity and indexing strategies in optimizing performance. The video also touches on the quirks of SQL Server’s missing index request feature, which often doesn’t consider column selectivity when suggesting indexes, leading to potentially suboptimal choices. Throughout the discussion, I provide practical examples and demonstrate how different indexing approaches can drastically affect query performance, making it crucial to carefully evaluate and manage your indexes based on real-world usage patterns rather than generic advice.
Full Transcript
Erik Darling here with Darling Data. In this video, we’re going to talk about how predicate selectivity can lead to a number of things. SQL Server might choose different execution plans based on it. You might get some inopportune missing index requests. Not because of predicate selectivity, but because the missing index request feature is garbage. And how it can also make index choice and parameter sniffing issues quite strange and confusing. So we’ve got a few things to cover here. So we might as well get started before anything weird happens. I don’t know, maybe an asteroid will hit my building. I don’t know. Times are, times are, times are strange, my friends. Times are strange. All right. So, we have, I’m going to do this just in case, because, you know, why not? Who knows what I may have forgotten to do before? My brain isn’t what it used to be. Local factors and such. So what I want to show you is first that I have some varying selective and non-selective predicates on these two columns in the POST table. So parent ID, less than parent ID, less than one, parent ID, greater than, well, that number, parent ID, score less than one and score greater than 19,000. And these all give slight, well, I mean, these ones give sort of similar counts, right? So about 6 million there, about 6.2 million there, 23 there, and three there. So some, so like some filters on parent ID are selective and some like filters on score are selective, others not so much.
When, when, when people give you sort of like stock, run of the mill indexing advice, and they say things like, oh, always put the most selective column first in the index. Well, sometimes that’s hard to do, because you search different columns differently. And even sometimes you might have equality predicates that match far, far different numbers of rows from one, from one to another. And so like, you know, what really, I just want you to understand that the sort of stock indexing advice stuff is, is just what it is. Stock, it’s not necessarily what you should, what you need to follow in every circumstance. So with that out of the way, let’s look at a stupid missing index request. And the reason it’s stupid is because the optimizer is not very helpful when it comes to deciding on key column order for missing indexes.
So if we look at these two queries, we can see that we have mixed and matched, right? Actually, let’s go, but go to the results because I named all these columns, the name of these results very helpfully. Non-selective parent ID with a selective score, we still returned one row. And then a selective parent ID with a non-selective score where we still returned one, well, I mean, obviously one row, but a count of one. So we filtered this down to one single row with those predicates. But SQL Server asks for the exact same index for both of them, right?
One is on parent ID comma score, and the other is on the exact same thing, parent ID comma score, regardless of how selective or non-selective these predicates are. Now, there is a very good Q&A with a fellow, well, I don’t know. I mean, I met him a few times. I don’t know. I don’t really know how to define that relationship. I’ve met him a few times in person. I haven’t seen him or heard from him in forever. A guy named Brian Reebok, like the sneaker Reebok, but with one E instead of two.
I don’t know. Maybe he’s a secret heir to the Reebok fortune and some uncle died and he’s like, well, screw SQL Server. I don’t know. I don’t know what happened. But yeah, so there’s a Q&A on Stack Exchange where he asks a pretty good question. And what we find out from Stack Exchange is that basically SQL Server chooses the order of columns in the missing index request by the column’s ordinal position in the table.
All right. So it’s all written out for you here, so I don’t have to go repeat anything. But that’s the basic gist of it. So because the post ID column, sorry, the parent ID column is like further up in the list in the tables definition, like the create table definition, the ordinal position of the column is first. SQL Server is just like parent ID, you first, no matter what.
It doesn’t think about things any further than that. I mean, that does separate equality and inequality predicates into different things, but within each of those bunches, it’s really just column ordinal position in the table that dictates things, not column selectivity or any further thought or assessment from the missing index request feature. So just to prove things out a little bit, because that’s what I like doing here.
I like to make sure that you get plenty of proven pudding from me. We’re going to create two different indexes. I apologize for leaving these highlighted, but one is on parent ID, then score, and the other one is on score, then parent ID.
All right, so stick with me on this, because this is all leading up to something very useful knowledge for you. Smart things you’ll be able to take immediately to your job for the rest of today before you’re missing some fingers tomorrow, because it’s the 4th of July. All right, so let’s run these four queries.
And what I’m going to do with these four queries is I’m going to execute them, and what I want to show you here is that for the first two queries, I am letting SQL Server choose the best index that it possibly can. Right? We have no hinting on these things.
SQL Server is free to choose whatever it wants for an index. On the second two queries, I am telling SQL Server which index to use, and I am obviously, I’m playing favorites here. I’m maybe spoiling the results a little bit.
I’m sorry about that. I often do that for clarity here. But if we look at these four queries, they all return the same number one. But if we look at the query plans, the two where SQL Server got to choose on its own used two different indexes.
This one used tabs. This one used spaces. And they both finished very quickly.
All right? Look at all those zeros. Goose eggs across the board. Couldn’t possibly be faster than that. Nolan Ryan would be jealous of all those zeros. And then for the two queries down here where I told SQL Server which index to use.
Right? I chose backwards. Right? These are backwards index usage up here. These both, I mean, it’s not disastrously slow in this case, but it is noticeably slower.
Right? These are about half a second where these are zero seconds. And if, you know, you’re talking about queries that execute quite a bit, you know, or like, you know, the schools of thought when it comes to looking at looking for queries to tune is, you know, you can look at what uses the most total CPU or has the most total duration. Or you could look at what uses the most average CPU or what has the highest average duration.
And you could kind of go from there and try and start to figure out, like, okay, like, what do I want to go after? Now, if you go by total CPU or duration, what you’re going to find is generally queries like this where, you know, like, they may execute the most and use the most CPU or have the highest duration in total. But every individual execution is pretty fast.
These ones are tougher to tune generally. Right? Like, this one wouldn’t be tougher to tune. You just, you need a better index for that. You switch the index order.
One would be zero seconds or, I don’t know. Maybe you whack the person who put the wrong index hint on these queries over the head and then delete the index hint. They’d be fine.
But, like, these are the kind of queries where, like, you know, you might see server CPU usage drop pretty significantly. If you have, like, something that executes hundreds or thousands of times a second and you bring it from 500 milliseconds to zero milliseconds, that could bring resource utilization on the server down pretty well. Now, like, you know, counter that, if you go by, like, average duration or average CPU and you, you know, start tuning those queries, then you see, like, you know, like big chunks of CPU come down because you have these queries that used to run for, like, 30, 40 seconds or longer or probably sometimes much longer.
And, like, data’s gobbled CPU the whole time. And, like, you’re no longer doing that. Right?
Maybe you got a parallel query to a fast single-threaded plan or just a much faster parallel plan or something like that. Just as an example, today working with a client, you know, we had a query that was running for a minute and 20 seconds. And after a little tinkering and forcing the use of the legacy cardinality estimator, it went from, like, a minute and 20 seconds to eight seconds.
Right? So it was a, you know, handsome use of a temp table and the right cardinality estimation model. This thing was flying.
And so, like, for that query, you know, a minute and 20 seconds of just this thing chugging along, eating CPU up, we no longer had that. It was down to eight seconds, so we no longer had those sustained bursts of CPU getting chewed up. So there’s all sorts of different ways to approach that.
And sometimes there is some glory in tuning these queries that don’t run for, like, hours or minutes or something because they might run a ton. And you might be able to have, like, a nice, like, you might be able to kill some of the mosquitoes in that swarm by tuning those up. So what we care about in this one is more along the lines of this, where index choice and predicate selectivity can make parameter sniffing issues a little bit more difficult to sort of figure out.
And what’s interesting here, to me anyway, is that I see indexes like this a lot. And when I see indexes like this a lot and I start trying to talk to people about, like, okay, well, you know, these both pretty useful to queries. Like, you know, even, like, you look at the index usage stuff, you know, like, they both might get used, but you don’t know if they’re being used well.
Right? All you can see is that queries choose them. You don’t know why they choose them or what they do with them. So you need to be a little bit careful with how you choose to either keep or remove or merge these indexes in together.
Because until you see the queries that hit them and, you know, understand how those indexes get used by those queries and if the usage is good or not, well, that’s, you know, there’s a lot to figure out. Right? So we have this store procedure here.
And this store procedure only takes one single parameter on a column called score. And what we’re going to do is look at two sort of different executions of this thing. Right?
We have one where we use a very selective score and one where we use a very non-selective score. Now, the store procedure itself isn’t doing anything all that interesting on its own. We are selecting the top 5,000 ordered by reputation descending from post joined to users.
And we’ve got some columns in there. And I don’t know. I mean, this calculation isn’t really doing much of anything weird or interesting or even particularly useful. But we’re looking for post types of two.
And we’re just filtering on score out here. So that is about it for what the procedure does. But you may notice that we are still executing these two.
And, well, we’re not actually finished executing this one. We have not finished executing this one yet. But we just did.
So lucky me. I was able to talk my way through that. So let’s look at what happened. And SQL Server chose this execution plan. This is a serial nested loops plan.
And what SQL Server chose to do was start with the post table, seek into there, find some rows that we care about, do a key lookup, and then join to the users table over here. And that worked out pretty well for when we were looking for a very selective score. So, right, typical parameter sniffing thing, this is a good plan for something that’s very selective.
This is not a very good plan for something that’s not very selective. The times change pretty drastically in here. We spend 1.8 seconds seeking into this table, way longer than before.
We spend 18 seconds in this key lookup. We spend almost 8.5 seconds in this seek. And we just spill some in the top end sort.
And that’s a pretty rough gig. Right? Now, this might be an example of when you have bad parameter sensitivity. But you might not always hit that bad parameter sensitivity.
So, let’s say that sometimes you run for the big value first and you get execution plans that are generally fast for everyone. Now, these execution plans look different in many ways. The order of joins is different.
This is a parallel nested loops join. There’s a sort really early on. And we still have the top over on the far side of the plan over here. But we have a sort right here now that we didn’t have before.
So, there’s a lot different between these two plans. And if we come and look at those, and we’re going to run these both with the recompile hint on them so we don’t parameter sniff anything, we can sort of start to parse these differences out.
And this is just another thing that, you know, parameter sensitivity and, you know, having indexes that, you know, we don’t know where they came from, what their lineage is, why they’re there, why they exist, what queries they get used by, how they help. This is just sort of a good example of how that can make parameter sniffing problems weird and confusing.
Because you might get, you might see this query run and it takes 52 milliseconds. And you’re like, wow, that’s great. That’s awesome.
It’s not awesome sometimes when a bigger value needs to get passed in. And then you might see this running. You might be, well, 206 milliseconds. That’s not bad either.
I’m not going to kick that one out of bed for eating crackers or for going parallel, I guess. And so, like, there’s just a lot to think about in here. So this one, of course, uses a different index, right? So this one uses the smooth index.
This one uses the chunky index. And we just have sort of different things going on in these plans. But this is how, you know, how selective your predicates are and how good your indexing job is. These are things that can make parameter sniffing problems way easier if you’re doing a good job or way harder if you’re not doing a good job to troubleshoot.
And, you know, granted, there are all sorts of cool ways with, like, query store and plan guides to force plans for things. But it’s all about you making sure. It’s, like, you know, I would much rather see you, you know, if we dropped this smooth index because we’re like, you know what, this index is just not maybe doing us any favors in general.
You know, it’s being used but maybe not being used well because, like, you know, when we have parameter sniffing on this, it’s pretty ugly. But I’d much rather see you start to, you know, clean up those indexes, make sure that the indexes you have are the indexes that you need. I don’t have numbers for that sort of thing.
I don’t have, like, a number of indexes or a number of columns that I care about. I’m more of a quality over quantity guy. If your indexes are good and queries are fast and everyone’s happy, then maybe you’re doing a good job there.
Maybe you’re all right. But anyway, I don’t know. I started to ramble a bit there.
I apologize. Got off script a little. Blame it on an empty tummy. All right. I’m going to go do some stuff now that’s more like working. Thank you for watching.
I hope you enjoyed yourselves. I hope you learned something. If you like this video, thumbs ups and what do you call them? What do you call those? Positive comments.
Positive comments are appreciated. If you like this sort of SQL Server content, you can subscribe to my channel and you can join nearly… Hold on.
We have to get the updated count here. Nearly 3,814 other data darlings who subscribe to this channel. So you can get notified every time I post one of these videos and the first 15 minutes are pretty good and the last two minutes are a little bit of a ramble. I am aware of these things.
Anyway, thank you for watching.
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.
Hello Erilk,
Merci pour tes vidéos, qui montre la complexité de l’optimisation.