Blog

If you’ve got a regular rowstore table and you need to modify thousands of rows, you can use the fast ordered delete technique to delete rows in batches without hitting the lock escalation threshold. That’s great for rowstore indexes, but…columnstore indexes are different.

To demo this technique, I’m going to use the setup from my Fundamentals of Columnstore class:

• An 4-core, 30GB RAM VM with the data & log files on ephemeral (fast) SSD
• SQL Server 2019 Cumulative Update 11
• The Stack Overflow public database as of 2018-06 (about 180GB)
• The dbo.Votes table – which has 151M rows

I’ll create a new copy of the Votes table with a clustered columnstore index:

Let’s say we’ve decided to no longer use VoteTypeId 12, Spam, and we need to delete those 29,852 rows.

The bad way: plain ol’ delete

I’m doing this inside a transaction only so we can slow it down to see the locks that are held open:

The locks output from sp_WhoIsActive @get_locks = 1 looks long, but only because there are so many rowgroups involved. I’ve trimmed it here to just show a few rowgroups:

The important lock to be aware of is the HOBT X: that’s an exclusive lock on the table. That’s the part that I need to avoid: in high concurrency situations, it may not be easy to get that kind of a lock on the table.

The good way: Fast Ordered Deletes

After rolling back that transaction, let’s try again with the fast ordered deletes technique. This time around, I’m using a CTE rather than the view technique I used in that last blog post:

This time, I don’t even have to trim sp_WhoIsActive’s output. It’s way shorter because there are less rowgroups involved:

Note these lines in particular:

Awww yeah: we only have intent-exclusive locks on the object, and the exclusive locks only involve 1,000 of the keys. Yay! We’ve avoided table-level locking, so now we get better concurrency on the object.

But…it doesn’t really work.

The real test of the fast ordered delete technique is the ability to run queries on the other 150,783,380 rows that we’re not deleting. (Remember, we’re only deleting 1,000 rows.) And here’s the bummer: all of these queries are blocked:

I can get single rows if I specify the Id, and the row isn’t in a locked rowgroup:

But if I specify a row in a locked filegroup, that row is locked – even if the VoteTypeId isn’t 12. This is blocked:

I can prove that it isn’t VoteTypeId by running it with NOLOCK:

Columnstore lock escalation gets ugly.

As far as our users are concerned, even with just 1,000 rows involved in a transaction, the entire table is practically locked for most kinds of queries. Queries can only succeed if SQL Server can do rowgroup elimination to bypass the locked rowgroups altogether – but even that is practically impossible because even the simplest queries, like top 10 by Id ascending, won’t eliminate rowgroups here.

This is one of the many reasons columnstore isn’t really a good fit for most transactional workloads. Don’t get me wrong – I looove columnstore indexes – but if your workloads involve concurrency, and even more so if they involve modifying thousands of rows at a time, you’re going to need to do special table design like partitioning and sliding window loads in order to get the performance you’re already used to with rowstore indexes.

For more learning, check out:

• Locking in Columnstore by Niko Neugebauer
• Clustered Columnstore Isolation Levels by Niko Neugebauer
• How Snapshot Isolation Slows Down Columnstore by 8x by sql.sasquatch – because if you think you’re going to work around this locking limitation with different isolation levels or offloading reads to a readable replica, you’re in for another big surprise.
• And of course, my Fundamentals of Columnstore class.

Last week, I gave you a 1-week, $1 deadline, and 777 of you took me up on it. That’s awesome! I love having fun with stuff like this.

Let’s do it again! You’ve got 1 week to learn the Fundamentals of Columnstore for $1, and sales close Saturday. Here’s the abstract:

Your report queries are too slow.

You’ve tried throwing some hardware at it: your production SQL Server has 12 CPU cores or more, 128GB RAM, and SQL Server 2016 or newer. It’s still not enough to handle your growing data. It’s already up over 250GB, and they’re not letting you purge old data.

Will columnstore indexes help? To find out, you’ll learn:

• How columnstore data is stored, and how that impacts your architecture choices
• How to do a hands-on self-assessment of your workloads, data, and server to figure out whether columnstore indexes make sense for you
• Why partitioning makes so much sense for columnstore indexes
• How to do a proof-of-concept implementation with initial creation, querying, ongoing loads, and index maintenance

Normally, you’d have to pay $89 to access this course for a year. However, I know how it goes: you buy the course, it sits in your account, and you don’t actually finish it because you don’t have the time. Well, not today, bucko: you can buy the course for $1, but you only have access to it for 1 week, so you better move it or lose it.

You don’t have to follow along with the demos in this course, but if you want to, you’re going to need a beefy SQL Server: 4 cores, 30GB RAM minimum, and the ~200GB 2018-06 Stack Overflow database specially prepped for my Mastering training classes. Here’s how to set up your machine.

Oh and I don’t know why I’m even mentioning this, but it just so happens that if you do that setup, you’ll be ready to go for my Mastering Index Tuning class. The next live one of those is July 19-21st. There’s probably no reason that I’m mentioning that, right? It’s just a coincidence that if you’ve been following along with the $1 sale, you’ve done all of the prerequisites for that class. It’s probably nothing. Disregard. You can just close this and go on with your day.

Update: sale’s over! Congrats to the hundreds of folks who moved quickly and got in on the deal.