Using FILLFACTOR v7.0
Over time, indexes can get fragmented with regular INSERT, UPDATE, and DELETE
usage. To help you manage your indexes, you can use the FILLFACTOR keyword when
creating the index. When you create an index with a FILLFACTOR value, you
establish "free space" within the index pages to allow for data
modifications. This exposes "holes" in the data pages where new rows
can be inserted without page splitting.
Page splitting occurs when a value is inserted on a full index page and then
approximately half the values from that page are moved to a new page to allow
room for the insert. Page splits can fragment the data in a table and result in
performance difficulties. FILLFACTOR can help alleviate these problems. By
applying a new FILLFACTOR value (0 to 100 are the valid values), you'll
redistribute the data to allow room for new inserts.
CREATE INDEX idx_1 on TableX (col1) WITH FILLFACTOR = 100
This CREATE statement will pack all of the rows as tight as possible on the
index. This value would be good for a table that is not modified or is
read-only.
CREATE INDEX idx_1 on TableX (col1) WITH FILLFACTOR = 80
This CREATE statement stores the data pages at 80 percent of capacity and
leaves 20 percent free space available for new data.
It's important to note that FILLFACTOR is not a dynamic function, meaning SQL
Server does not maintain it. If you have created an index with a FILLFACTOR of
80 and you know that the table has grown by 20 percent, it may be time to drop
and re-create the indexes with a new FILLFACTOR value.
Home Previous Next