Learn T-SQL With Erik: Columnstore Modifications

Erik Darling here with Darling Data, and in today’s video we have something to talk about that not a lot of people talk about when they talk about columnstore, and that is the detrimental side effects of modifications on columnstore indexes. There are, you know, of course, when people talk about them, we talk about the wonderful performance advantages they give you for read queries. You know, if you are dealing with a columnstore index, your data is highly compressed, so you are, you remove sort of an I.O. bound potential from your query. And the use of batch mode, of course, not, which is not specific to columnstore indexes, of course, but, and, of course, you can read from rowstore indexes using batch mode as well, but it is not as efficient as when the base object is columnstore. In batch mode, of course, due to its SIMD magic, sending multiple rows to CPUs for processing can remove the I.O. bound portion. But, there are things about modification queries that can hurt your columnstore indexes, and that’s what we’re going to go and talk about today.

Anyway, down in the old video description down yonder, you’ll see all sorts of helpful links. You can hire me for consulting, buy my training, become a supporting member of the channel if you’re feeling generous across those things. But, for free, you can always ask me questions for my Office Hours episodes, or you can do me a real social solid and like, subscribe, and or tell a friend. I should probably not make any of those optional. I should probably force you to do those like boot camp calisthenics.

But, anyway, down also in the old video description is a link, and that link will allow you to download and use my absolutely free SQL Server monitoring tool. It is an open source project that I released, I think, back in February, and we’re up to version, at least of this recording, 2.5. It’s come a long way. It’s seen a lot of neat changes, a lot of great contributions, and is sort of getting its way out into the wider world where, you know, issues that I wouldn’t find locally get found and resolved pretty quickly.

So, it is reaching a nice, stable, mature point at this point, and I am really happy with the way things are looking. But, if you are in a shop that has a bunch of SQL Servers that you want to monitor, and you can’t get anyone to spend money on a monitoring tool, this is a wonderful way to get great performance insights into your SQL Servers without having to fork over $1,000 a server a year or something to some just absolute buffoon who has never actually troubleshot SQL Server performance before in their life.

So, there are some pretty big advantages to using my free monitoring tool, right? Should be, at least, I think, obvious and apparent based on the source. And if you are on the cutting edge with the robots, there are optional opt-in MCP servers you can use.

You can have your favorite robot talk directly to your monitoring data and only your monitoring data for what I think is… I mean, it may not always give the smartest advice about it. It may not give you, like, the best resolution to things.

But it is very good at digging through the monitoring data and summarizing issues and giving you, at least, a nice write-up on what it finds in there. Which is a whole lot better than just letting it run roughshod through your whole SQL Server DMV family and all that stuff. If you do not care for the robots, if you still care for human warmth and kindness, you can experience all of my human warmth and kindness out in the world.

I will be at Pass on Tour Chicago. That will be May 7th and 8th. From there, I’ll be heading over to Poland for SQL Day, May 11th and 13th.

Then I’ll be home for, I mean, just on the cusp of a month. Then heading to Data Saturday, Croatia, June 12th and 13th. And then, of course, we have, you know, the big Pass Summit.

I don’t know, is it Pass Summit or Pass on Tour West now? I don’t know exactly how they’re branding it. But that’s in Seattle, Washington, November 9th through 11th.

And with that out of the way, let’s play ball. Woo! Eddie! Yeah! All right. So, columnstore modifications. I’m going to drop and recreate this table because I forget what I’ve done so far.

But then while I do that, we’re going to talk a little bit about at least the delete portion of the world. So, when you delete data from a columnstore, it’s not like deleting data from a rowstore index, whether it be clustered or non-clustered, where it just sort of disappears. It’s a little bit more like when you delete data from a heap, kind of, like sort of kind of.

So, like with heaps, when you delete rows, the rows are actually gone, but SQL Server doesn’t deallocate the pages automatically under various conditions, which we’ve talked about in recent videos. So, if you miss those, go watch those. But what happens is SQL Server just marks rows as deleted in this, like, bitmap, and it sort of just leaves behind things in there.

But we have this marking that says the row is deleted. But if you’re doing a lot of deletes or, as we’ll talk about later, updates to a columnstore index, they can accumulate over time and they can degrade performance, especially on, you know, big tables where a columnstore is usually, you know, a little bit probably preferred for these, for, you know, some workloads. So, the delete marks the rows deleted in the bitmap, doesn’t remove them from the row groups, scans, still read deleted rows, and then filter via that bitmap.

The more deletes you have, the more sort of wasted IO and CPU your read queries will have when the number of deleted rows becomes significant. And segment elimination will not skip over those things either. So, with that table freshly loaded, let’s run this query.

