I am looking for an opinion, which I know is usually frowned upon, so my apologies in advance.
I'm currently in an environment where every instance is SQL Server enterprise edition. The maintenance tasks are being performed by a custom plan. This plan by default does an offline rebuild during the night.
There are no nightly maintenance windows, and as the environment is multinational the "nightly" part of the maintenance doesn't always mean the lowest activity point (however it does in 95% of the cases). And there are a lot of nightly jobs that are currently running slow due partly to aggressive indexing maintenance. (A reporting query that got stuck waiting for the index rebuild of a 300GB table with customer data led me to this route)
Can I assume that the following will have mostly positive impact:
- Add a minimum fragmentation value of 10% for reorganize (currently done every night regardless of %)
- Changing minimum rebuild requirements to 30% (currently at 5%)
- Changing default behavior to ONLINE
- Changing minimum page count to 1000 (currently at 50)
Mainly what I'm asking here is, am I correct in assuming the current maintenance schedule is too aggressive. And that the above 4 changes will have a net positive effect.
I'm mostly worried about the reorganize everything every night for everything... That seems excessive.