Index Fragmentation

I have some very large transactional tables (20GB+). I am looking to optimiz
e
the indexes. My question relates to fill factors.
In these tables (assuming they have a clustered index that is a sequential
value (or identity field)), if I have a covering index where the first key i
s
the clustered index, will fragmentation occur in this index as new data is
added since all the values will be added to the end? If fragmentation does
not occur in this situation, I would assume that specifying a non-default
fill factor on these types of indexes be a waste of disk space and resources
.
Similarly, would another index, such as a transaction date (which generally
will only increase) peform similarly to the above example.Jason
Start with
http://www.sql-server-performance.c...agmentation.asp
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>|||I had already read that, however that article really focuses on when to
defragment the indexes, not necessarily what a proper fill factor is or with
what types of indexes a fill factor might be proper for.
"Uri Dimant" wrote:

> Jason
> Start with
> http://www.sql-server-performance.c...agmentation.asp
>
>
> "Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
> news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>
>|||Jason,

> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
I am not quite sure what you mean by that. A clustered index (CI) is
essentially a covering index on all columns. If you have a CI on a
monotonically incrementing value such as Identity then newly inserted rows
will not cause fragmentation. But if you later update any rows on columns
that are not fixed in size with a larger value than the original you can get
page splits. A little bit of fragmentation is usually not a problem. If it
is an OLTP system fragmentation is not that much of an issue. See the
article below:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
To answer your question on non-clustered indexes they are just like
clustered indexes in structure except that the leaf level is only the
column(s) in the index expression. In a CI the leaf level is the whole row.
As with a CI the NCI will not fragment if the rows are inserted in column
order such as datetime etc.
Andrew J. Kelly SQL MVP
"Jason Haase" <Jason Haase@.discussions.microsoft.com> wrote in message
news:768CE682-AD39-40D1-98F5-52B8CAD8A1A2@.microsoft.com...
>I have some very large transactional tables (20GB+). I am looking to
>optimize
> the indexes. My question relates to fill factors.
> In these tables (assuming they have a clustered index that is a sequential
> value (or identity field)), if I have a covering index where the first key
> is
> the clustered index, will fragmentation occur in this index as new data is
> added since all the values will be added to the end? If fragmentation
> does
> not occur in this situation, I would assume that specifying a non-default
> fill factor on these types of indexes be a waste of disk space and
> resources.
>
> Similarly, would another index, such as a transaction date (which
> generally
> will only increase) peform similarly to the above example.
>