Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 356

Root Page
Intermediate
Level Pages
Leaf Pages
Figure 11-1
Leaf-level pages are connected by what is called a doubly-linked list so that SQL Server can move
forward or backward at the leaf-level without having to traverse the non-leaf levels. This benefits range
scans in particular where SQL Server seeks to the first leaf page and can then scan forward or backward
from there.
The Fill Factor
Fill factor allows you to configure how much free space to leave on each leaf-level page to allow for more
inserts without causing a page split. A page split occurs when an index record needs to be inserted into
a leaf page that's already full so the single page is split into two pages to allow the insert to happen. This
operation isn't bad on its own but if there are lots of inserts causing page splits it can affect performance.
Fill factor is specified as a percentage to fill each page when you create or rebuild an index and the free
space isn't maintained, so you need to specify it again with each index rebuild. The default is 0, which
means fill the page entirely and has the same effect as specifying 100. A fill factor of 70 would leave 30
percent free space on each page.
You should specify a fill factor (70 is a common rule of thumb) when the underlying table has a high
number of inserts and your index isn't on a sequential column. For example, in the People database
there is an index on the name column in the lastnames table. New inserts into the table will trigger an
index update which might be anywhere in the index tree because the name inserted is unlikely to be
sequential to the previous value. If the leaf page it inserts to is full, a page split occurs. If the index
were on the id columntherowwouldbeinsertedtotherightoftheindextree(becauseit'ssequen-
tial) where you're far less likely to have a full-page so you wouldn't need to use a non-default fill
factor.