Fixing some of the problems caused when you introduce Columnstore indexes

Reading Time: 2 minutes

In this post I will cover fixing some of the problems caused when you first introduce Columnstore indexes to a SQL Server database.

Because in my last post I talked about fixing some of the problems caused when you introduce rowstore filtered indexes.

Before we start I’d just like to point out I’m not going to reinvent the wheel here. Niko Neugebauer has a whole series dedicated to Columnstore indexes which I talked about in a previous post.

Instead, I’m going to cover some immediate DBA related issues in this post.

Deadlock creating Columnstore index

Now before anybody panics, I have only ever experienced this issue once. Because a while back I had to investigate an interesting issue.

A large table was being converted from a traditional rowstore (clustered) index to a Clustered Columnstore index. However, when I went to change it to a Clustered Columnstore index it failed with a deadlock error.

At first, I assumed it was due to something else running. I checked and couldn’t see anything. I then went to convert this back to a rowstore (traditional) clustered index. Surprisingly it worked.

But the aim was a table with Clustered Columnstore index. After a double check I ran the create index statement yet again. However, this time I added an option to set the maxdop to 4. It still failed with a deadlock error.

Reluctantly, I tried creating a Clustered Columnstore index with the Maxdop set to 1. I was reluctant because I knew it would take a while due to the amount of data. However, it worked.

Now, this was a short term solution to an unusual issue. Long term the solution either needed rearchitecting or testing on a newer version of SQL Server.

I just want to make everybody else aware of this unusual issue on the off chance they are ever unfortunate enough to encounter it.

Error due to online statement

It’s a very common error caused usually by somebody copying a rowstore “Create Index” command. The “online=on” option you use with rowstore indexes does not work with creating Columnstore indexes in SQL Server 2016.

For the record, you can use the online option with SQL Server 2019.

Unsupported Data Types

Another common issue when trying to introduce Columnstore indexes (especially when it involves Datawarehouse loads) are failures due to unsupported data types.

What I mean are data types you cannot create Columnstore indexes on. 

One alternative is to keep the table as a rowstore clustered index and try and cover the columns required by a Columnstore index. Another option is to get the invalid data types changed.

Alternatively, you may have to upgrade SQL Server if you are running an older version. For instance, certain LOB columns like varchar(max) are only supported in SQL Server 2017 or above.

You cam read more about the limitations and restrictions for Columnstore indexes here.

Final word

I hope you have enjoyed this brief overview. Feel free to comment below if you have any comments or queries.