FIVE MINUTES EXACTLY
Thanks for watching!
Video Summary
In this video, I delve into a quirky yet important aspect of SQL Server execution plans: the warning for implicit conversions. I start by sharing a personal anecdote about my silent air horn and how it led to a bit of downtime, but now that I’ve wrapped up those preparations, I’m excited to share insights on query optimization. The video highlights a common scenario where an implicit conversion in a query can trigger a warning, making you think the execution plan might be suboptimal. However, I demonstrate through examples how this warning isn’t always as dire as it seems; sometimes, even without the conversion, you won’t get a seek plan due to missing indexes. By creating these necessary indexes, we see the difference in performance, with SQL Server finally able to use a seek operation instead of a scan. This video aims to clarify when such warnings are actually significant and when they might be misleading, helping you make more informed decisions about your query optimization strategies.
Full Transcript
Erik Darling here with Erik Darling Data. Still do not have a proper air horn. Still have a silent air horn, which is very, very sad and depressing for me. I’m going to get on that this weekend. I’ve been a little busy. Now I’m less busy. Now that I don’t have bits to prepare for. Now that I just have a couple other things to prepare for. But now that I’ve delivered the material, I’m really happy with it and I think I’ll be able to move on from there. But anyway, I wanted to talk to you about something kind of funny that shows up in execution plans that can be a little bit misleading. And it’s a warning about implicit conversions. And I’m just going to jump in and show you the warning so that we’re all on the same page here. Now when I run this query over here, when I hit execute, I get query plans turned on. I’m going to run this and I’m going to get results back and that’s not really the point. The point is that when I go into the execution plan, I have this little bingo bango over the select operator. Sign that SQL Server is angry with me. We have summoned the wrath of SQL Server. All of a sudden, we have to worry about these things.
That little bang is coming up because we have an implicit conversion in this execution plan and it may affect the seek plan in the query choice. Yeah, it might do that. Yeah, crazy, right? Something went terribly wrong. And what went terribly wrong is that we have flubbed this query from the get-go. What we’ve done is tremendously idiotic. We are converting a column that’s an integer to be a varkar 10 and then comparing it to a string over here. I know. Who would ever do that in real life?
Anyway, what’s misleading about this is that if we run this query in a way that does not summon the implicit conversion gods like this, we still won’t get a seek plan. I know. Crazy, right? Over here. We are still just scanning the clustered index. Bananas. Bonkers. Outrageous. Slings and arrows, my friends. Slings and arrows. There is something different in the plan now, though. We do have a missing index request. Now, SQL Server is saying, hey, pal, between you and me, you create this index on this badges table on that user ID column.
We could do good things together. We could have a good time. And that’s exactly what you need in order to get a seek. See, without an index on that column, the way this query is set up, specifically without an index that leads on user ID. If we had user ID second in the index and we had another equality predicate first, then we could seek for both. But with this one with just one predicate, without an index that leads on user ID, this 22656 is just going to have to scan something else.
The primary key clustered index on the badges table is a column called ID, not user ID. So we are missing out on something fundamental there. But if I go and I create an index on user ID, well, I’m sorry, this takes a second for some reason. There we go. Now we’re cooking with indexes. Get rid of you. Goodbye. Now if we go back and run this one, we will in fact get a real snappy seek plan. Get that. Good. Yeah. Seek. Look at that. We did it. Thanks, ma. Look, ma. No hands.
But now it makes this warning reasonable. So now if I run this and we look at the execution plan, we are going to go back to scanning. Well, not back to. Now we’re going to be scanning our nonclustered index rather than our clustered index. But we still had to scan it. Now when we get this warning over here about affecting the seek plan choice, that’s not terrible at all to say. Thanks, Microsoft wording people. But now this warning is at least accurate because it did affect the optimizer’s ability to seek. So just a heads up, when you get that warning, that doesn’t mean that you have a good index for SQL Server to use to seek into.
That means that even if you did have the index, we wouldn’t be able to seek into it. So don’t think that just because you get rid of the implicit conversion, all of a sudden you’re going to see a seek plan, you still need an index to back it up. Anyway, that’s it for me. I’m going to keep this to five minutes. Thanks for watching. Again, I’m Erik Darling with Erik Darling Data. And thanks for watching and I’ll see you next time. What’s that stop button? Goodbye.
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 performance problems quickly.