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

Question

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

I wouldn't imagine you would need to, as the NC index itself is aligned to the partitions, therefore any dropped data is removed along the same lines. A statistics update might be prudent. For a non-aligned index maybe a periodic index rebuild, but not every night. Don't forget to leave an empty partition at each end always. — Charlieface
– Charlieface, Commented May 1, 2022 at 19:59

Dan Guzman · Accepted Answer · 2022-05-01 20:10:23Z

2

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.

answered May 1, 2022 at 20:10

Dan Guzman

46.8k3 gold badges52 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlieface Over a year ago

Does it make a difference if there are non-aligned indexes? (not relevant in this particular case)

Dan Guzman Over a year ago

@Charlieface, one cannot use partition-level truncate with a non-aligned index so you are right it's not relevant here. A DELETE would be required for the purge, in which case a reorg and stats update (or rebuild) would be appropriate.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related