Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004

    Cool Unanswered: What is Fill Factor for indexes

    Hi ,

    Can u plz tell me what is fill factor and what its role in defining the indexes. It is by default 0% and can be set upto 100%, but what it makes difference if i change the percentage? Where it is exactly impacts? If u know any links then plz forward me.

    Thanks And Regards,

  2. #2
    Join Date
    Aug 2004
    fill factor specifies the amount of space of index data pages that will be filled with existing data when a index is created. (100% - fill factor procent) specifies the empty space that will be used for further data modifications.

    general hint: if you have a 'read only' table you should use a high fill factor for a index and you should lower the value for a highly accesed indexed table with dml statements like 'insert, update, delete' to avoid page splits.

    check BOL for full details on this subject.

    Cristian Babu

  3. #3
    Join Date
    Feb 2004
    Did you take a look at BOL's page about indexes?

  4. #4
    Join Date
    Apr 2004
    Kansas City, MO
    Books Online is kewl. (It's free also)

    Fill Factor
    When you create a clustered index, the data in the table is stored in the data pages of the database according to the order of the values in the indexed columns. When new rows of data are inserted into the table or the values in the indexed columns are changed, Microsoft® SQL Server™ 2000 may have to reorganize the storage of the data in the table to make room for the new row and maintain the ordered storage of the data. This also applies to nonclustered indexes. When data is added or changed, SQL Server may have to reorganize the storage of the data in the nonclustered index pages. When a new row is added to a full index page, SQL Server moves approximately half the rows to a new page to make room for the new row. This reorganization is known as a page split. Page splitting can impair performance and fragment the storage of the data in a table. For more information, see Table and Index Architecture.
    When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table's data and reduce the potential for page splits. The fill factor value is a percentage from 0 to 100 that specifies how much to fill the data pages after the index is created. A value of 100 means the pages will be full and will take the least amount of storage space. This setting should be used only when there will be no changes to the data, for example, on a read-only table. A lower value leaves more empty space on the data pages, which reduces the need to split data pages as indexes grow but requires more storage space. This setting is more appropriate when there will be changes to the data in the table.
    The fill factor option is provided for fine-tuning performance. However, the server-wide default fill factor, specified using the sp_configure system stored procedure, is the best choice in the majority of situations. 
    Note  Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can degrade database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 percent can cause database read performance to degrade by two times.
    It is useful to set the fill factor option to another value only when a new index is created on a table with existing data, and then only when future changes in that data can be accurately predicted.
    The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. Trying to maintain the extra space on the data pages would defeat the purpose of originally using the fill factor because SQL Server would have to perform page splits to maintain the percentage of free space, specified by the fill factor, on each page as data is entered. Therefore, if the data in the table is significantly modified and new data added, the empty space in the data pages can fill. In this situation, the index can be re-created and the fill factor specified again to redistribute the data.
    See Also
    Creating an Index
    fill factor Option 
    Table and Index Architecture
    ©1988-2004 Microsoft Corporation. All Rights Reserved.
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts