Clustered Columnstore Indexes – part 10 (“Compression basics”)

Continuation from the previous 9 parts, starting from https://www.nikoport.com/2013/07/05/clustered-columnstore-indexes-part-1-intro/

Now it is time to see the actual compression in action, and so for the beginnings I have downloaded Contoso BI database. This is a good enough database to start playing with Microsoft BI – there are a couple of Fact tables with over 1 million rows. :)

I decided to experiment with the FactOnlineSales table, which has over 12 Million rows. After restoring the database from the download, lets see how big the FactOnlineSales is by executing the following command:

exec sp_spaceused '[dbo].[FactOnlineSales]', true;

In my case the occupied space of this table was 372.360 KB and the index size is 1568 KB, where the table itself has an exact number of 12627608 rows.

Now lets try to create a Clustered Columnstore Index on this table by executing the following command:

create clustered columnstore index [PK_FactOnlineSales_SalesKey]
	on dbo.[FactOnlineSales]
	with ( DROP_EXISTING = ON );

which will fail, with the message that we definitely need to take care of all the foreign keys that this table has. Yes, Foreign Keys are not supported and so far it is known, they will not be supported for the v1 (SQL Server 2014). That might be a problem for some OLAP installations, while others can live with this.

Lets not be bothered by this at the moment, and let’s go and drop all the foreign keys (while before generating a script of all the foreign keys, you know, just in case ;)):

ALTER TABLE [dbo].[FactOnlineSales] drop CONSTRAINT [FK_FactOnlineSales_DimCurrency]
GO

ALTER TABLE [dbo].[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimCustomer]
GO

ALTER TABLE [dbo].[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimDate]
GO

ALTER TABLE [dbo].[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimProduct]
GO

ALTER TABLE [dbo].[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimPromotion]
GO

ALTER TABLE [dbo].[FactOnlineSales] DROP CONSTRAINT [FK_FactOnlineSales_DimStore]
GO

Lets try again – oh no! Now the SQL Server is saying something about the Constraints, but we do not have any constraints at all!!!
Wait a second, but there is a Primary Key, which is our clustered index … We need to drop it first:

ALTER TABLE [dbo].[FactOnlineSales] DROP CONSTRAINT [PK_FactOnlineSales_SalesKey]
GO

Now lets try again:

create clustered columnstore index [PK_FactOnlineSales_SalesKey]
	on dbo.[FactOnlineSales];
GO

Now we have had finally a successful statement execution. Lets see how much space does it occupy – 169.424 KB ! Wow, we just managed to half the size of our table, going from ~372 MB right to 169MB. This is really impressive! If you think that it is not, then just think in GB or TB instead of MB :)

We are still very far from over, since I would love to see how good the new type of compression will work – the COLUMNSTORE_ARCHIVE compression, as I have already explained in the first part of the series is a slightly modified version of the LZ77 compression. Lets rebuild the table using this new compression algorithm and see the results.

alter index all on dbo.[FactOnlineSales]
	rebuild with ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

What is the size that the table occupy ? 78.480 KB ! Impressive, we just halfed the size of the our Columnstore Compression! Well, lets be honest, I do not expect the results to be that high in all the cases (and a much bigger test is coming soon), but for the fist beginning it is not a bad start ;)

Lets see how much space the actual uncompressed table occupies (we know that it has had a PAGE compression after we have restored the backup). I drop the Clustered Columnstore Index by using the following command:

drop index [PK_FactOnlineSales_SalesKey] on dbo.[FactOnlineSales];
GO

Let’s check the results: 2.023.824 KB – now that is a lot of space!

At this point I would love to understand all available options and understand what can be done with different compression methods, existing in SQL Server.
So now I will recreate my traditional Clustered Index, but this time without Primary Key constraint by executing the following command with ROW compression.

create clustered index [PK_FactOnlineSales_SalesKey]
	on dbo.[FactOnlineSales] ( OnlineSalesKey asc ) with ( DATA_COMPRESSION = ROW );
GO

This time we have got a table with 890.248 KB of space occupied, which is a kind of expectable, since ROW compression is the one which uses CPU resources more lightly and compressing very lightly the content as well.

Next step for us would be to create a traditional Clustered Index using a PAGE compression:

create clustered index [PK_FactOnlineSales_SalesKey]
	on dbo.[FactOnlineSales] ( OnlineSalesKey asc ) with (DROP_EXISTING = ON, DATA_COMPRESSION = PAGE);
GO

and this time we have a different result – 351.560 KB, I assume that the difference existing with the initial information was based on the fact that the table was not rebuild completely by default.

I would like to know how the existence of the traditional Clustered Index and the actual rebuild processes will affect the Clustered Columnstore Index. As you should already know, the Clustered Columnstore Index is not reordering the data, and as it is very logical, the ordered data gets better compression in a lot of cases. Lets execute the following script recreating Clustered Columnstore Index with default and with a Archive compressions and consulting the respective sizes of the tables:

/* ReCreate Clustered Columnstore */
create clustered columnstore index [PK_FactOnlineSales_SalesKey]
	on dbo.[FactOnlineSales] with (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE);
GO

exec sp_spaceused '[dbo].[FactOnlineSales]', true;

/* ReCreate Clustered Columnstore Archive */
create clustered columnstore index [PK_FactOnlineSales_SalesKey]
	on dbo.[FactOnlineSales] with (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

exec sp_spaceused '[dbo].[FactOnlineSales]', true;

The results for my case were : 165.968 KB and 75.856 KB for default Columnstore and for Columnstore Archive compressions respectively. Now that is just a couple of percent, but if we think in GB and TB, then those numbers might start making much more sense. Scanning a table with 1 or 2 TB less might be a very different performance for a lot of queries, and so it is definitely worth trying to maintain information inside the tables with Clustered Columnstore Indexes ordered.

There is one more compression method that I would love to test before going into comparison and analysis – Nonclustered Columnstore. It would be extremely interesting in comparing it to the new Clustered Columnstore and see if they perform comparably. Lets execute the following script to drop the Clustered Columnstore Index and to create a Nonclustered Columnstore one:

— ReCreate Clustered Columnstore
create clustered columnstore index [PK_FactOnlineSales_SalesKey]
on dbo.[FactOnlineSales] with (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE);
GO

— ReCreate Clustered Columnstore Archive
create clustered columnstore index [PK_FactOnlineSales_SalesKey]
on dbo.[FactOnlineSales] with (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

Msg 1904, Level 16, State 1, Line 74
The index ‘PK_FactOnlineSales_SalesKey’ on table ‘dbo.FactOnlineSales’ has 19 columns in the key list. The maximum limit for index key column list is 16. Oops, that does not went the way we wanted. So we are facing a limitation of the Nonclustered Columnstore Index of the maximum number of columns. It is nice to think that we have Clustered Columnstore that does not have any such type of limitations. :) I will be looking into some of the comparisons between Nonclustered and Clustered Columnstore in the very next part of the series.

Let take a good look at the current results: it is quite visible that in this specific case we have managed to at least double the compression with each consecutive level of the compression. As it was expected the default Columnstore compression was much more effective then the Page compression, and the Columnstore Archive compression is definitively the sort of compression to be used when the IO is the biggest bottleneck and the CPU resources are available.

to be continued with Clustered Columnstore Indexes – part 11 (“Clustered vs Nonclustered compression basics”)