• steve 96379 - Wednesday, April 4, 2018 7:07 AM

    This has been designed to specifically ensure that duplicate indexes never occur if you actually look at the script.  I have run this script for years with very good results that have never hurt performance.  Our web production database has it scheduled to run every day to ensure maximum performance at all times.  I have yet to see any negative impacts from running this script.  For large database in excess of 50gb I recommend restricting it to top 50 indexes found to avoid creating too many indexes.  I would challenge you to show a weakness in the script as I have yet to find one.  I recommend running this adhoc in large databases as needed rather than scheduling it.  It is not intended to fine tune a database but only to address major indexing problems with minimum hands-on work as necessary.  Fine tuning a database is still a manual process.

    First, I really appreciate the fact that anyone would share information and try to make someone else's life a bit easier.  You're definitely one of the "good guys".  Thanks for sharing.

    To Chris' point, though, the trouble is that you don't provide those warnings (the ones in your response above) anywhere in your write up or your code.  You also don't warn that the creation of indexes is a wanton duplication of data and that on a huge but narrow table, SQL Server may include enough INCLUDEs to actually duplicate such a huge table.  Neither do you include the notion of a correct FILL FACTOR, which is uber important to INSERTs, "expansive" (which are "expensive") updates, and the horrible page splitting problems that occur when you Reorg or (especially) Rebuild an index (especially with a 100% Fill Factor) during index maintenance. 

    When you post something like this, you have to remember that many people are not prone to reading the discussions that follow.  Normally, they're desperate for a solution and such warnings and issues must be clearly identified right up front and short enough so that folks don't do the "TLDR:" thing.

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