A Weird Thing With NOEXPAND
Video Summary
In this video, I share a humorous and intriguing experience I encountered while writing training material on index views and the NOV expand hint in SQL Server. I discuss how using the NOV expand hint can sometimes prevent parallel execution plans when you might expect it to allow them, highlighting an interesting quirk of the optimizer’s behavior. This video aims to provide insights into why this happens and explore potential reasons behind it, while also emphasizing that the use of index views and the NOV expand hint remains valuable for many scenarios. If you’re interested in diving deeper into SQL Server optimization or need help with your database performance, consider supporting my work through donations or by subscribing to my channel to stay updated on future content.
Full Transcript
Erik Darling here with Darling Data. And today’s video is a little funny thing that I ran into. I was writing some training material up and I ran into a funny thing was talking about index views and the NOV expand hint. Now, there are many good reasons why you may want to use a NOV expand hint when dealing with index views, particularly in standard edition. You know, like perhaps because you don’t want SQL Server’s optimizer to attempt to expand the index view to the underlying definition. You know, maybe have worse performance. You might also want statistics automatically created on your index view. Good reasons to use the NOV expand hint, but I ran into something funny with it where it was like when I used the NOV expand hint, I was not getting the parallel plan that I wanted. But when I dropped the NOV expand hint, all of a sudden, happy, fun, parallel query. So we’re going to talk about that today. Down in the video description, you will find all sorts of links that are helpful to both of us, right? It’s a symbiotic relationship. You can give me money. I can make your SQL Server faster or you can give me money and you can receive training from me or you can give me money and also continue to receive YouTube content, which I mean, you know, I don’t get paid much by the hour for the YouTube content, to be honest with you. So, you know, that’d be nice. But, you know, apparently I’m like 10 million subscribers away from that being lucrative. So we’ll just have to we’ll just have to punch harder. Get that and get those numbers.
You can also ask me office hours questions for free. I answer five of those every Monday. And of course, if you if this if this content is up your alley, you may you should like and and subscribe, of course. And if you know someone whose alley this might also be up, you should tell them to to watch it and hopefully like and subscribe as well, because how else am I going to hit 10 million YouTube members and then I can finally stop doing all the other stuff and just I can just stream. All day long. Wouldn’t that be great. This November, I will be in Seattle with Ms. Kendra Little delivering handle hand delivering two days of the best T SQL pre conning pre connery you will ever see in your life. So you should go there. You should come to Seattle on the appropriate date and come to the pre con and get some stuff from me and Kendra be great for everyone, right? We all win. We all continue to win.
We’ll win. We’ll win. We’ll win. We’ll win and triple security in here in the next August 1815 in this year is your exp dativeness of the past three years long. Yellow, you’ll win. It’s not very simple, it’s not great for you.
of the store procedure is to allow me to inject hints and options easily without having to rerun the query that hits the index view. And I’ll show you why here. So let’s first run this store procedure with the no expand hint. Thank you, SQL prompt for making yourself known.
And let’s look at the messages tab. And in the messages tab, we’re going to see the query that hits the index view. This is why I don’t want to have to keep highlighting the whole thing. This is much more compact. But you’ll see that I have the no expand hint supplied here, just like I have in the hints parameter up here. And the execution plan for this irks me. Why?
Well, it’s a serial plan. And I do not want a serial plan. I want a parallel plan. The problem is, in this case, the no expand hint does something kind of funny. It doesn’t prevent parallelism in all cases, of course, right? That’s not my point. I’ve certainly seen the parallel queries with the no expand hint applied to them. That’s not what I’m getting at here. This is just a curious case, right? So like, so just like a weird thing. Now, if I rerun the store procedure, right?
We execute this and we say no hints and no options. You’ll notice that that was a little bit faster. And of course, we got a parallel plan. Because of that, if you go over to the messages tab, and we scroll down to where the table gets hit, we now have a blank spot where there was once a, there was once a hint supplied in there. So like I said, the no expand hint does not prevent parallelism. If I run the query with the no expand hint, and I also use the use hint enable parallel plan preference, well, SQL Server comes up with a actually, let me show you one thing first.
If I run this, oh, wait, let’s come back to the original one. This isn’t this is sort of a funny thing about it. If I run this, when we get the serial execution plan, it has a cost of 22.6722 query bucks, right? So the serial plan was not expensive enough, right? The estimated cost of the plan was did not break my cost threshold for parallelism setting, right? Which is weird, because when we run it without the no expand hint, we get a parallel plan, right? So it’s like, like, why would the no expand hint end up here, right? Like, like, did SQL Server just not like, go find some more stuff? It’s a good question. We’re going to answer that. Now, if we look at the cost of the parallel plan that we get without the no expand hint, right? This, of course, has a cost of 9.3 something query bucks. So SQL Server did find a cheaper parallel plan, right? So at some point, without the no expand hint, the optimizer did some more stuff. What more stuff did it do?
Maybe, maybe we’ll be able to answer that. But what SQL Server does is it went out and it found maybe a more expensive serial plan and then found the cheaper parallel plan and said, oh, I’m going to go with the cheaper parallel plan. So there is the exploration space there. For some reason, we just don’t hit it when we use no expand. Now, like I said, it is possible to get a parallel plan with no expand hints. I’ve seen plenty of times where it happened completely naturally, but in this case, we don’t get it. So I’m going to go here and I’m going to run it with no expand and the use hint enable parallel plan preference so that we get a parallel plan even with no expand there. And you’ll see that it is possible. It does happen for us. Sort of curious. And this is, this is the very curious thing. If we look at the cost of the, the, um, the parallel plan with that hint supplied, it is 5.2 query bucks. So that’s even cheaper than when it found the parallel plan naturally without the no expand hint. So what happened in there? Well, I was able to get some answers, not full answers. Um, if we run, so I’m going to show off some, some rocket science-y trace flags here. Uh, the first one is 8675, which will show us how many tasks the optimizer applied to a query while it was optimizing it. So this is without the no expand hint and without the no expand hint, the, if you look at the, right, we get the, get the parallel plan. Uh, we go over to the messages tab.
This is where SQL Server will show us the stuff that it did, right? So the number of tasks, which if we scroll down a little bit, we’ll eventually get to do, do, do, do. So at the end of, actually that was important. The end of search one, we had a cost of 136.73 query bucks, right? So there we go, right? That was the cost of the serial plan. And then if we keep scrolling down, we’ll have more tasks. Look at 7, 7,200 tasks. And if we keep scrolling down, we’ll have this end exploration, right? And this is where SQL Server found the parallel plan that costs 9.3, whatever query bucks, right? So that keep that number in mind, 7,239. Okay. Now, if we run this with the no expand hint and those same trace flags, um, what we’ll see is something a little bit different, right? So we get the serial plan again. If we scroll through messages, we’ll have do, do, do, do, do, do, do, 1900 end of search one cost 2,200 query bucks. So SQL Server and did fewer tasks here, right? The other one had like 7,200. This is only 1,900 that the end of search one here, remember there were two end of search ones. So SQL Server ended search one on the last one with a cost of 130 something query bucks. This one ended search one with a cost of 22 query bucks. So this is why like we just didn’t go into that additional exploration space for some reason with the no expand hint applied. There were a couple other trace flags that I tried, or rather there was one other trace flag that I did try to look through a bit to try to figure out where things were going weird in here. But the output of 8615 is not very easy to manage. You’ll notice that the scroll bar over here is, and that’s a quite a small little rectangle scrolly thing. And there is quite a bit of information in here to get through that. I just couldn’t like, I couldn’t make enough sense of this to figure out if there were like where the, um, actual change and stuff was to like find where parallelism was, uh, where either were like the part, like the plan wasn’t where the same space wasn’t explored rather. So a little bit of just an oddity thing there and a little bit of how I looked into it further. Um, that’s about it. Uh, it’s not, not, not telling you not to use index views and not telling you not to use the no expand hint, but if you ever run into this situation, maybe, maybe you’ll, maybe this is the same reason why. Um, I don’t know, I don’t know if no expand, um, in general limits the search space more than not using the no expand hint. Maybe it does. I don’t, I don’t know. I don’t have that much insight into what the, the no expand hints interactions with the optimizer, but it might be something to, it might be something interesting to delve further into, uh, if I, if time, as time allows. But anyway, thank you for watching. I hope you enjoyed yourselves.
I hope you learned something and I will see you in tomorrow’s video. I believe we’ll talk something about query correctness or something like that. I don’t know, whatever. Anyway, thank you.
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.