• Lj Burrows - Sunday, June 24, 2018 10:41 PM

    Here is the best way to reorganize the SQL Server Database. Go through it:
    http://www.sqlmvp.org/how-to-rebuild-and-reorganize-index-in-sql-server/

    Like so many articles on the subject of how to maintain indexes, it falls fairly well short.  You can't just explain how to maintain indexes without explain the why and what happens behind the scenes.

    1. It doesn't explain the ramifications of what happens when you use REORGANIZE instead of REBUILD, which can be devastating depending on the index.
    2.  It doesn't explain the horrible effect it can have if you REBUILD or REORGANIZE regularly fragmented indexes that have a 100% Fill Factor.
    3.. It speaks little of the importance of physical fragmentation.,
    4.  It speaks nothing of when fragmentation actually matters and I'm not talking about 10% fragmentation... I'm speaking of why fragmentation matters and what is affected by it.  Even Books Online states that the presence of logical fragmentation is not reason enough alone to do index maintenance and why (although it's not written with the necessary urgency on the subject either).
    5.  It speaks nothing of how to determine Fill Factor, which is quintessential in proper index maintenance lest the index maintenance cause more performance problems in an ever increasing death spiral than it fixes.
    6.  It speaks nothing of what actually happens to your system if you make the mistake of doing a REBUILD ALL on a table, especially with an assigned FILL FACTOR.

    --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.

    Change is inevitable... Change for the better is not.


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