I wanted to get some opinions on the index maintenance we currently do at the company where I work. One of our production SQL 2012 clusters that consists of 4 nodes, with two instances on each, has multiple AGs which service some very heavy workloads. Some of the databases in these AGs are 2TB+.
We have a standard daily index maintenance routine that does the usual rebuild vs reorg depending on the fragmentation level, but we also only do reorgs in indexes that are over a certain size because we have seen issues with SYNC latency if these larger indexes were to be rebuilt. Once index maintenance has been performed we then update statistics etc.
This job can sometimes run up to 12 hours+ so it impacts on our critical business hours where we see peak traffic, so we really need to do something to alleviate this.
I've seen quite a bit of commentary recently where it has been suggested that index maintenance isn't always required at all, and I suspect that may be the case for us where we are doing a reorg on large indexes that are only 5% fragmented.
I guess I'd like some ideas on how I could identify indexes where the level of maintenance we do daily isn't necessarily required, other than disabling it and observing the impact, if any.