Affect of FillFactor on Index

I am trying to understand how the fillfactor might be affecting the clusered
index.
The primary key (nonclustered) on the table is a composite index of two (int
data type) fields, where one field is an identity seed incrementing by one,
and the other field being more constant.
The non-unique clustered index (that seems to fragment quite often) is a
composite index of four columns:
lname varchar(35)
fname varchar(35)
mname varchar(35)
rptype char(12)
This index has a fillfactor of zero.
The row size on the table is about 900 bytes.
The number of reads versus writes is about 50:1 under normal activity, but
sometimes (once every two weeks or so) this table is loaded with
approximately 200-500 new records at one time.
With a row size of 900 bytes, I am getting about 8 rows a page.
I am trying to wrap by brain around what affect updates and inserts have.
Please help explain.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1cbrichards via droptable.com wrote:
> I am trying to understand how the fillfactor might be affecting the cluser
ed
> index.
> The primary key (nonclustered) on the table is a composite index of two (i
nt
> data type) fields, where one field is an identity seed incrementing by one
,
> and the other field being more constant.
> The non-unique clustered index (that seems to fragment quite often) is a
> composite index of four columns:
> lname varchar(35)
> fname varchar(35)
> mname varchar(35)
> rptype char(12)
> This index has a fillfactor of zero.
> The row size on the table is about 900 bytes.
> The number of reads versus writes is about 50:1 under normal activity, but
> sometimes (once every two weeks or so) this table is loaded with
> approximately 200-500 new records at one time.
> With a row size of 900 bytes, I am getting about 8 rows a page.
> I am trying to wrap by brain around what affect updates and inserts have.
> Please help explain.
>
With a fill rate of 100%, clustered on lname (assuming this is "last
name"), you're going to see page splits (i.e. fragmentation) any time
you insert a new last name between two existing ones. Example:
LNAME: Jenkins
LNAME: Jones
These are stored on a data page, that page is 100% full. You insert a
new record:
LNAME: Johnson
Due to the clustered index, this new record belongs between the two
existing ones. However, since the data page containing those records is
full, it has to be split to make room for the new record.
Does that help?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks Tracy...that makes sense.
When it comes to updates, at 900 bytes per row (and let's say 8 rows per pag
e)
, that would leave approximately 800 bytes free per page (before any page
splits factor in) for updates, correct?
Tracy McKibben wrote:
>[quoted text clipped - 20 lines]
>With a fill rate of 100%, clustered on lname (assuming this is "last
>name"), you're going to see page splits (i.e. fragmentation) any time
>you insert a new last name between two existing ones. Example:
>LNAME: Jenkins
>LNAME: Jones
>These are stored on a data page, that page is 100% full. You insert a
>new record:
>LNAME: Johnson
>Due to the clustered index, this new record belongs between the two
>existing ones. However, since the data page containing those records is
>full, it has to be split to make room for the new record.
>Does that help?
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> Thanks Tracy...that makes sense.
> When it comes to updates, at 900 bytes per row (and let's say 8 rows per p
age)
> , that would leave approximately 800 bytes free per page (before any page
> splits factor in) for updates, correct?
Sounds right, I think...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||So, if I have 800 bytes free per page (based upon 900 and some bytes per row
),
reducing the fillfactor to say, 80%, would not be that beneficial in reducin
g
page splits due to updates, correct?
Tracy McKibben wrote:
>Sounds right, I think...
>
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1|||cbrichards via droptable.com wrote:
> So, if I have 800 bytes free per page (based upon 900 and some bytes per r
ow),
> reducing the fillfactor to say, 80%, would not be that beneficial in reduc
ing
> page splits due to updates, correct?
>
Probably not. You'll reduce the frequency of the page splits, but you
will still eventually encounter them. Your options are to monitor
fragmentation, reindexing when necessary, or consider clustering on
different keys that aren't so prone to splitting.
I have a script that you might find useful for handling the
fragmentation. I'm currently rebuilding my web site, and don't have the
script online, but you can pull it from Google's cache by searching for
"www.realsqlguy.com/twiki/bin/view/RealSQLGuy/DefragIndexesAsNeeded".
Tracy McKibben
MCDBA
http://www.realsqlguy.com