A Little About Index Union Query Plans In SQL Server
Thanks for watching!
Video Summary
In this video, I dive into the fascinating world of index union plans, a somewhat rare but intriguing query plan pattern that can sometimes appear in SQL Server. I explore how these plans work by demonstrating examples where SQL Server uses two nonclustered indexes to satisfy a query with multiple equality predicates or an `ORDER BY` clause. I also discuss the differences between simple concatenation and merged concatenation, explaining when each is used and what you might see in the execution plan. Additionally, I delve into scenarios where additional columns are required, illustrating how SQL Server can use key lookups to retrieve necessary data post-index union. This video aims to demystify these complex plans, helping you understand why they occur and how to design indexes more effectively to avoid or optimize them.
Full Transcript
Erik Darling here with Darling Data. Today’s video, we’ve got another one of our query plan pattern videos. And this one is about the wonderful, the fabulous, the somewhat exceedingly rare, index union plans. All right, I know you’re excited for this one. Before we talk about that, the usual things apply here. If you would like to get a membership to this channel for as low as four American dollars a month, there’s a link to do so in the video description. Otherwise, liking, commenting, subscribing, all completely fine activities by me. If you would like help with your SQL Server, I can do all this and more at a reasonable rate. Ding! If you would like some affordable training on SQL Server Performance points tuning, it’s tuning. I’ve got all that and then some for about a hundred and fifty USD for about twenty four hours of content. You can, you can buy that and you can watch it for the rest of your life. How long it’s good for. There is no expiration date. Aside from like, I don’t know, maybe if I die and the bills stop getting paid. That might be it. Likewise, if I die, I won’t be at this event. Otherwise, I’ll see you there on my birthday, November fourth and fifth in Seattle, Washington at Past Data Summit where Kendra and I will be talking and just doing wonderful things with SQL Server and showing you how to solve all sorts of performance mysteries and become the data whatever you are. I don’t know, there are too many titles. Everyone wants to say DBA developer, but there are like 50 million data titles now and I can’t keep track of them all. So whatever data you do, get better at it with me and Kendra at Past Data Summit. And with that out of the way, let’s go do something fun with query plans because that’s what we love doing here at Darling Data. All right, so the first type of index union plan that you might see would look something a little bit like this. And what you’ll have are two index accesses to different nonclustered indexes on your table and then a concatenation of the results of those indexes. All right, now what I’ve got up here for indexes is I’ve got one on owner user ID and I’ve got one on score. And so when I run this query and I say, hey, I want to know where owner user ID equals zero or score is greater than 10.
SQL Server says, well, I’ve got a couple indexes here that are pretty good. I can use both of these. Now, I’m not often one that recommends single key column indexes. They work out well to show you the query plan that I care about in this demo. And this isn’t necessarily solely the domain of single key column indexes. You might see this if you had two indexes that led with those same columns and had maybe different key columns afterwards or in different included columns or all sorts of other different arrangements. You might also see SQL Server sometimes choose to do two other things.
You know, when SQL Server is picking which indexes to use and it needs to fuse indexes together from a table to satisfy all the columns from a query, it essentially has three options. Well, I guess, I guess technically four options. It can scan the clustered index. It can hit a nonclustered index and do a lookup back to the clustered index. It can do index union, which is what we’re looking at, and it can do index intersection, which is what we’re going to look at in the next video. So this is index union, where it takes results from two indexes, munges them together, and then does some, well, in this case, aggregation to get the records out. So the first example that we saw just uses regular old concatenation.
So it’s just taking all the results, plopping them together, and, you know, just producing the result that we need to count things over here, right? The other option that you might see, when you have sometimes two equality predicates, or sometimes if you have an order by, you might see things change a little bit, is if we run this, and what’s different in this query is that we are asking not for where up here we said score is greater than 10, here we’re saying score equals 10. So we have two equality predicates here, and in this plan, rather than just a regular old concatenation, we have a merged concatenation. So SQL Server is essentially taking two sorted inputs that it can merge together, rather than taking two inputs where either one or both is unsorted, and just cramming them all together into one. You’ll notice that this plan is a little bit different, because rather than, and I should have maybe showed these back to back, but it really only makes sense when I talk about this one thing, and it could get confusing otherwise, and confusing sort of like using a mouse to grab tiny little lines in SQL Server Management Studio. This plan changes a little bit after the concatenation, where the the unordered version, just the straight concatenation, immediately does a hash match aggregate, and the first operator after the merge join is a stream aggregate, which indicates you have sorted input already.
Otherwise, we would have seen an explicit sort operator here, where SQL Server would have put the data in order for us. Now, we’re going to jump into the crazy world of needing additional columns, right? Because like I said, when SQL Server is choosing which index or indexes to use on a table, it has options available to it.
Right? And one of those options is to do an index that we can use a union plan with a key lookup. So if you’ll notice in this query, we are selecting the post type ID column, and we’re grouping by the post type ID column. So we need that column from somewhere.
However, that column is not a clustered index key column. It is just a regular old column in the table. So it is not automatically going to be part of our nonclustered indexes. And when we run this query, we’ll see that SQL Server has chosen a somewhat different approach, where we still do the index union, right? We still hit our indexes here, and we still concatenate them here.
But now we have a key lookup back to the clustered index to get that post ID column. If we hover over the lookup and we see what was output there, we can see post type ID. So SQL Server can use an awful lot of indexes on your tables to satisfy different parts of predicates, depending on selectivity and other costing options. And sometimes that’s great, and sometimes it’s not. Where I see it being not great is when folks have added a whole bunch of single key column indexes to, say, every column on the table. And SQL Server chooses to use a whole bunch of that and have to put all those together. And then sometimes even additionally doing a key lookup to do that. If you see query plans doing that, and they look very weird and confusing to you, and you start wondering, is this a view? Why is it hitting this? Why is this table getting hit so many times? It might, the answer just might be in the index design being used for this stuff. Now, I’m going to say not single key column indexes, but like very nearby indexes can be incredibly useful for stuff like interval queries. But we’re going to talk a little bit more about stuff a little bit closer to that in the next video on index intersection, which is when SQL Server, again, chooses two nonclustered indexes, but in a slightly different way.
All right. So, with all that being said, this tiny little chunk of, this tiny little nugget of SQL Server knowledge, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you find this sort of content useful, or at least amusing. Pick a somewhat positive adjective from the potpourri of positive adjectives you have in your head, and just give me that one. I’ll take it.
It doesn’t really matter too much to me. And I will see you over in the next video on index intersection. Again, thank you for watching. My rates are reasonable.
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.