Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Clustered Columnstore Index - Multiple Trimmed Row Groups with reason of "RESIDUAL_ROW_GROUP" within a Single Partition RE: Clustered Columnstore Index - Multiple Trimmed Row Groups with reason of "RESIDUAL_ROW_GROUP" within a Single Partition

  • Hi Brad, 
    No, the query isn't trying to select specific rows; it's aggregating data with some GROUP BY logic - it's a fact table in a data warehouse with 35M rows, and the columnstore works quite nicely for our purposes.   A little more background on this specific scenario: we currently have the fact table partitioned on column A, and we're thinking of changing the partition key to a different column to accommodate a future business requirement.  I've been testing the 2 partition schemes in an isolated development environment to see how query performance is impacted.  For these tests, there is no fragmentation or anything like that - the CS index is built fresh, so no delta store and no deletes.

    One of the test results was puzzling to me - I thought I would see a performance increase with our new partition key (call this P2) due to partition elimination, but it actually performs worse than the original partition setup (call this P1).  I do see partition elimination, so that is behaving as expected.  Also, the Query plans are very similar.  However, I noticed that the LOB reads are much higher with P2 than they were with P1.  It turns out that P2 has many more segments being read than P1 did.  I looked into why that was, and noticed that P2 has a lot of segments that are trimmed in each partition.  And I'm not sure why.  I think this is the cause for the increased query time, but not sure.

    Hope that helps to clarify.  Yes, I reference Niko's blog quite a bit as I work through CS issues - his work is invaluable!