• In that case, the best you can do is start monitoring and making a plan for the indexes and stats. Check which indexes are used or not and how many times they're used with the idea of possibly consolidating or just dropping some of the indexes. Maybe run a server side trace or use extended events to monitor for queries that are hitting the table so that you can test them for possible performance problems and as possible candidates to help identify any new indexes that may be needed.. Even though the clustered index might not get fragmented, make sure that you at least update stats on it. And, of course, remember the REORGs don't update stats and that REBUILDs don't necessary have a large enough sample on the stats. If the only thing that you can touch is the indexes and the related stats, then you really need to get used to eating cat food with just the right kind of sugar on it. 😉

    Also remember that REORGs only work on the leaf level of indexes. If you want to defrag the BTREE, then you'll need to do a REBUILD. Oddly enough, I've found that REBUILDs cause some fragmentation insofar as the number of fragments go and if I really need the indexes to be absolutely air-tight, I'll follow a REBUILD with a REORG. Sounds strange but it has worked for me on several edge cases.

    As for bullet-proofing and ensuring consistent performance as the table grows, you're intentions are certainly honorable but performance is in the design of the table and the code that uses the table. Indexes can help if the code is capable of using indexes but not always. There's only so much you can do with indexes and stats. Of course, you already knew that.:-)

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