locked
Incremental Stats on Partitioned CCI table RRS feed

  • Question

  • Using SQL Server 2016

    Hello, I have a very large (5 Billion row) partitioned table utilizing a Clustered Columnstore Index. The issue I'm having is when we do ETL processing, several partitions get updates to them. Then when the maintenance happens over the weekend, it takes forever because it appears to be updating statistics for the entire table (when only a small % of partitions had any changes.)

    Our database does not have incremental stats enabled (easy enough to fix.) But I've read that CCI does not have true statistics so I'm wondering why it's taking so long to run update statistics on this table? Can I have the DBA's disable updating statistics for CCI tables? If we do need to continue to run update statistics, how can we do it only for partitions with changed data (remembering that this is a CCI table?)

    Thanks.

    Wednesday, November 6, 2019 5:34 PM

Answers

All replies

  • Hi Scott Dinnis,

    Please check if below link could help you.

    https://social.technet.microsoft.com/Forums/security/en-US/f138dd4f-5d2b-46be-b90e-d60a2360e83a/update-statistics-takes-too-long?forum=sqldatabaseengine

    Best regards,
    Cathy

    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

    Thursday, November 7, 2019 9:34 AM
  • The scripts attached to that thread are great and we have used them while creating our maintenance scripts.

    However, I guess my question boils down to -> Do we need to run update statistics on CCI tables?

    As I'm doing more and more reading, it seems like the CCI index statistics are not persisted and the column statistics are auto-generated. So would there ever be a reason why we would manually need to run update statistics for a CCI index/table?

    The long tent pole of our maintenance jobs is the update stats of the CCI tables so it would be great if we could just exclude them.

    Thanks.

    Thursday, November 14, 2019 6:56 PM