fill factor of SQL Server 2008

Question

Hello erveyone,

I am new to SQL Server 2008 fill factor, as mentioned here in SQL Server 2008 BOL,

http://msdn.microsoft.com/en-us/library/ms177459.aspx

My 2 confusions,

1. Whether fill factor applies to index page? Or applies to both index and data page? At the beginning, seems fill factor applies only to index page -- "The fill-factor option is provided for fine-tuning index data storage and performance", but at the end it is mentioned -- "If existing rows will be updated with data that lengthens the size of the rows, use a fill factor of less than 100. The extra bytes on each page will help to minimize page splits caused by extra length in the rows.". Any comments?

2. Whether fill factor takes effect to only rebuild index/create index operation (only rebuild/create index will utilize free space reserved by fill factor), or applies to any insert/update operation (any insert/update operation will utilize free space reserved by fill factor)?

thanks in advance, George

Answer 1

FILLFACTOR applies to index pages only. But the catch here is that if you have a clustered index (and most tables do), your FILLFACTOR will essentially apply to your data as well, since your data lives inside the leaf-nodes of your clustered index. Basically, if you have a truly read-only table, set the FILLFACTOR to 100 for all of its indexes. That will get you the smallest and fastest table possible for any given index structure. Be aware that if you set FILLFACTOR to 100 for indexes on data which will be modified, you're setting yourself up for page splitting and possibly degraded performance.

FILLFACTOR applies table on every insert/update operation. You will get a page split if necessary whenever you modify the data. This is one of the beauties of the B-tree structure -- it automatically balances the tree. But if you want to adjust the FILLFACTOR to an explicit setting, BOL says that this only takes effect when you rebuild the index.