Index Fill factor is the percent of page you want to fill and thus percent of free space you want to have in a page. A page is 8k bytes. Depending on columns in index, index can be a few bytes. Lets take 1000 bytes in our case.
A default fill factor is 100% and thus one can fill 8 records on a page.
If a default fill factor is 80% would mean, 8000*0.8=6400 bytes to fill, which would allow probably 6 records and leave part of page free.
When inserts updates happen, if there is a free space on the page, there is no need for a page split/new page to accomodate the new record. Thus fill factor is important, since it reduces the number of page splits.
The fill factor, is important only when indexes are created, reorganized or rebuild. Since that is when, sql server keeps this percentage of space free on every page.
No comments:
Post a Comment