Interesting SQL Server 2019 findings whilst testing sp_estimate_data_compression_savings

Reading Time: 2 minutes

In this post I want to cover some interesting SQL Server 2019 findings whilst testing sp_estimate_data_compression_savings.

Because in SQL Server 2019 the stored procedure sp_estimate_data_compression_savings has been updated.

In fact, you can now use it to estimate amount of savings used if you convert an index to be a columnstore or columnstore_archive index.

You can read more about these new changes to the stored procedure that affect Columnstore indexes in detail here.

I have been testing it in the latest CTP and it works well. I really think this will benefit a lot of people if it works well, as you can use it to highlight potential compression gains.

However, there are a few interesting findings with testing at this moment in time that I think you should be aware of.

I decided to do test it using the SQL Server 2019 on Linux installation that I setup using a local repository. You can read more about that in detail here.

In addition, I tested using the same databases I used to test the database scoped settings for index rebuilds. Which you can read about in detail here.

Results

For instance, sometimes it will give you results back for indexes even though you may suspect that you can’t convert them to clustered columnstore indexes. For example, if your databases are in SQL Server 2012 compatibility mode.

At first I thought this was going to be an issue with all the results. However, I do appear to be able to create clustered columnstore indexes on databases that are in SQL Server 2012 compatibility level.

With this in mind, I certainly will be testing this more at a later date.

Error message

Now, for some data types you do get an error when using this stored procedure. For instance, for certain column types like xml you do receive back the below error.

Msg 35343, Level 16, State 1, Line 1
The statement failed. Column ‘x’ has a data type that cannot participate in a columnstore index.

Computed columns

In addition, it will also return results back for clustered indexes that have computed columns in. However, if you try and convert these clustered indexes to columnstore clustered indexes you will receive an error.

Second run error

In addition, I have noticed in a database that’s running in SQL Server 2012 compatibility mode that you can get an interesting error.

For instance, I tried running the stored procedure twice a few times and got the below error.

Msg 6348, Level 16, State 1, Line 1
Specified collection ‘schema_65541’ cannot be created because it already exists or you do not have permission.

Final word

Remember, these interesting SQL Server 2019 findings whilst testing sp_estimate_data_compression_savings are based on the current CTP. In reality, these findings may change before the official release.

However, be careful when using the extra options in this stored procedure. In addition, I recommend always checking that the columns are supported to be converted to columnstore indexes regardless of compatibility level for now.