CODEWITHSUNDEEP
sqlsql-serversql-server-2008indexing
George2
George2

Reputation: 45801

fill factor of SQL Server 2008

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

Upvotes: 3

Views: 5569

Answers (2)

Subhransu Panda
Subhransu Panda

Reputation: 77

Fill factor give information about..how much space available in a SQL page which is default 8kb.For Example.. Suppose FF=20% that means 100-20=80 % of space is filled.The default value of the Fill Factor is 100, 100-100=0% space filled

Upvotes: 0

Dave Markle
Dave Markle

Reputation: 97761

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.

A FILLFACTOR which is set too high can affect performance on insert/update operations. You will get page splits when you modify enough data. This is part of the intrinsic nature of the B-tree index structure -- it automatically balances the tree. If you want to adjust the FILLFACTOR to an explicit setting, BOL says that this only takes effect when you rebuild the index.

Upvotes: 8

Related Questions