none
Why is the number of rows per row-group in a columnstore so low?

    Question

  • I have a partitioned clustered columnstore index in SQL 2016. Each partition is 1 month. I'm viewing a list of partitions, row groups and total rows. In partitions 1 to 141 it's nice and tidy with most row groups full with 1,048,576. Parition 142 has lots of row groups with much less than the full number of rows. I tried a rebuild using this statement:

    alter index incc_FactDailyANZTransactions ON FactDailyANZTransactions
    	REBUILD PARTITION = 142 WITH( DATA_COMPRESSION = COLUMNSTORE, MAXDOP = 1 );

    This doesn't appear to make any difference.

    Anyone got any ideas? Is it possible that the partition number in the ALTER INDEX statement is zero based but the query on sys.column_store_row_groups is 1 based?



    Nick Ryan MIS Programmer Analyst, ANZ Bank


    • Edited by Nick Ryan Friday, May 24, 2019 2:46 AM Missed out part of the code
    Friday, May 24, 2019 12:38 AM

Answers

  • Hi Nick,

    If there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup. Could you please check it out?

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Nick Ryan Thursday, May 30, 2019 1:04 AM
    Friday, May 24, 2019 7:12 AM

All replies

  • Hi Nick,

    If there is memory pressure during compression, the columnstore index might reduce the number of rows in the compressed rowgroup. Could you please check it out?

    Best Regards,
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Nick Ryan Thursday, May 30, 2019 1:04 AM
    Friday, May 24, 2019 7:12 AM
  • That's possible but I'm not sure how to check.

    Also, I think if there's memory pressure compressing one partition then I would have had the same problem building the original index. Also when it's loading new rows (2.8 to 3.2 million per run of the SSIS package via SQL Agent) then that would also leave things fragmented but both of those result in nearly all full row groups (except the last in each partition).

    I'm going to try dropping and rebuilding the entire index when I've finished loading the data.

    Another thing I find odd is that the partitioning key column is logically distributed in the row groups when I start the REBUILD but on completion it's all over the place. Again that wasn't the case when I built the original index.


    Nick Ryan MIS Programmer Analyst, ANZ Bank

    Sunday, May 26, 2019 8:16 PM
  • OK, I think I've got it.

    When I built the index, originally, I ran it as a step of an Agent Job as my desktop was being timed out and logged off in the middle of the very long running build (22 hours).

    When I ran the rebuilds, I ran them directly in SSMS.

    So it was memory pressure as Puzzle suggested and the reason for the difference was the memory available on my desktop compared to the the SQL Server.


    Nick Ryan MIS Programmer Analyst, ANZ Bank

    Thursday, May 30, 2019 1:04 AM
  • So you ran SSMS on the database server? Memory pressure on the client is irrelevant. Also since SSMS is a 32 bit app, it souldn't use much memory, even if you ran it on the server...

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, June 3, 2019 8:31 AM
    Moderator
  • Exactly, I realised that while walking home from work and then felt very foolish.

    I rebuilt the entire index from scratch and it is now well organised.


    Nick Ryan MIS Programmer Analyst, ANZ Bank

    Monday, June 3, 2019 8:22 PM