And the only thing that I want to show you here, the results are entirely unimportant, but I will zoom in on them anyway, is we start, we read 10 segments, and we skip 41. All right. Now, we’re going to delete a bunch of rows.

Do-do-do-do-do. And this takes about 10 seconds. You know, we’re not deleting, like, a bajillion rows. So, like, let me go back up a little bit.

The table that I made is really just a copy of the votes table in the Stack Overflow 2013 database. So, it’s about 53, 54 million rows somewhere in there. So, if we look at how many rows we deleted.

Oh, I don’t have query plans turned on. That’s all right. So, what we can do is, let me turn on query plans despite just running a DMV query. We’ll see that we have about 1, 2, 3, 4 million, 4 and a half million, plus some others.

Right? And so, like, these row groups, even though, like, all the rows in them are deleted, we still have these compressed things hanging around in there. And if we run this query, and now we get no data back because, you know, we’re filtering on, like, the date for the date range that we just deleted everything from.

Like, we still scan that table and we still read everything. And if we look over here, we still read 10 entire segments. Right?

So, we don’t actually skip anything there. If we run, let me actually look at this first. Oops, that was control and R. So, we have this, right? So, this is our current situation.

If we run a reorg and we say compress all row groups, and why in SSMS 22 we still don’t understand that compress all row groups is valid syntax, I don’t know. That is interesting to me. I don’t know who to talk to about that, but maybe they’ll watch this video and maybe they’ll say, ah, Eric, you’re right.

We shouldn’t do that. So, now we have all these things, but they are now tombstones. Rather than, say, compress, these are tombstones.

And if we, so this was actually a good run where, like, I actually saw that happen. And now we get nothing back here. And if we look at the messages, we still have five segment reads.

And the reason we still have five segment reads is if we look at this, right? We have these tombstones in here, but we still have one, two, three, four, five row groups, segments or row groups with deleted rows in them. And SQL Server is like, well, I don’t know.

You might, you might, could be in there. Maybe there’s something that I need in there. But rebuilding the index, if I, if I were to run, there are some problems that running reorg a second time will fix. This is not one of them, right?

So, like, running reorg a second time won’t get rid of the, like, those last few things in there. But running a rebuild, fully rebuilding the columnstore will do it. You know, I’m a, I’m a pretty firm believer in the, the max top one thing for columnstore.

Um, you know, it can, it can drag stuff down pretty bad. Like, this thing has been going on for about 30 seconds now running this rebuild at max top one. But it really is the best way to get the best compression out of, uh, your columnstore indexes.

And now if we run this, we look at messages, we will see zero segment reads because we could completely rebuild stuff and got rid of all of the remaining deleted rows, right? So now nothing in there is, has a deleted row count. Unlike the last query, uh, if I just run this portion, you’ll see, of course, all the deleted rows have been removed.

So anyway, um, the other thing that can impact, um, columnstore indexes are inefficient inserts. So, uh, the Delta store, um, so like the Delta store is different from the deleted bitmap portion. The Delta store is like this clustered B tree that is uncompressed and just sort of brain leaches onto your clustered columnstore index.

Um, if you have uncompressed row groups, that’s where those live. Um, if you have a lot of open Delta row groups because you’re just accumulating inserts or, um, you’re doing a lot of updates, which is a delete and an insert for columnstore. Um, then you’ll, you could end up with, you know, uh, sort of like similar problems like we saw before where like, you know, you’re probably reading too much.

Now you’re reading also from this rowstore thing on top of the columnstore thing. The data is not compressed. So you’re just not getting maximum effort out of your columnstore indexes.

Uh, there are some things that the SQL Server will do, um, in the background to try to help you like the tuple mover. Uh, that’s a background process that runs every five minutes or so and compresses closed Delta row groups. But those row groups have to have, uh, greater than 102,400 rows in them.

Uh, you can, of course, run a reorg to fix that as well if you are impatient or, um, you know, you want something to happen that, you know, isn’t happening with, but with the tuple mover by itself. So let’s, uh, let’s run some intentionally bad inserts against our columnstore table. And what we’re going to do here is wait for this to run.

And I forget how long this takes. I should probably time these things. Didn’t I?

It’s probably a good idea. Anyway. Oh, it’s done. How lovely. So now if we look at our columnstore, we will see that we have this one row group that is open. And we have no deleted rows in it because that’s all inserts.

And if we run this query, uh, we will now see that we read 20 segments, but that’s not really the thing that I want to call out here. The thing that I want to call out is that now SQL Server registered actual logical reads associated with the columnstore. Now we can run a reorg.

