2

Cluster Columnstore Index maintenance

We are using clustered columnstore index in SqlServer 2016 for 400M rows. We check the time for sample select query - it was 2s just after index creation. After month the same query took up to 20-30 s. Total fragmentation is 96% page fullness 66%, avg row size is 20, depth 3. Reorganizing index gives 1% less fragmentation. Rebuilding is not available as we need to have all data online. We insert 1M rows daily. Any ideas?

How to get query performance similar to initial?

NataliaA
  • 31
  • 1
  • 3
  • 1
    Are you measuring the time it takes to return 400 million rows? To where? Over what network? And rendered by what? I would expect a query that returns 400 million rows to take longer as the table gets bigger (and certainly a lot longer than when it was empty), and I would expect client tools like SSMS to have increasing difficulty consuming those rows and rendering them. If your query is not returning all of the data, and you want specific help, please provide specific details - table structure (data types), the index definition, the query, the actual execution plan. – Aaron Bertrand Feb 15 '19 at 13:20
  • time were measured in SSMS just after creating the index and after a month with the same query, for both tries data were the same as the query didn't change, the problem is (I think) with index fragmentation as it was much lower before so I need help with decreasing it – NataliaA Feb 19 '19 at 07:15

1 Answers1

0

Any ideas?

Problem likely is due to delta store overhead. We encountered similar issue on SQL Server 2016 and full rebuild solves for some time. Suggestions:

  • Use partitions, perhaps on monthly or bi-weekly basis
  • Rebuild index on partition level, this can reduce maintenance duration significantly. Run rebuilds at night.
  • Wait for SQL Server 2019, MS ships ONLINE rebuild of columnstored indexes in this version

Reorganizing index gives 1% less fragmentation

Was it executed it with COMPRESS_ALL_ROW_GROUPS?

ALTER INDEX idx_cci ON table  REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON); 
ALTER INDEX idx_cci ON table  REORGANIZE;

References:

Alexander Volok
  • 5,630
  • 3
  • 17
  • 33
  • Yes, I know that rebuild Online is going to be available with 2019, but we cannot wait so long to take it to our production. We have no option to rebuild index at night because we have 24/7 availability due to 3 time zones. Building that index from scratch took 13h. Also tried with compress_all_row_groups it gives 1% more than without (so 2% less than before running reorganize). – NataliaA Feb 19 '19 at 07:19