Rebuilding indexes vs fill factor

  • Hi

    I’m not sure if I’m posting this to the correct forum but here goes…

    One of the SQL 2005 databases that I administer has indexes that have fill factors of 100%. I have a scheduled maintenance plan to rebuild these indexes with the default fill factor every Sunday night. However, I have found that fragmentation in several of the indexes increases faster than I would like. For example, after only two days of user activity, one index has accumulated 84% logical fragmentation with 5679 physical fragments. Immediately after the index rebuild, the same index had 0% logical fragmentation and 2 physical fragments. There are several other indexes that experience similar results to a greater or lesser degree.

    The two options that I am looking at to address this issue are to either increase the frequency of the index rebuild and/or reduce the fill factor. However, I have read that reducing the fill factor can have a negative impact on read operations. As such, I am erring on the side of increasing the frequency of the index rebuild using the default fill factor (100%). I know that user access to the database can be affected while the indexes are being rebuilt. However, I can (and currently do) have the rebuild operation scheduled for an hour of the night when there is little or no user activity. Apart from being an offline operation, does anyone know of any negative effects of defragmenting indexes? Also, are there any reasons why I shouldn't schedule the index rebuild to happen every night?

    Thanks,

    David

  • as far as i read 100% fill factor can be given only to read only tables/DB's.

    if the table has regular inserts or deletes index needs to have some fillfactor left to alter the index arrangement.

    i never heard of any negative impact on reducing fill factor.

    Regards
    Durai Nagarajan

  • First off setting the fill factor too low can affect read performance because it takes more pages to hold the same amount of data meaning more pages need to be read into memory to retrieve the same amount of data.

    Consider an index with 1000 pages worth of data. After rebuilding your index with a 100% fill factor you have 1000 pages that are 100% full. If you were to rebuild that same index with a fill factor of 80% that index would now occupy 1250 pages.

    You are right to be careful to keep fragmentation to a minimum, especially on oft-used tables.

    I would recommend you move away from using the Maintenance Task to rebuild indexes and look into a solution like Ola Hallengren's Index Maintenance solution.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    The primary difference between the Maintenance Task and Ola's solution is that Ola's solution only rebuilds or reorganizes indexes that are fragmented above a specific threshold whereas the Maintenance Task rebuilds everything regardless of whether it needs it or not, making for a lot of wasted resources and unnecessary blocking on the server (edit: blocking is avoided if using Enterprise Edition and using ONLINE index rebuilds). Chances are you could schedule Ola's solution to run nightly to keep all indexes healthy nightly.

    As for lowering the fill factor on some indexes, it is justified in some cases. If you use Ola's solution I would recommend using the LogToTable option so you can analyze the indexes that are regularly being rebuilt or reorganized and lower those indexes fill factors gradually until you are comfortable with the frequency at which those indexes again become fragmented.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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