4

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.

  • 1
    Stop doing this stuff manually. 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. – Aaron Bertrand Dec 8 '14 at 13:05
  • @AaronBertrand That was the first thing I suggested. But it was shot down. "The current procedures are in place, and we don't plan to replace them any time soon". It's not manual though, it's a homebrew (part of the reason why it can't be replaced) maintenance solution. (with the default values I stated above). Sorry if I wasn't clear. – Reaces Dec 8 '14 at 13:07
  • Well, you did do it manually, and now you're going to go into your scripts and make manual changes. With pre-built tools and scripts, you just change a parameter value. And I bet they cover all kinds of things you haven't thought of (yet). – Aaron Bertrand Dec 8 '14 at 14:14
  • @AaronBertrand My point is though: Currently each new database receives the same maintenance plan (hence the not manual). And I was told quite clearly I'm not allowed to replace it by Ola's scripts. So I'm left with either respecting the status quo, or changing at least some parts. Both of which will be accompanied with further lobbying for Ola's scripts... The reason I'm asking my question here is not to make my mind up about what I should do (because my mind is already quite similar to what you suggest). I just want to make sure what I do is a net positive. To facilitate later changes. – Reaces Dec 8 '14 at 14:17
  • Hard for us to know if it will be a net positive. Well the ONLINE option is a no-brainer, as is not worrying about tables with < 1000 pages. But we have no idea what impact defrag/rebuild has on your read workload, and whether holding off until some % is better, gains you anything at all, or maybe makes some read queries worse. – Aaron Bertrand Dec 8 '14 at 14:19
3

Hard for us to know if it will be a net positive. These are pretty much no-brainers:

  • setting all operations to ONLINE, because you can
  • ignoring tables with < 1000 pages

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.

  • It's much harder if said housekeeper / gardener is still working there and deciding on whether or not they can ... Well you get the point. – Reaces Dec 8 '14 at 14:35
  • Unless you pre-paid them it's actually not hard at all. Still, you get the analogy, right? – Aaron Bertrand Dec 8 '14 at 14:36
  • I get the analogy. I'm just not knowledgeable enough to butt heads yet ;) (with the gardener) – Reaces Dec 8 '14 at 14:38
2

I assume that the database is highly transactional, to require rebuilds; and being international, accessed all the time... so IN MY OPINION leave everything as it stands, and change to ONLINE rebuilds.

Everything all the time - is wasteful; but not a train-smash. Ease into the task a check for fragmentation - setting it SLIGHTLY less aggressively - 10% fragmented then reorganise; 20% then rebuild.

So you introduce only 2 changes, evaluate and tweak again. This way, you factor in optional instead of EVERYTHING ALL THE TIME; and ONLINE so you have people able to access.

As suggested, check Ola Hallengren's work. Brilliant example of coding... inspirational.

Good luck!

0

The main differences are:

  1. OFFLINE index rebuild is faster than ONLINE rebuild.

  2. Extra disk space required during SQL Server online index rebuilds.

  3. SQL Server locks acquired with SQL Server online index rebuilds.

    This schema modification lock blocks all other concurrent access to the table, but it is only held for a very short period of time while the old index is dropped and the statistics updated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.