This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
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
asked 2018-09-27 by user161709
Answer
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…”
answered 2018-09-28 by Andy Mallon