Post reply

Sql server index

  • June 8, 2020 at 4:27 pm

    Go to Answer

    #3761371

    Hi Experts ,

    I have a job which calls 100 of stored procedure. I have identified the tables which were used in the process and rebuild the fragmented index having average_fragmentation_percentage>25.

    But the performance of the query degraded after index rebuild operation .

    Can you guys tell me the possible reasons why this happened.

    Thanks & Regards

     

  • June 8, 2020 at 5:36 pm

    Answer

    #3761406

    If most of your indexes have a "0" (equivalent to a 100 Fill Factor) and you rebuilt or reorganized them and the tables don't have an "ever-increasing" key on all their indexes (usually not possible if there's more than one index on each table) AND you have either "ExpAnsive Updates" where a row gets larger or the inserts/updates are out of order, then you've slammed all pages to be as full as possible and there's no chance of the inserts or updates NOT causing page splits.  For sure, page splits are the bane of all performance, including SELECTs because the (in this case, "Bad") page splits lock up at least 3 pages (original, next, and new split page) in the index (which could be the table when it's a Clustered Index) and causes a huge amount of work to take place in the Transaction Log File.

    I'll also tell you that the "Best Practice" of reorganizing between 5 and 30% and rebuilding when more than 30% fragmentation was meant only as a starting point and it says so in BOL.  How the world ended up thinking that is was a best practice for everything is beyond me because it's actually the absolute worst thing you can do to most indexes that fragment either logically or physically (page density).

    In fact, I did a 4 year experiment on my main production box.  I went 4 years without doing any index maintenance at all except for the occasional 1/4 Terra Byte Clustered Indexes and the related Non_Clustered indexes (which contain 10-20GB each) just to recover some disk space.  The only other thing that I did was to rebuild statistics on a very regular bases.

    The reason I did that experiment was for the same reason you ask your question.  Every Monday, my production system would suffer from massive blocking (due to the page splits, which occur in a system transaction involving the page being split, the logical page after that, the newly split page, and the transaction log).  I narrowed it down to the fact that I was doing supposed "Best Practice" index maintenance on Sunday nights.

    Now, I don't recommend you simply not doing index maintenance but I will tell you that it's better to do no index maintenance (but DO continue to rebuild stats) than to do the index maintenance incorrectly.  I'll also say again that the current supposed "Best Practice" of reorging between 5 and 30% logical fragmentation and rebuilding after 30 is flatly and surely the wrong way to do index maintenance.

    There are 6 different "insert patterns" and that is nearly doubled when you take updates into consideration.  I'd point you to a link on it all but I haven't finished writing it, yet. 😀

    My recommendations at this point, though, are...

    1. If you have indexes that fragment a whole lot and also have a "0" Fill Factor, stop rebuilding or reorganizing them because the index maintenance will just make the problem worse... a LOT worse.
    2. Make sure that you rebuild statistics on a regular basis... especially if you have a lot of "ever-increasing-keyed" indexes that are frequently inserted into or indexes that suffer a lot of updates (which is about 80-90% of everyone's volume of indexes).
    3. Check for indexes that have a page density that has dropped to 70% or less.  Consider rebuilding them to 82% (I use the Fill Factor of 82 to identify the index as "Unknown but needed to recover space" once they get to 70% or less.  Reorganizing them will actually make them worse because reorganize is not capable of adding new pages and, so, will usually NOT make room for things like "ExpAnsive Updates" or out of order inserts, etc.
    4. Unless you specifically need to compact some LOBs, stop using REORGANIZE.  It doesn't work the way most people think (although it IS correctly documented but the ramifications aren't mentioned anywhere in BOL) and removes critical free space when it's need the most, which makes fragmentation worse.
    5. If a lot of your indexes are based on random GUIDs, post back and I'll show you a wicked easy method to make your indexes go for weeks and, frequently, months with no pages splits (not even supposed "good ones") and, so, absolutely zero fragmentation.

     

     

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

  • June 9, 2020 at 1:24 pm

    #3761650

    Thanks it was helpful

Viewing 3 posts - 1 through 2 (of 2 total)

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