
Chapters
- 00:00:00 – Introduction
- 00:03:24 – A Demo Claude Wrote
- 00:05:19 – Fast Plans With Recompile
- 00:06:23 – Compiling for Vote Type ID 4 First
- 00:08:11 – When the Other Index Gets Compiled First
- 00:09:44 – How to Fix It
- 00:11:06 – Wrap-Up
Full transcript
Erik Darling here with Darling Data, your best friend in the SQL Server community. In today’s video, we’re going to talk about, well, I don’t know, it was either this past Tuesday or the Tuesday before. Someone asked a question about index usage and parameter sensitivity. And the answer that I gave is something that I see, I mean, just as commonly as like, oh, you go from like a nonclustered index thing, lookup to like a clustered index scan and you’re unhappy about it or something like that, where SQL Server switches which nonclustered index it uses based on the like a recompile and like the compiled parameter values, right? So we’re going to do that in this one. I should have hit click there. So it’s a slightly different parameter sensitivity problem, right? So down in the video description, you will find all sorts of helpful links for which you can hire me for consulting. Buy my training at a discount, sometimes even a steep one. Become a supporting member of the channel. Ask me office hours questions. And of course, please do spread the great word of this channel. Help it grow and flourish and blossom into a channel with almost 10,000 subscribers. I’m going to get that Amiga Repair channel someday. That guy’s a real jerk. I forget what his name is. Jeff, maybe. Greg.
Something like that. Something like that. Anyway, I am helping people. On top of helping people with SQL Server these days, I am also helping companies and people get better in their usage of the robots to do their daily tasks and chores. So I’ve got a training course that I’m working on recording now. It’s on pre-sale for $250. That’ll be going up to $500 when the full thing is published. And then I’ve got a new consulting offering for companies. It’s a one-day class where I can either remotely or impersonally show up and help your team do smarter things with databases and the robots. So whichever one makes sense for you is probably the one you should pick there. I can’t tell you which one. Ask a robot which one makes more sense for you. If you need free, absolutely free, SQL Server and Postgres performance monitoring, I’ve got all that for you at code.erikdarling.com. It’s very easy to set up, get running with.
You click a few buttons and point a few things in the right way. Maybe enter a password or something. Put in your Windows AD account or something. And Entra. Entra works with Entra, too. You do that stuff and it just starts pulling in all the good performance data that you’d care about looking at if you were a smart person who looked at performance monitoring data. I still have this thing coming up at Pass Summit West in Seattle. Wah! November 9th through 11th. It’s at the Hyatt Regency this year at 808 Howell Street.
Isn’t there a Howell character on Gilligan’s Island? I think so. Hopefully he’s not there. He’s very old. But anyway, I’ve got a pre-con there about Advanced T-SQL and I’ve got a regular session about isolation levels and you should be at both or I’ll beat you up in front of your loved ones. Anyway, let’s go look at this demo. Now, this demo was a little bit of an experiment because I had an idea but I didn’t have a lot of time.
I was pressed for time. I had stuff to do. Other commitments that I had to oblige. And so I said, hey, Claude, write a demo for me based on this. Use this table in the Stack Overflow database and here’s what I want to happen. I want different nonclustered indexes to get used by queries depending on the compile time parameters.
And Claude, because I guess I paid him well, Claude did a great job with this. So good job, Claude. I’m proud of you. So let’s go look at this demo. I’ve already created these indexes because as Claude notes in this carefully crafted demo script, they take a minute to create.
We can just scroll down instead of up. But we’ve got one index. The votes table is very narrow, so the indexes are narrow to sort of compensate for that. We’ve got one index on vote type ID and then we’ve got another index on creation date that includes vote type ID.
All right. And then we’ve got a stored procedure here called vote index sniffing. We can work on the naming a little bit probably, but, you know, at least it’s descriptive. That’s good. I’m not crazy about parameter stuff there, but, you know, we can let that slide probably.
And then we’ve got our query that does a select from the votes table and we take a look at our parameters in here, right? So, look at that. It even knows my ASCII font. What a good little robot that is. What it doesn’t know is I never look at this crap, right?
Statistics IO, get out of here. Logical read. That’s offensive to me. Just like vote type ID for means your post was offensive. So, if we run this here with a recompile, right, we get what I believe is a fast execution plan, right?
It finishes in two milliseconds, maybe some nanoseconds in there, but two milliseconds, I’m willing to round to that. We get an index seek into the vote type ID index. And then down here, we do a key lookup back to the clustered index, right?
So, there’s still key lookups in the plan, but that’s not the important part, right? Down here, we have an additional predicate where we evaluate creation date separately when we do the key lookup, right? This is often a sign that there’s a missing or incomplete index, but SQL Server is not telling us about a missing index on this table.
There is no helpful green text in there, right? And now, if we run this one, again, with a recompile directive on this, you’ll see that this thing still runs pretty quick, right? 10 milliseconds, I wouldn’t complain about that too often, but it uses a different index on the votes table, the one that leads with creation date and includes vote type ID.
All right, so this happens. Okay, great. Now, what happens when we compile a plan for vote type ID 4 first, right? We get our fast plan for this thing, right?
We’re, again, at two milliseconds, perhaps some nanoseconds that we just can’t see. Who could see nanoseconds? You would truly have to be a robot to see nanoseconds. But now, we’re using different parameter values, and SQL Server is like, ah, guess what?
We’re going to do something else here, right? And we run this, and this takes four seconds. It’s mocking me with 119 million logical reads.
How dare you? No, it took six seconds. Wow, 5.89 seconds, right? And this version, the seek is pretty quick, but notice that the cardinality estimates are a bit munged there. We got 3.5 million rows instead of 733, but that’s a very common parameter sniffy thing, right?
You keep the compile time cardinality estimates from the first one. So, SQL Server is like, hmm, I don’t know. I’ve got to do a lot more of these now.
And I know these numbers down here look weird. It’s 3,951 of 733. But we had to do 3.5 million lookups to get down here and get that. And, of course, because we evaluate this predicate when we do the key lookup, that filters out rows.
So, only the 3,951 of the rows here would match the criteria. So, that’s how many left, despite the fact that we really did do 3.5 million lookups just to figure those out. That’s not a good time if you’re paying attention at home, which maybe you are, maybe you’re not.
I can never tell what you’re doing on the other side of the camera. Keep it clean, people out there. Alright, so, and then this happens when the downmod gets compiled first, right?
And we still get the quick query plan after recompiling for this one, right? So, but it switches the index and it says, ah, creation date, obviously. Look, 3,951.
Ah, look how many rows we got. Then the rest do that many key lookups and all is fine. Ah, but we have a bad time when we do this, right? And you go kicking around.
Man, oh man, right? And now this, it flips, right? Where the time is spent flips. Now we spend 3.5 seconds seeking here into this creation date index, right?
Because we find just about every row in the table, right? Because when we executed this, it was for 2008-01-01, which, I don’t know, perhaps this feels unrealistic to you because it is the year 2026 and you’re thinking to yourself, well, that’s many years of rows, Eric.
Of course, that’s going to be terrible. But, of course, this being the Stack Overflow 2013 database, time ends on January, well, December 31st of 2013, really. So, still a lot of rows, but, you know, you might have rows in your tables that would qualify for that with different predicates.
This is just convenient for me. So, what do we end up with here, right? It’s a bad parameter sniffing situation where not the planned shape that flips, but just which index gets chosen first flips.
That’s not fun to deal with. Now, how you might deal with this in real life might depend on what you have control over, right?
If you have control over the code, you might identify some vote type IDs or some creation dates that indicate which index should get used and write some dynamic SQL to direct SQL Server to use the correct index for those values.
You might just throw a recompile hint on there, too. I don’t know. You kids are crazy with your recompile hints. You know, you might expand the indexes so that the lookup isn’t necessary in either case.
And you might have a good time with that, right? Especially seeing predicates on a key lookup. That’s never a good sign.
And you should fix those because it means that your index is not built to satisfy the query that you have written, right? You have predicate key lookups.
Your where clause is too much for the index that you’ve got. So, depending on what you have access to and what you feel like fixing and dealing with, either one of those might be a better choice.
But, again, I don’t know. Unless you call me and say, hey, Eric, can you come look at this for me? I will give you money in exchange for making my SQL Server faster. I can’t tell you what you should do.
But if you do that, then I can tell you. I’ll even write it down for you. It’d be great. All right. Cool. Thank you for watching. I hope you enjoyed yourselves.
I hope you learned something. And, well, I will see you next Tuesday for office hours. All right. Have a great weekend, everybody. And a Friday on top of that. Maybe it’ll be a long weekend.
I don’t think it is. All right. Anyway. Adios. Adios.
Going Further
Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.