Require Recommendations | Index Rebuild

  • JigarShah

    SSC Veteran

    Points: 223

    Hi Guys,

    I have created a script to rebuild indexes which is in the attachment-

    NOW - Please review the procedure and let me know if there is need for any modification or if there is anything additional to keep into consideration. Be my guest.

    P.S- Maintenance jobs are not working properly. Hence the script.

    • This topic was modified 3 days, 20 hours ago by  JigarShah.
    Attachments:
    You must be logged in to view attached files.
  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    JigarShah wrote:

    Hi Guys,

    I have created a script to rebuild indexes which is in the attachment-

    NOW - Please review the procedure and let me know if there is need for any modification or if there is anything additional to keep into consideration. Be my guest.

    P.S- Maintenance jobs are not working properly. Hence the script.

    I have not looked at your script. My suggestion would be to use ola hallengren maintenance script. His script will have everything with new features and support latest version including cloud and third party backup etc. He is dedicated to the script and fixing the issue when reported.

    I have also created my own script but we cannot update make it up to date, all the time.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Michael L John

    One Orange Chip

    Points: 25818

    I agree.  Download and run Ola Hallengren's solution.  It's far more complete, and is far more flexible, than what you have written.

    https://ola.hallengren.com/

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Jeff Moden

    SSC Guru

    Points: 995457

    Ola's code is great but it doesn't help you if you use the current "Best Practice" (it's not) of blindly rebuilding indexes after you've exceeded 30% logical fragmentation.  Even "Books Online" warns against it.

    Here's the link where the supposed "Best Practice" was born... almost no one Reads'n'Heeds the warning tip highlighted in light green just below that "recommendation".

    https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

    Here's the warning tip from that link...

     

    Tip

    These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. However, the actual values may vary from case to case. It is important that you experiment to determine the best threshold for your environment. For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. The performance benefit is less noticeable for indexes that are used primarily for seek operations. Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.

    What I've found the hard way is that a whole lot of people (including me at the time up until 18 Jan 2016) never realize  that the day after index maintenance, they suffer a whole lot of slowdowns in the form of some very significant blocking.  There are several reasons for this...

    1. REORGANIZE doesn't do what you think it does even though it's documented (but poorly explained when it comes to the impact) as to what it does and does not do to an index.  It's also not the tame little kitty that a whole lot of people make it out to be (especially when it comes to log file use) and, because of the way it operates, removes free space at the most critical time when it is needed and does almost nothing to add free space in the  critical area between the Fill Factor and 100% page fullness.  The effect delivered is that REORGANIZE "conditions" a whole lot of indexes in such a fashion as to perpetuate page splits, which is both logical and physical (page density) fragmentation at its worst.  This is why a lot of people mistakenly say that GUIDs cause a lot of fragmentation.  They can actually be setup to run for weeks and months without any page splits at all... not even good ones.  If, however, you use REORGANIZE on such indexes, you end up with perpetual pages splits, instead.  It also affects other types of indexes the same way.  I don't use it even when all I have is the "Standard Edition".
    2. The other thing is that most people don't actually analyze what's going on with their indexes.  The either leave the default Fill Factor of "0" (which is the same as 100) or they assign Fill Factors that not only may not help but may actually harm the system both for performance and space used.  They also don't realize that waiting until 30% fragmentation is actually waiting for the worst to happen insofar as page splits.  For example, for GUIDs and other evenly distributed index, it's great to lower the Fill Fact to 91, 81, of even 71 because that will provide weeks or even months of fragmentation free inserts and some updates but as soon as you have 1% logical fragmentation, you need to do a REBUILD (never REORGANIZE for this) immediately because the only way for such an index to start fragmenting is for ALL the pages are close to 100% full and on the verge of fragmenting.
    3. A lot of people also don't realize that "ExpAnsive Updates" are what is actually causing the fragmentation nor do they realize that inserts will always try to fill pages to 100%.  In only one very rare instance, INSERTs don't care about the Fill Factor and neither do UPDATES.  If you're getting fragmentation on an ever-increasing index, lowering the Fill Factor might only be wasting space and doing nothing at all to prevent fragmentation.  That normally occurs when people do INSERTs to such an index and then UPDATE the rows shortly after ("ExpAnsive" Modified_By columns are the normal but not the only reason).  Since the INSERTs went into at 100% (regardless of Fill Factor), there's no room left for "ExpAnsive Updates" and you immediately get massive fragmentation during the UPDATEs.

    There's a whole lot more to be concerned with but way too much for a forum post. The bottom line is that if you follow the current perception of the 5/30 "guidlines" actually being a "Best Practice", it's extremely likely that no matter what code you use to accomplish it, you're doing more harm than good.

    I'm writing a Stairway on the "Black Arts" of Index Maintenance with proofs for all that I claim but it's going to take a while to write it and get it out there.  I have given several presentations at various SQLSaturdays on the subject to try to begin to make people aware of the problems and that the current "Best Practices" actually aren't... they're guidelines to start with and, as it says in "Books Online", they're not actually very good and can cause more damage than doing no index maintenance other than occasional maintenance to "recover space" according to page density (page fullness).

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

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

  • Jeff Moden

    SSC Guru

    Points: 995457

    Michael L John wrote:

    I agree.  Download and run Ola Hallengren's solution.  It's far more complete, and is far more flexible, than what you have written.

    https://ola.hallengren.com/

    Ola's code is, indeed, the "Gold Standard".  However, despite the claim of it being used to "optimize" your indexes, it does not.  You need  separate processes to figure out what the index is actually being subjected to and REBUILD it both accordingly and at the right time.  Use the 5/30 guidelines will bring you a heap of trouble, especially when larger indexes are involved.

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

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

  • muthukkumaran Kaliyamoorthy

    SSCoach

    Points: 19023

    Hi Jeff,

    Waiting for Stairway. Do you have any threshold, which should be rebuild and reorganize (OR) it depends on their own system and test case.

    I could have some of VLDB with standard rebuild and reorganize thresholds, which is increasing log file growth as well.

    Muthukkumaran Kaliyamoorthy
    https://www.sqlserverblogforum.com/

  • Jeff Moden

    SSC Guru

    Points: 995457

    Read my larger post immediately before the short one you responded to.  "It Depends" although I recommend that you simply stop using REORGANIZE except for those things I cited.

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

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

  • frederico_fonseca

    SSChampion

    Points: 14297

    Jeff Moden wrote:

    Michael L John wrote:

    I agree.  Download and run Ola Hallengren's solution.  It's far more complete, and is far more flexible, than what you have written.

    https://ola.hallengren.com/

    Ola's code is, indeed, the "Gold Standard".  However, despite the claim of it being used to "optimize" your indexes, it does not.  You need  separate processes to figure out what the index is actually being subjected to and REBUILD it both accordingly and at the right time.  Use the 5/30 guidelines will bring you a heap of trouble, especially when larger indexes are involved.

    When calling Ola's script we can easily remove the reorganize out of the options - including the online as in some cases it may not be the best option either.

    and on the cases where reorganize is required (lob compaction) it is easy to issue a call just for those tables that do require it.

     

  • Jeff Moden

    SSC Guru

    Points: 995457

    frederico_fonseca wrote:

    Jeff Moden wrote:

    Michael L John wrote:

    I agree.  Download and run Ola Hallengren's solution.  It's far more complete, and is far more flexible, than what you have written.

    https://ola.hallengren.com/

    Ola's code is, indeed, the "Gold Standard".  However, despite the claim of it being used to "optimize" your indexes, it does not.  You need  separate processes to figure out what the index is actually being subjected to and REBUILD it both accordingly and at the right time.  Use the 5/30 guidelines will bring you a heap of trouble, especially when larger indexes are involved.

    When calling Ola's script we can easily remove the reorganize out of the options - including the online as in some cases it may not be the best option either.

    and on the cases where reorganize is required (lob compaction) it is easy to issue a call just for those tables that do require it.

    Understood but, like I said above, blindly rebuilding indexes and assigning or ignoring Fill Factors can (and usually does) cause many performance, log file, memory usage, backup/restore size/time, and blocking issues especially on the "Morning After".  If you don't know which type of insert/update pattern your suffering, you're frequently doing more harm that good with your index maintenance.  Folks really need to get a better grip on indexes even if they use a fantastic tool like Ola's.

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

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

  • webrunner

    SSC-Dedicated

    Points: 30053

     If you don't know which type of insert/update pattern your suffering,

    Hi Jeff,

    Can you recommend a starting point for a scalable way to find out these insert/update patterns across dozens or hundreds of SQL Servers? Or does this need to be checked one-by-one? I completely understand that the ideal approach is to understand these patterns while designing index maintenance for a server, but it seems that DBAs who manage tons of servers have come up with a set of scripts (based on DMVs or other ways of getting insert/update metrics) they can run to assess the insert/update patterns at scale, and then apply tools such as the Ola Hallengren jobs accordingly.

    Thanks for any help,

    -- webrunner

     

    • This reply was modified 9 hours, 36 minutes ago by  webrunner.

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • ScottPletcher

    SSC Guru

    Points: 98285

    but it seems that DBAs who manage tons of servers have come up with a set of scripts (based on DMVs or other ways of getting insert/update metrics) they can run to assess the insert/update patterns at scale

    Yes, exactly.

    and then apply tools such as the Ola Hallengren jobs accordingly.

    Nah, not for me.  I still use my own customized REBUILD script, so that the script "knows" more about the environment.  Besides, statistics updates are much more common than rebuilds anyway, although both are needed.

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

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

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