Best Practice for Rebuilding SQL Server Indexes on Partitioned Table After Purging

Question

question

BizLight-9871 asked May 1, '22 Cathyji-msft edited May 2, '22

Best Practice for Rebuilding SQL Server Indexes on Partitioned Table After Purging

I have a partitioned table that contains approx. 16 billion rows. The table has a clustered columnstore index and one partition aligned non-clustered index. The partitions are all on the same filegroup. The table is partitioned by day. I plan to run a weekly process that truncates partitions containing data older than 90 days. The process also removes the partition (using a partition function merge range stmt). During a given weekly run, there may be approx. 500 million rows truncated across multiple partitions. Should I be rebuilding the indexes or updating stats after truncating that much data?

On SQL Server 2017

sql-server-general

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-02T06:53:50.033Z

Cathyji-msft answered May 2, '22 Cathyji-msft edited May 2, '22

Hi @BizLight-9871,

Quote the reply by Dan from this thread;

No need to rebuild indexes after the purge. A partition truncate and merge removes both data and storage from the table. The remaining partitions are unaffected by the purge operation and thus don't need rebuilding afterwards.
You probably don't need to bother update statistics for the clustered columnstore index unless you've created column statistics. The column segment value metadata, used for rowgroup elimination, is also removed by the purge.
Updating statistics on the non-clustered b-tree index after the purge is a good idea since there is only one stats blob per index. Stats will otherwise stale after the purge.

In addition, an article about the TRUNCATE TABLE WITH PARTITIONS contains the next note:

To start using the new TRUNCATE TABLE statement option, make sure that all your table indexes are aligned with the source table. If there are non-aligned indexes in your table, drop or disable these indexes, truncate the partition(s) you want then rebuild the indexes again.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-01T21:13:24.56Z

ErlandSommarskog answered May 1, '22

If you perform the purging through partition switching and no DELETE statement, I see no reason to rebuild the index, since the index structure on the other partitions are unaffected.

But updating statistics might be a good idea. At least the statistics on the partitioning column. For the other column, maybe not so much, if the distribution is about the same in every partition.

Caveat: I don't work much with partitioned tables or columnstore tables, so what I'm saying is more based on common sense. We'll see if anyone challenges what I'm saying.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question