• Fill factor only makes sense if you understand how SQL Server stores records. It stores in an object called a page, which when you're done with header information is roughly 8000 bytes. If you have rows that are 50 bytes each, you can put 160 of these rows on a page. That would be 100% fill.

    By dictating fill factor, you're leaving yourself a little space for rows to be inserted into the middle of these pages, since they must be ordered by the clustered index. So if you fill this theoretical page with all of your entries 1-160, there's no room for any new records in between. Now, if you have a new record with (as a bad example but good for this discussion) a record numbered 100.5, it needs to go between records 100 and 101. This causes what's known as a page split, which moves a portion of the records on this page to a new one. This is an intensive operation, causes fragmentation, and all sorts of annoyances.

    However, if I used FILL FACTOR to put it to 90%, that puts (160 rows * .9) only 144 rows on this page. A second page is prebuilt in the proper order for 145 - 160. When I want to insert 100.5 into this page, it merely needs to shift things around a little.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA