Under what circumstances is it a good idea to re-index tables every night?

Solution 1:

If the information is rolling that indicates that there is a large portion, roughly 20%, of the total DB being inserted every day. This indicates that your stats and your indexes are 20% wrong.

That sounds like exactly the conditions you should be re-indexing for.

Solution 2:

Basically, your shrink and reindex were simply undoing each other (the shrink fragments tables to recover space, then the reindex uses extra space to defragment tables).

So since you've removed the shrink, you probably can remove the nightly reindex as well. (Still probably advisable to have a weekly full reindex, in most cases.)

But given the rolling 5-days of data, you might actually be ok with NO REINDEXES at all on those rolling tables (depending on exactly how the clustered index is configured). Here's what I mean:

Assuming you do inserts only (no updates), and the clustered index is either on the date/time, or just a perpetually increasing numeric value, the internal table arrangement might look something like:

[  Day 1  ][  Day 2  ][  Day 3  ][  Day 4  ][  Day 5  ][  Empty  ]

On day 6, you delete the data from day 1, and insert the data from day 6:

[  Empty  ][  Day 2  ][  Day 3  ][  Day 4  ][  Day 5  ][  Day 6  ]

and the same pattern continues, re-using the recently-freed space:

[  Empty  ][  Day 2  ][  Day 3  ][  Day 4  ][  Day 5  ][  Day 6  ]
[  Day 7  ][  Empty  ][  Day 3  ][  Day 4  ][  Day 5  ][  Day 6  ]
[  Day 7  ][  Day 8  ][  Empty  ][  Day 4  ][  Day 5  ][  Day 6  ]
[  Day 7  ][  Day 8  ][  Day 9  ][  Empty  ][  Day 5  ][  Day 6  ]

To know for sure if this is what is happening, I would use SQL's table fragmentation queries (dm_db_index_physical_stats) after a final reindex,then turning off the reindexes for several days. If the fragmentation numbers are very low, then this is probably what is going on. If they are high, then this probably isn't an accurate picture, and you might need to reinstate your reindexes.