A Little About Index Intersection Query Plans In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the fascinating world of index intersection plans with Erik Darling from Da Darling Da Data. We explore how these plans come to life when your table has multiple indexes and SQL Server decides to use a combination of them in a unique way. Unlike simple index unions where data streams are concatenated, index intersections involve joining those indexes together to produce the result set. I also share some practical advice on managing indexes effectively—avoiding unnecessary single key column indexes and opting for compound keys with included columns for better performance. Plus, if you’re curious about upcoming events or need personalized SQL Server support, I discuss how my services can help. Don’t forget to join our community by subscribing, liking, and commenting!
Full Transcript
Erik Darling here with Da Darling Da Data. All those Ds. Deepen the Ds there. In today’s video, we’re going to talk about, drumroll please, index intersection plans. No jokes about me not having a driver’s license, please. These are a type of query plan that you will see when your, you know, table has multiple indexes on it and SQL Server chooses to use some combination of those indexes. But it’s a little bit different from the index union thing because rather than just concatenating the two streams of data, we do something a little bit different and we’re going to talk about that in a couple of seconds as soon as we talk about how you can buy things from me. If you would like a membership to this channel, four bucks a month, not bad. That’s like, I don’t know, with inflation, that’s like quarter of a box of mac and cheese. Right? If you would rather have a quarter of a box of mac and cheese, I totally understand. So would my kids. Then you can do all sorts of other fun things that help you, that help me, that help you help me, like like and comment and subscribe because that’s nice things to do. If you need help with SQL Server problems, because I am a SQL Server problem fixer, I am available to do all of these things and more and my rates are reasonable.
If you would like some reasonable rates on high quality SQL Server performance tuning that will last you a literal lifetime, you’ll never have to subscribe to this chunk of training. You can get it for about 150 US dollars with this discount code. This link is also in the video description. Just like the membership thing and just like Intel timing out being able to find driver updates constantly in the background. That thing shows up like every 45 seconds. If you would like to see me live and in person without Intel driver updates timing out in the background, you can actually they might still show up because I have to use this laptop for those things too. You can catch me at past data summit with Kendra little doing not one but two days of SQL Server performance tuning magic, November 4th and 5th in Seattle, Washington.
And that’s that’s the last event that I have so far for 2024. 2024. Gosh 2025 sure did sneak up quick didn’t it. Golly feel like I just paid taxes. You can tell me if there’s an event near you that you think I might be a good addition to and if they are looking for pre con speakers, there’s a chance I’ll even show up looking pretty.
You never know. Crazy things, crazy things happen in this wild data world of ours. But with that out of the way, let’s talk a little bit about index intersection. Now, just like in the index union video, I have two single key column indexes.
And just like in the index union video, two single column key two single key column indexes are not strictly necessary for this to happen. And it’s just easier for me to do this way. I have to type less and I have to work less hard on the demo part of it, which means that I can record it and show you stuff better.
So that’s great for everyone. So in this first query right here, we are going to run a query that says where creation date is greater than this and last activity date is less than this. And the resulting query plan will miraculously use index intersection.
You can see that right here where we seek into this index and we seek into this index. But then instead, like in the index union plan, there was this concatenation operator. There was a regular concatenation and there was merge concatenation.
But now instead of concatenating, we actually join those two indexes together to get our results set. So we take all the rows from this one and we join all the rows from this one. How do we join those rows, though?
Well, it’s just like with a key lookup. SQL Server has the clustered primary key for the post table stored away as a hidden key column in both of those nonclustered indexes. Because they are not unique indexes, the clustered index key column, in this case singular, is an additional key column hidden away in there.
If this index were unique, the clustered index column would be treated like an included column. So SQL Server uses the ID column to join those two indexes together here. Just like with the key lookup, it would use the clustered index key column or columns to get data out of the nonclustered index and then seek into the clustered index to locate the additional rows that we need to get the additional columns that we need out.
So it’s almost the same principle, just with a slightly different join setup. Rather than a nested loops join, we have a hash join. And I don’t know, I guess SQL Server just felt strongly about that.
You might see other join types in there. Like you could see a merge join in there because I have put a merge join hint on this plan. And golly and gosh, doesn’t that just work out in our favor? But you can see why SQL Server did not choose the merge join plan naturally for the other query, which other than the merge join hint was identical.
Because it would have to fully sort both of those result sets in order to use the merge join. Now, you totally could see this in real life if SQL Server costed the hash join out of proportion to the two sorts in a merge join. Or if you had equality predicates on the two date columns, but two date-time columns rather.
But come on, who uses equality predicates on date-time columns? It’s obsessively difficult. Like, bleh!
I don’t know what would be wrong with you to do that. You would have to so very specifically be looking for something. Or a date column, maybe, but holy cow. Now, you might see SQL Server choose different join types.
So here, because I said it when I had to do it, we are going to use two equality predicates here. We’re not going to find any rows, and that’s okay. But here’s an example of where a merge join plan happens somewhat more naturally, because we don’t have to sort that input.
The reason why we don’t have to sort the input is because we are using two equality predicates. And if you’ve watched some of my other videos on indexes and how indexes work, you’ll already know that when you have equality predicates, like an equality… So in this case, we have an equality predicate on creation date, which means that the order of the ID column will be preserved.
And the query up above that, where we had a range predicate or an inequality predicate, greater than, equal to, less than, less than, equal to, all that stuff… That ordering is not preserved in the index. So that’s why it would have required sorting up there, but not down here with the equality predicates.
And just like with the index union plan, SQL Server is able to do index intersection plus a key lookup. So to imitate the index union demos a little bit, we are going to select the post type ID column and group by the post type ID column. And of course, since the post type ID column is not a clustered index key column, it is not going to be present hidden anywhere in either of these nonclustered indexes.
There is no invisible post type ID. So when we run this query and we get the resulting query plan, even though nothing comes back, SQL Server still faithfully executes the query. Does a seek into this index?
Does a seek into this index? Uses a hash join once again to join those two indexes together on the ID column, which is the clustered primary key. And then we have a key lookup back to the post table to get that post type ID column.
And just to sort of bring the point home that I was making before, we are outputting post type ID from the lookup. And our seek predicate is the ID column because that is the clustered primary key of the post table. That’s the column that we use in the lookup to locate the correct row for the columns that we need.
Pretty cool, right? So again, single key column indexes, generally not the first thing that I recommend for SQL Server. You can end up with some tremendously weird, complicated, probably ill-performing query plans.
If you are the type of person who creates a single key column index on every single column in the table, not usually a good idea. Compound index keys with included columns are usually the best strategy for either OLTP or, you know, reporting type workloads or rather mixed workloads. You know, there’s always the columnstore question, but, you know, that one’s a little bit too much to answer.
And a video that’s not about the columnstore index question. So we’re not going to get into that. But we are going to say thank you for watching. I hope you enjoyed yourselves.
And I hope you learned something. And I hope you will stick around and watch more videos because I have many, many more to come. Many such videos. Who knows what the next one will be about? It might even be about something crazy like doping bitmaps.
You might even put money on such a thing. If there is like a betting channel for me and what my next video is going to be about, I would heavily suggest voting on .bitmaps. I would not suggest voting on join or clause plan patterns.
I’m not saying that because I’m going to switch the order of those and make you lose all your money. I would much rather have you spend your money on me in other ways. Anyway, thank you for watching and I will see you over in the next video.
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.