Fill-factor 100 for a CLUSTERED UNIQUE IDENTITY field

hi,
i have a table with IDENTITY field.
i set a CLUSTERED UNIQUE index on this field.
i wonder whether is it right to set this index's Fill-
factor to 100?
i though that if the values of this field are always auto-
incremented so there would never be a need to split places
between records, so there is no need to save space for new
records(or updated records) that might need to be placed
between any other prior inserted records.
is it true?
is it the optimal choise for this case?
thanks,
edo.
Not quite true.
You're on the right track as far as inserts are concerned, but updates are
another matter.
Because clustered indexes contain ALL columns, any updates that increase the
width of the row might cause a page split.
Therefore, whether 100% is optimal or not depends on whether there are any
updates to the table.
HTH
Regards,
Greg Linwood
SQL Server MVP
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.
|||When you create a clustered index on a table, the actual data rows move into
the leaf level of the clustered index ( and are therefore stored in the
order specified by the index key.)
When there are variable length columns in the table, updating may increase
the length of the row. When a row length increases on a page which is
completely full, it will bump one or more of the rows following it off of
the current page, and onto the next page. This causes extra work...
Completely full pages are wonderful for readers, because the number of IOs
which has to be done decreases, and the amount of memory required to keep
the data is lower as well. But If you ever insert new rows into the middle
of the table, or update rows so that they grow in lenght, page splitting can
occur... If you wish to reduce the speed of some reads, while improving the
insert/update situation, you simply reduce the fillfactor somewhat...
So by setting the fill factor you are stating performance preferences (
readers vs updaters) on the table.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1c2db01c4528a$671b1890$a601280a@.phx.gbl...
> hi,
> i have a table with IDENTITY field.
> i set a CLUSTERED UNIQUE index on this field.
> i wonder whether is it right to set this index's Fill-
> factor to 100?
> i though that if the values of this field are always auto-
> incremented so there would never be a need to split places
> between records, so there is no need to save space for new
> records(or updated records) that might need to be placed
> between any other prior inserted records.
> is it true?
> is it the optimal choise for this case?
> thanks,
> edo.