How Large Table before Using Clustered Columnstore Index

Question

At what size should we consider applying Columnstore Index on a table? Is there a SQL Server storage or design guidance?

Our Data Warehouse is 5TB total. Additionally, we have tables which are 100GB to 1TB which compose the database. Just curious if SQL Server has guidance on how large should table before a columnstore index is applied?

For Fact Tables: Eg, should we have columnstore index on table that is 300MB, 1GB, 1TB. Or should we have on all tables, and size does not matter?

For Dimension Tables: Additionally, should we have columnstore indexes on lookup tables? These are small, generally 2-50 MB

This article never stated

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-design-guidance?view=sql-server-2017

Answer 1

The article you link to gives this guidance:

Consider using a clustered columnstore index when:

Each partition has at least a million rows. Columnstore indexes have rowgroups within each partition. If the table is too small to fill a rowgroup within each partition, you won't get the benefits of columnstore compression and query performance.

The "minimum size" for columnstore isn't really any size on disk, but number of rows. Each table (or partition if the table is partitioned) should be at least 1 million rows before considering it for columnstore.

A very narrow multi-million row table might be only a few megabytes and still be an excellent candidate for columnstore. A wider table with fewer rows may take up more space on disk, and not be a good columnstore candidate.

I'd recommend revisiting the sections in that article titled "Consider using a clustered columnstore index when..." & "Don't use a clustered columnstore index when..."