columnstore indexes: what was the magic row count for optimum columnstore compression

travisGatesMcGee@hotmail.com 21 Reputation points
2020-10-10T03:09:55.91+00:00

Remember of hearing it somewhere ... it was something like 900K ... something. Does anybody know what that row count is?

Thanks
Travis

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,515 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,726 Reputation points
    2020-10-10T12:19:02.667+00:00

    Are you thinking of the rowgroup size? A full rowgroup is 1 048 576 rows. Typically in a columnstore table, you many such rowgroups. Simply because you typically put columnstore on really big tables.

    If you have done it already, check out Nico Neugebauer's blog, http://www.nikoport.com/columnstore/. He has a lot of information about colunmstore.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. David Browne - msft 3,766 Reputation points
    2020-10-10T15:23:48.193+00:00

    The other magic number is 102,400 (100K) for bulk loading columnstores:

    If the batch size is >= 102400, the rows are directly into the compressed rowgroups. It is recommended that you choose a batch size >=102400 for efficient bulk import because you can avoid moving data rows to a delta rowgroups before the rows are eventually moved to compressed rowgroups by a background thread, Tuple mover (TM).

    Columnstore indexes - Data loading guidance

    0 comments No comments