Basic Question regarding Fill Factor

[sendba]

All,

Can someone let me know which is the best fill factor for the below scenarios.

1. Only inserts
2. Frequent Updates
3. High inserts and deletes NO updates
4. Insert and updates.

Thanks for the reply

Sen

 

[ptheriault]

Here is quote right from BOL.

Although a low fill factor value, other than 0, may reduce the requirement to split pages as the index grows, the index will require more storage space and can decrease read performance. 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 decrease database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 can cause database read performance to decrease by two times. Read performance is decreased because the index contains more pages, therefore increasing the disk IO operations required to retrieve the data.

Finding the right fill factor is often trial and error.

You can use the scan density from showcontig to help you determine the correct fill factor. Here are some guide lines.

    * Be consistent with the frequency of index rebuilds.

    * If the index is small then don't adjust the Fill Factor.

    * Monitor and make changes at the index level, not the table level.

    * Keep the Fill Factor values at 0, or between 75 and 100. If any Fill Factors need to be set lower than 75 you should be confident that this is what is needed. Which very possibly is the case in which you will keep seeing a low Scan Density and a low Average Page Density. Do some discovery work and find out how often the table is read from before setting lower Fill Factors.

    * If the Scan Density is at 90 percent or above, leave the Fill Factor alone. At least for the first couple of passes at changing the Fill Factors.

    * If the Scan Density is between 60 and 90 percent then bring the Fill Factor down with small decrements. Say 2 percent.

    * If the Scan Density is lower than 60 then increase the amount of the decrement. Usually I will pick a value that is halfway between the Average Page Density and the Original Fill Factor. For example, if the Original Fill Factor is at 100 (or even 0) and the Average Page Density is at 60 then I would set the Fill Factor to 80.

    * If the Average Page Density is higher than the Original Fill Factor and the Scan Density is close to 100 then raise the value of the Fill Factor. This is great, because you know you can pack the pages a little further. For example, if the Fill Factor is at 80, the Scan Density at 98, and the Average Page Density is at 88 then on average the pages filled by 8 percent just prior to the next index rebuild, and are still fairly contiguous; I would increase the Fill Factor, but not bring it higher than 92 since 8 percent growth brings me to 100 percent and another page split. I would probably bring the value to 88 and watch what happens.

    * Try to avoid drastic changes to the Fill Factor. I intend on taking more than one pass at changing the values so I can adjust a little at a time.

The information I posted can be found at the following link.

http://www.revealnet.com/newsletter-v5/0204_B.htm

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

When deciding on a fill factor to use there is some important information that you didn't include.

1, 3 and 4. When the data will be insert into the index will it be sequencial (such as an IDENTITY column), or will it be random (such as peoples names)?

2 and 4. When updates are being made will the value within the index be changed?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[sendba]

some inserts are into the index which is sequential.

When performing updates the value will change.

 

[mrdenny]

If all inserts to the index as sequantal then fill factor should be lower as old pages will never have data written to them. If the values will never be updated then a fill factor of 100 shoule be fine. 95 is there is a chance that a few records may change.

As the value in the index will change, what percentage of records will be changed between index defrage operations?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com