I Still Don’t Like Index Rebuilds
(Except columnstore)
Thanks for reading!
Video Summary
In this video, I dive into some of the new features in SQL Server CTP 2.4, specifically focusing on a feature called “elevate online” within database scoped configurations. I explain how this setting can potentially change the behavior of index rebuild operations, allowing them to run online even if they are not explicitly flagged as such. By demonstrating with an example, I show that while this might seem like a performance improvement in certain scenarios, it actually results in longer rebuild times for me—three times longer in my case. The video also touches on the confusing nature of some of the new settings and their inconsistent use of data typing, which can lead to confusion among users.
Full Transcript
Hello, Erik Darling here with Erik Darling Data, here to talk about another goodie in CTP 2.4 that caught my eye and I think is at least nominally cool. We’ll have to wait and see what you think of it. You’re free to have your own opinion. You don’t have to think it’s cool because I think it’s cool. I think a lot of stuff is cool that many other people would think is decidedly uncool. Now, first thing we have to make sure of is that our data boss is in compat level 150 and we can double check that by looking at sys.databases and hitting the old F5-er there and we’ll zoom in and we’ll see that stack overflow is in compat level 150. That is sweet because that’s exactly what I told it to do. It’s amazing how that works. Now, I want to show you some new stuff in sys.database scoped configurations. Not the least of which is Microsoft’s complete inability to respect data typing. We have some ones and some zeros and some offs. Make up your mind. This is why people get confused. Off. One, zero, off. What’s on? Is it three or is it on? Or is it one? I don’t know.
But there’s some stuff in here. If you notice the wording next to those offs, which were so haphazardly added, we’ll see a couple things. We’ll see some more words. Elevate online and elevate resumable. Now, I haven’t messed with elevate resumable yet, but I would assume that it’s pretty close to what elevate online does. There’s some other new stuff in there that I’m not going to get into because that’s not the point of this video.
But there’s this verbose truncation warning down there that I’m fairly excited about. And I’m even more excited because it has a one next to it, not an on or an off. So I’m pretty excited about that.
So let’s look at what elevate online does. Now, I’ve got an index on the post table and I’ve already created it, so I don’t have to do that. That’s going to throw an error. Good. Now, the way to turn on this elevate online thing is by saying alter database scoped configuration. Set elevate online equals when supported.
Not on or off. When supported. God almighty. Anyway, what this means is, so in the old days, if you said alter index whatever on votes rebuild. I’m going to make sure that’s turned off.
So in the old days when you did alter index whatever on votes rebuild, the default behavior was for it to be an offline operation. So if I say rebuild and I come over here and I say select count and I come over here and I run sp who is active, I’m going to see that my select is stuck behind that index rebuild. It’s blocked.
So that the rebuild of the index that the select count is trying to read from is blocked by the rebuild of that index. So I have to wait for this index to finish rebuilding in order for the count to finish. Thankfully, it does. And the index rebuild takes nine seconds.
You can see very clearly there. Nine seconds. Very nice. Right. Nine seconds. Not bad. Not bad for an index rebuild. And when we come over here, we can see that this ran for about eight seconds. So I didn’t start this exactly when the index rebuild started, but it was a little bit after. So we had to wait for that rebuild to stop.
And then when it stopped, it finished immediately. Just so you can see, we did read from ix whatever in the query plan. So we were trying to read from the index that was being rebuilt. And if I just hit F5 on this, this will finish quickly. This finishes in under a second when it’s not blocked.
So now let’s go look at what happens when we set this database scoped configuration to when supported. So I’m going to run that. And that’s going to take effect. And I’m going to run this rebuild. And what’s going to happen is rebuild and this.
And this will finish, well, not immediately, but really quickly. So this finished in two seconds. And if we look over here, of course, nothing’s going to be blocked. But this index rebuild is still going to be going. So the query finished immediately.
But now I’ve got an index rebuild. Well, it used to finish in nine seconds. Now it’s at 23 seconds. And well, that took 26 seconds. So that’s about three times as long to do the index rebuild online as it is to do it offline. Now this has been a thing in SQL Server, I mean, for as long as there have been those.
And I think index rebuilds were a mistake. I’m going to go ahead and say that. But for as long as you’ve been able to rebuild indexes online or offline, you have had the situation where the index rebuild offline was a lot faster than the index rebuild online. So that’s still true.
So what this means is that if you’re on a version of SQL Server, like, say, Enterprise Edition, which magically makes all your CPUs worth four and a half times as much money, you can run index rebuilds and other operations online. You can’t do that right now in standard edition. I don’t know if that’s coming to standard edition or if it’s never going to happen.
But who knows? Who knows? I don’t know. To me, it’s not a performance feature because it takes three times as long to rebuild an index, at least in this case. It could be longer for you or other things. Crazy to think about that. But anyway, yeah.
So that’s what index elevate online does, is when you have an operation that is eligible to be run online, elevate online will elevate it to online unless you specifically say with online equals off. So let’s go ahead and do that. Let’s do with online equals off here.
And we’ll hit and we’ll do that. And now even with this on, so I’m going to make sure that’s extra turned on, and I’m going to run my rebuild with online equals off specifically, and then run my select query, and I am going to be el blockadood all over again. Kaboom, right?
Sad face. Such sad faces. So if you specifically say build this thing offline, then SQL Server will respect that. But if you run it with no, with sort of an agnostic point of view, if you just say rebuild, and you have elevate online equals when supported, and you’re on, is it version or edition? One of those.
One of those things. Edition, probably? Edition of SQL Server that supports online index operations, you can have your index rebuilds take three times as long, too. This doesn’t change my mind about rebuilding indexes. I still think you should do that as rarely as humanly possible. I would only do it if you need to change something about the index or if you delete a whole bunch of data.
But that’s a story for another time. Anyway, thanks for watching. I hope you learned something vaguely interesting about elevate online when supported, not on or off. Oh, look.
Now it’s when supported. Good. Good. Oh, you know what? I bet that there’s tri-value. I bet it’s because of the tri-value logic. I bet because there’s fail unsupported is another option, too. So let’s just, for completeness, let’s copy that and let’s just say fail supported here. And let’s run that.
And let’s go look. Yeah, now it’s fail unsupported. So that’s good. It’s good to know that. But if you’re the kind of person who writes DMV queries, this is going to drive you out of your gut for damped mind. Anyway, thank you for watching.
I hope you learned something. And I will see you next time, probably. As long as these DMV queries don’t kill me. Thanks for watching.
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.