SQL-DBA-01 - Friday, March 24, 2017 10:44 AM
This is a part of the reason that I stopped doing any index maintenance at all on my main production server way back on the 17th of January, 2016 (more than a year ago). Rebuilds and reorgs do a very nice job of repacking indexes to their given Fill Factor size. If it's at 0 or 100, there's no room left on pages. For clustered indexes that are following the "ever increasing/immutable" rules (to name 2 of the 5 rules), it's not so bad. For Non-Clustered indexes, it's terrible. Pages are full and the indexes are not in the same order as new inserts and so right after a rebuild/reorg, the system slows down until the page splits stop.
I've never seen an index reorg cause a change in execution plans by itself. Rebuilds are a different story. They inherently rebuild stats on the index which causes a recompile of the procs that use the index. Between the fully packed index pages causing page split on inserts and a possibly bad execution plan caused by the recompile at perhaps the worst time, it can make quite a mess as inserts take a whole lot more time and the selects have to wait on all that.
--Jeff Moden
Change is inevitable... Change for the better is not.