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.
Best Answer
Hard for us to know if it will be a net positive. These are pretty much no-brainers:
You should still test those, though.
But we have no idea what impact defrag/rebuild has on your read workload, and whether holding off until some % is better, gains you nothing, or maybe even makes some read queries worse.
Other people have already perfected this type of maintenance and taken you out of the guessing game. Get Ola Hallengren's scripts or our tool, Fragmentation Manager. You can make changes like you're suggesting by simply changing parameter values. And don't forget that these people have already worked out lots of bugs you haven't come across yet and thought of things you haven't thought of yet. And when new operations are offered online in newer versions, or new options are added to different maintenance features, these tools and scripts will be updated for you. What you've done is affectionately known as re-inventing the wheel:
Refusing to move to a more automated solution because of investments in the past is kind of like keeping a terrible housekeeper or gardener because you paid them last year. Sometimes you just need to cut the cord.