Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Fill factor is concept that works only when creating a new index or when the index is rebuilt.
Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) without the fill factor.
Is it correct to say that the concept of fill factor is useful only when the index rebuilt?
Fill factor is concept that works only when creating a new index or when the index is rebuilt.
Fill factor is only applied when an index is created, rebuilt, or reorganized filling the page to the specified target at that time. Inserts will continue to fully fill each page.
Subsequently there will be fragmentation due to inserts/updates which will create new pages (page split) without the fill factor.
Leaving fill factor at 100% does not mean there will always be additional fragmentation or page splits. Just as setting it lower does not mean there will never be additional fragmentation or page splits. This is largely dependent on the volatility of your data.
Page splits can happen for various reasons. A few common reasons include.
A page is full and you're trying to fit additional data into an existing row. Maybe you have a varchar(100) column and originally inserted 50 characters in length, and now you want to update that value to be 100 characters in length. To make room for the additional width of the updated column, the page must be split. If most of your DML is inserts, and there are very few updates, this should not be of concern.
You're using a non-sequential key column as a clustered index, and inserts are happening across all of your data pages. In this case, each time the page targeted by the insert if full, the page must be split to fit the new row. If you're using a sequential key, this should not typically be a problem.
Is it correct to say that the concept of fill factor is useful only when the index rebuilt?
I'm not crazy about the wording here. It would be more appropriate to say it's only applied when an index is created, rebuilt, or reorganized. The times it's useful revolve around the data in each unique scenario.
In most cases, you'll want to leave fill factor at 100%. If you're running into an issue where lowering fill factor seems to help with performance, you may instead want to look into what is causing your page splits in the first place.
A good example. Years ago, I supported an application that audited all user transactions to a single table. The table received an insert at the beginning of each transaction, leaving a result_text VARCHAR(1000) column NULL during insert. This column was then updated when an unpredictable text response was received from another application within 1-3 seconds. The transactions happened 100s of times per second and the table was rather wide. So, pages filled up quickly. The table would reach 90% fragmentation within days. The fix in that instance was not to reduce fill factor.
