Post reply

Nonclustered Columnstore with only one rowgroup even with > 1 million rows

  • January 3, 2017 at 10:28 am

    #401311

    Hi, I have some real time OLTP analytics and aggregates that we perform on OLTP tables. I am testing the use of nonclustered columnstore on the scores tables. I can see the benefit of the batch operator on my aggregation queries, but noticed that my tables have only one row group even with > 1 million rows. I would expect there to be multiple row groups with at least 100,000 rows.

    I have been looking online, and the only thing I see is a quote from BOL that says if your table is less than 1 million rows, there will only be one rowstore. Is that a general number?

    This is what I see when I query sys.column_store_row_groups for the index object_id

    object_idindex_idpartition_numberrow_group_iddelta_store_hobt_idstatestate_descriptiontotal_rowsdeleted_rowssize_in_bytes

    130099504410NULL3COMPRESSED1039361010851376

    Thank you,

    Yell McGuyer
    DataYell.com

  • January 3, 2017 at 12:51 pm

    #1920469

    SQL Server will try to pack a rowgroup as full as possible, to the max of 1,048,576. The deltastore will not be converted to a rowgroup until there are at least 102,400 rows.

    As rows are inserted, new rowgroups will consist of somewhere in between the min & max depending on your batch sizes. If you are creating your CS index on a populated table, only the last rowgroup will have less than the max rows.

    The more rows in a rowgroup, the better the compression and overall performance.

    https://msdn.microsoft.com/en-us/library/gg492088.aspx

    Wes
    (A solid design is always preferable to a creative workaround)

  • January 3, 2017 at 1:04 pm

    #1920473

    Thanks for the reply and link!

    Thank you,

    Yell McGuyer
    DataYell.com

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply