Post reply

Index Framentation

  • February 5, 2018 at 6:16 am

    #343480

    Hi,
    I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count on some tables.I set up a job to rebuild and organise these indexes using Ola's script.There is no change after the job runs thrice successfully .the avg_fragmentation is the still the same.How can i solve this?

    Thanks

  • February 5, 2018 at 6:25 am

    #1978529

    You don't - it doesn't need solving.  Indexes of less than, say, 1000 pages don't benefit from maintenance.  Indeed Ola's code may even specifically exclude them.

    John

  • February 5, 2018 at 6:26 am

    #1978530

    There is no point on a page count of 26 and I think Ola's scripts will only run when it's over 1000 pages (not 100% sure on this though). 
    Generally don't worry about it if it's below 1000 pages.

    Thanks

  • February 5, 2018 at 6:49 am

    #1978537

    Another thing to check is, what is the data type of the key column(s) of the index?

    I've got several tables with 90+% fragmentation which I don't even bother with defragmenting, because the key columns (in the clustered index, no less,) are GUIDs.

  • February 5, 2018 at 6:58 am

    #1978539

    Thank you to you all

  • February 28, 2018 at 6:45 am

    #1981446

    tmmutsetse - Monday, February 5, 2018 6:16 AM

    Hi,
    I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count on some tables.I set up a job to rebuild and organise these indexes using Ola's script.There is no change after the job runs thrice successfully .the avg_fragmentation is the still the same.How can i solve this?

    Thanks

    checking your stats will get you the most mileage

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • February 28, 2018 at 7:59 am

    #1981465

    Perry Whittle - Wednesday, February 28, 2018 6:45 AM

    tmmutsetse - Monday, February 5, 2018 6:16 AM

    Hi,
    I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count on some tables.I set up a job to rebuild and organise these indexes using Ola's script.There is no change after the job runs thrice successfully .the avg_fragmentation is the still the same.How can i solve this?

    Thanks

    checking your stats will get you the most mileage

    +1000 to 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)

  • March 8, 2018 at 1:56 am

    This was removed by the editor as SPAM

  • March 8, 2018 at 10:54 am

    #1982517

    Lj Burrows - Thursday, March 8, 2018 1:56 AM

    here is How to Reduce Fragmentation: http://www.sqlserverlogexplorer.com/how-to-find-database-fragmentation/

    That's a "good" traditional article on the subject... with absolutely no mal intent directed towards you or that author (neither of you have done the experiments I have and you're both operating on "Best Practice" recommendations) the problem is that most such articles are based on supposed "Best Practices" and the testing I've been doing for about the last 3 months prove that such traditional methods actually cause performance problems because people wait too long to defrag or have a 100% fill factor on out of sorted order indexes.  In the former, all the page splits that have been held off suddenly happen on the day before it's detected for defrag and in the later, page splits go absolutely crazy just as soon as the defrag is done.  That is, unless the table has gotten large enough for the "Reorg" portion of most code to kick in and then it doesn't do any better than not defragging at all.

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

Viewing 9 posts - 1 through 8 (of 8 total)

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