And for some reason, like the, it’s either always either the first reorg or the second reorg that takes a second for things to happen. Um, but after running that and looking at the messages tab, we see that this is bumped down to zero. We do another segment read, but that’s because all those uncompressed rows that we were reading from up here are now in a new, uh, segment in the row group in the columnstore.

So where we just have another one of those to check through, um, that’s other thing that is, gets annoying is updates. So, uh, updates, uh, not only move row or not only mark rows is deleted in the bitmap, but we’ll also insert new rows into the Delta store. So columnstore indexes use this special row locator called call store lock or loc, I guess, uh, XXXX, and that XXXX will be a number for you.

Uh, but it identifies the row group and position within the, it identifies the row group and the rows position within the row group. Uh, it is a bit different from the rid that you see with heaps or clustering keys that you’re probably used to from normal tables. Um, and, uh, but this is why updates with columnstore indexes are particularly expensive.

You have to mark the row as deleted in the bitmap, and then you have to insert the new version of the row into either the Delta store or a new row group. You can’t just do in-place updates with these. They’re sort of like forwarded records.

There aren’t, they’re not forwarded records. They’re just sort of like them because there’s sort of similar overhead, uh, once they really start accumulate, once a lot of updates really start accumulating against your columnstore indexes. So let’s turn off query plans so they’re not on for this loop, and let’s, um, run an, run, run an update cursor.

Well, I mean, the, it’s not really an update cursor because you can’t cursor over a columnstore table, but you can cursor over a temp table. And you can use values from that temp table to do stuff with the columnstore table, which is an interesting sort of, um, I don’t know, something on the, on the, uh, documentation. Cause it’s like, no cursors with columnstore, but then you’re like, ah, but I’m cursoring over this other table and using stuff from that other table to update the columnstore.

But what this is doing is essentially updating, oh, what a thousand or so rows at a time. And, uh, then that’s going to give us a bunch of uncompressed row groups. And, um, so like we have this thing that’s open.

And then like, if you look through these, like look at all the deleted rows, right? Just like a small number of deleted rows in each one of these, right? So we have about 10,000 total, well, 10,872 total rows deleted.

That’s the number we started with, uh, at the start of the loop. Uh, we have, um, this thing and this is, is the new, uh, uncompressed row group that we created from the 10,872 updates we did, right?

So we like, essentially like every update, like I said, is a delete with an insert. So we do like when we, every, we, we updated 10,872 rows total, which means now we have 10,872 rows across deleted bitmaps. Then we have 10,872 rows in an open row group that is uncompressed.

That’s, that is the Delta store, right? And if we look at this thing, we will see over in the messages tab, uh, some number of logical reads. It’s not a ton here.

Like this is not like break columnstore numbers bad. This is just easy numbers to show you. So you can see what I mean that, uh, we can, of course, uh, attempt to fix it with, by compressing all row groups and then coming back and looking at this.

And what we’ll see is that, well, now we have these two tombstones. We still have a small hundred number of deleted rows in here and like, it, like things are maybe just not like where we would want them to be for this.

We would of course need to rebuild our columnstore index again, and that would bring us back to a joyful columnstore state. So just a few messages here about columnstore.

Um, you know, be careful how you, uh, a put data into them and be, if you find yourself doing a lot of deletes and updates against your columnstore, be prepared for those deletes and updates to maybe be a little less efficient than you’re used to when you are dealing with rowstore indexes.

You can solve some problems either waiting for the tuple mover to do its thing or by reorganizing with compress all row groups equals on. But there are some problems that just don’t get fixed until you rebuild your columnstore indexes from scratch, right?

So now with all that stuff out of the way, if we look at messages, we’ll have those 21 segment reads that we were used to. And we have gotten rid of the logical reads for this, which means we are not reading from the Delta store.

Again, I am not a big logical reads person. Uh, they are a secondary artifact of a slow moving query at times, but they do not like, like I’m a much more on the CPU duration front. CPU and duration was never hurt by the, the, the columnstore stuff that we did.

Uh, it’s just an easy way to show you the behavior. So anyway, uh, this is all part, uh, this is a small snippet of material from my course Learn T-SQL with Eric.

Um, it’s available for sale. Of course, uh, the link with a coupon code is down in the video description. So if this kind of stuff is what you’re into learning about in your free spare time or for your job, or maybe, I don’t know, like watching these sorts of videos on the job and you can be like, boss, I’m learning, uh, then I would highly recommend purchasing the course so that you can learn all of these things.

Anyway, that’s about it for here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in tomorrow’s video where we are going to, uh, I believe we’re going to make fun of partitioning. So that’s always a good time.

Anyway, thank you for watching.