Jon Russell shares some thoughts:
Most database administrators are comfortable with the daily tasks of backup, CHECKDB, index maintenance, and statistics updates. The available guidance for those topics is extensive. Much less has been written about caring for estates that contain hundreds of databases—or a single database large enough to strain conventional maintenance windows. This post will focus on options that help when individual tables or complete databases grow beyond the point where “standard” maintenance jobs finish in a reasonable time.
Click through for Jon’s advice. My big thing is to stop worrying and learn to love the bomb fragmentation. Index fragmentation was very important back in the days of spinning disk, where accessing pages took much longer if you needed to move the disk head. With NVMe storage, all-flash arrays, and even more prosaic solid state drives, random access is typically at least as fast as sequential access, so there’s no real benefit to having pages of data be contiguous.