Read on Twitter
i'm in favor of #sqlserver index maintenance strategy that focuses on reorganization (plus ID of any stats requiring fullscan update) vs rebuild.
for a number of reasons.
for a number of reasons.
yeah, rebuilds now are "resumable". But terminating a reorg and keeping completed progress is still less resource intensive.
index rebuilds are very chatty if the database is in full recovery model; consider carefully whether to replace with reorg especially if the database is in an AG.
reorg will nearly always* touch fewer database pages than in original index footprint.
(*is there an edge case with row versioning IDs introduced after index is populated? i'm not sure.)
an index rebuild will *always* touch more database pages than in original footprint.
(*is there an edge case with row versioning IDs introduced after index is populated? i'm not sure.)
an index rebuild will *always* touch more database pages than in original footprint.
so if the system is constrained by write traffic? reorgs are gentler.
if trying to maintain a snapshot database? reorgs introduce less change.
if trying to maintain a snapshot database? reorgs introduce less change.
(i should have specified in this thread i'm referring to rowstore only; columnstore have separate considerations to consider)
* #sqlserver ROWSTORE index maintenance strategy. columnstore has separate considerations.
