Fragmentation Fear

  • Tony Davis

    SSCarpal Tunnel

    Points: 4340

    Comments posted to this topic are about the item Fragmentation Fear

  • Gail Shaw

    SSC Guru

    Points: 1004446

    The design of indexes of clustered indexes to prevent fragmentation isn't entirely (or sometimes even mostly) about preventing fragmentation. It's also about preventing page splits, which can be pretty nasty operations in terms of efficiency (many times the work of a single insert) and logging.

    The resulting low average page density is also more of a concern than the fragmentation itself in many cases. Sure, a page that's half empty is only wasting 4kb of memory, but what if they entire buffer pool (say 64GB of it) is on average 60% full. That's a lot of wasted memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Jeff Moden

    SSC Guru

    Points: 995096

    I'll add to what Gail so correctly stated by saying the index defragmentation isn't as easy as you say, Tony. If you need to rebuild an index, it sometimes prevents access to the underlying tables unless you rebuild the index in an ONLINE fashion. That's not always possible either because you don't have the Enterprise Edition of SQL Server or you have blobs (VARCHAR(MAX), etc) in the table that prevent it.

    I agree that most front end code can withstand litterally years of not doing any index maintenance and still manage to get index seeks when looking up the paltry one or two rows at a time even from multiple tables. But then there's reporting, building datamarts, and other batch processes that all support what the front end can do. You also have to remember that every nonclustered index also contains the clustered index (unless the table is a HEAP) and all of that will very much affect the much desired seek/range scan need for truly effective batch processing.

    I've also seen it where fragmentation in the form of extent-splits on nonclustered indexes during inserts from the front end have caused timeouts so bad that it rendered the front end code virtually useless.

    I'd say that there's probably not enough fear about fragmentation in any of its forms.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Gail Shaw

    SSC Guru

    Points: 1004446

    One other point... Fragmentation will not affect whether a query plan uses a seek or a scan, because the optimiser does not take fragmentation into account.

    Low page density however might (needs testing).

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • des.browning

    SSC Enthusiast

    Points: 107

    This debate ran in Oracle a few years ago and the same conclusions were reached as seems to be the case in Sql Server - except in a very few clearly defined cases they are a waste of time and can actually make things worse. Great generator of overtime,though.

  • Jeff Moden

    SSC Guru

    Points: 995096

    des.browning (10/1/2012)


    This debate ran in Oracle a few years ago and the same conclusions were reached as seems to be the case in Sql Server - except in a very few clearly defined cases they are a waste of time and can actually make things worse. Great generator of overtime,though.

    Seriously? You think index and stats maintenance is a waste of time?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • des.browning

    SSC Enthusiast

    Points: 107

    stats maintenance - no. What I actually said was that the practice of regularly rebuilding Oracle indexes as they were 'unbalanced' or 'untidy' has fallen into disuse, and that it appears that the same realisation is happening to Sql Server users.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Well, if that 'realisation' happens to SQL Server DBAs there will be more optimisation work than ever for the SQL Server consultants.

    Rebuilding indexes in SQL Server is not a waste of time. SQL Server != Oracle. Very different index architectures. Very different 'best' practices.

    Sure, the fragmentation itself may not be an issue ('may not be', not 'is not'), but the low page density that results from page splits or lots of deletes can be very nasty. Nothing like a table with 20000 rows taking up over 5000 pages when it could fit in less than 2000. Hell, I've seen a table with about 2000 rows take up just under 2000 pages because of the pattern of deletes and because the indexes were never rebuilt. That's a waste of space (in memory, on disk, in backups) and of time (backing up, reading off disk, consistency checks, etc)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • des.browning

    SSC Enthusiast

    Points: 107

    You may well be right and it could be entirely necessary. I was commenting on the particular article that prompted this thread, which suggested that it wasn't necessary, and observed that this was paralleling what happened in Oracle. Of course the source article was referring to index rebuilding to reduce fragmentation, which was the aspect I was commenting on.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    I read the article. It would appear that I either didn't emphasise some things to Tony or he chose to ignore them. 😀

    p.s. If any DBA ever told me they wanted overtime so they could do index maintenance, I'd laugh. It's something that should be scheduled and automated.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • TravisDBA

    SSCoach

    Points: 15780

    Agreed, re-indexing is one of several things that should be automated on your db server from the getgo.:-D

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • des.browning

    SSC Enthusiast

    Points: 107

    'It's something that should be scheduled and automated.'

    True, if you have the window to do it - not always possible without affecting the system's usage.

  • TravisDBA

    SSCoach

    Points: 15780

    Databases are not self-healing animals, they require maintenance windows, period. I usually do weekly online re-indexing between 3-5AM on a Sunday morning, after the Sunday backups have taken place. In that way, I don't affect my U.S. systems adversely, even the 24/7 databases. For overseas databases i adjust accordingly.:-D

    "Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

  • Jeff Moden

    SSC Guru

    Points: 995096

    des.browning (10/2/2012)


    'It's something that should be scheduled and automated.'

    True, if you have the window to do it - not always possible without affecting the system's usage.

    Catch-22. Not doing it will likely affect the "system's usage", as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jo Pattyn

    SSC-Dedicated

    Points: 31315

    Postponing defragmentation till really necessary as there are some many things involved: usage, fragmentation DB level, fragmentation os level, fragmentation storage level...

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply