Post reply

Index Rebuild and Fillfactor

  • April 17, 2012 at 8:23 am

    #272865

    Hi,

    I have a table with 17 indexes...

    I execute a query to check the fragmentation level and get 16 indexes with fragmentation higher than 25%.

    After executing the index rebuild command ALTER INDEX ALL ON Movimentos REBUILD WITH (FILLFACTOR = 90, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON, ONLINE = ON) I still get 16 indexes with the same fragmentation level.

    But if I lower the FILLFACTOR to 50 I only get 5 indexes with fragmentation level higher than 25 but still get 16 indexes (lowest frag. level is 11,11).

    Only when I use FILLFACTOR = 15 all indexes have no fragmentation, but this makes the indexes occupy a lot of disk space.

    What's the best commitment between FILLFACTOR and fragmentation percentage?!

    Thanks,

    Pedro


    If you need to work better, try working less...

  • April 17, 2012 at 9:19 am

    #1474806

    My first question is how much data do you have (how many pages)? If it is small, you will not be able to reduce fragmentation. ADD: Fragmentation is also irrelevant when the amount of data is small.

    Jared
    CE - Microsoft

  • April 17, 2012 at 9:29 am

    #1474815

    Thanks.. that's probably it... it has very low amount of data.

    In addiction, what's the "optimal", if possible, fillfactor value? 90?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • April 17, 2012 at 9:32 am

    #1474819

    PiMané (4/17/2012)

    Thanks.. that's probably it... it has very low amount of data.

    In addiction, what's the "optimal", if possible, fillfactor value? 90?

    Thanks,

    Pedro

    Simple answer, it depends. It depends on the activity on the database, how quickly the indexes get fragmented, how much data is in the tables.

  • April 17, 2012 at 9:38 am

    #1474825

    Lynn Pettis (4/17/2012)

    PiMané (4/17/2012)

    Thanks.. that's probably it... it has very low amount of data.

    In addiction, what's the "optimal", if possible, fillfactor value? 90?

    Thanks,

    Pedro

    Simple answer, it depends. It depends on the activity on the database, how quickly the indexes get fragmented, how much data is in the tables.

    +1 There was a poll about this recently and the default... I can't find the link.

    Jared
    CE - Microsoft

  • April 17, 2012 at 9:52 am

    #1474838

    Lynn Pettis (4/17/2012)

    PiMané (4/17/2012)

    Thanks.. that's probably it... it has very low amount of data.

    In addiction, what's the "optimal", if possible, fillfactor value? 90?

    Thanks,

    Pedro

    Simple answer, it depends. It depends on the activity on the database, how quickly the indexes get fragmented, how much data is in the tables.

    Since the database is "sleeping" during the night and assuming I have a job that reorganizes and rebuilds the indexes what should be considered the best value?

    90 for tables that don't "grow" very much and 40 or so for tables that are always getting data (like log tables)?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • April 17, 2012 at 9:56 am

    #1474841

    PiMané (4/17/2012)

    Lynn Pettis (4/17/2012)

    PiMané (4/17/2012)

    Thanks.. that's probably it... it has very low amount of data.

    In addiction, what's the "optimal", if possible, fillfactor value? 90?

    Thanks,

    Pedro

    Simple answer, it depends. It depends on the activity on the database, how quickly the indexes get fragmented, how much data is in the tables.

    Since the database is "sleeping" during the night and assuming I have a job that reorganizes and rebuilds the indexes what should be considered the best value?

    90 for tables that don't "grow" very much and 40 or so for tables that are always getting data (like log tables)?

    Thanks,

    Pedro

    It depends on the indexed column and how the data is inserted. Say you have an employee table that is primarily searched and joined by employee id. Employee id is sequential and not dependent on other employee information. This is your only indexed column. When data is inserted, it is always inserted in sequential order, so fragmentation is not likely to occur. 100% fill factor may be fine for this. You have to look at all aspects of your data and the corresponding index(es).

    Jared
    CE - Microsoft

  • April 17, 2012 at 10:01 am

    #1474851

    So I can have different fillfactor for indexes on the same table depending on the columns they use...

    Should I also consider the indexes' INCLUDE columns?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • April 17, 2012 at 10:08 am

    #1474858

    > So I can have different fillfactor for indexes on the same table depending on the columns they use...

    Yes you can

    > Should I also consider the indexes' INCLUDE columns?

    Yes you may

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

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

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