Index Fragmentation and Datatype Issue

I've got an issue where by certain types of records in a particular table ar
e
becoming fragmented. The table is made up of an ID Column (identity), a
Reference Column[FK](int) , a Date Column (timedate) and an Account ID C
olumn
[FK](varchar) and some other ones.
The none-clustered index comprises these four columns in ascending order.
For the most part this is fine, records are appended to the table and
fragmentation doesn't occur. However the indexes have started to become
fragmented for certain types of records.
The records which seem to be causing the fragmentation use predominantly
numeric account codes. Has anyone experienced a problem similar to this wher
e
by the data type has caused index fragmentation?
Many thanks,
--
=============
VB .NET Developer
http://www.rocketscience.uk.comWhy would you have an index like that? Since the identity column is unique
the rest of the index is pretty much useless. And if this is a
Non-clustered index it has no bearing on the amount of fragmentation in the
table itself or the placement of new rows into the table. That is controlled
by the clustered index or if it is a Heap you have no control over where the
rows get placed. You might want to have a look at these:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag Best Practices 2000
http://www.sql-server-performance.c..._showcontig.asp
Understanding DBCC SHOWCONTIG
http://www.sqlservercentral.com/col...
illfactors.asp
Fill Factors
http://www.sql-server-performance.c...red_indexes.asp
Clustered Indexes
Andrew J. Kelly SQL MVP
"JumpingMattFlash" <JumpingMattFlash@.discussions.microsoft.com> wrote in
message news:9A4EFB0E-0B3F-4024-AC6A-ECA0FCE52D4A@.microsoft.com...
> I've got an issue where by certain types of records in a particular table
> are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
> Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this
> where
> by the data type has caused index fragmentation?
> Many thanks,
> --
> =============
> VB .NET Developer
> http://www.rocketscience.uk.com|||JumpingMattFlash a écrit :
> I've got an issue where by certain types of records in a particular table
are
> becoming fragmented. The table is made up of an ID Column (identity), a
> Reference Column[FK](int) , a Date Column (timedate) and an Account ID
Column
> [FK](varchar) and some other ones.
> The none-clustered index comprises these four columns in ascending order.
> For the most part this is fine, records are appended to the table and
> fragmentation doesn't occur. However the indexes have started to become
> fragmented for certain types of records.
> The records which seem to be causing the fragmentation use predominantly
> numeric account codes. Has anyone experienced a problem similar to this wh
ere
> by the data type has caused index fragmentation?
> Many thanks,
In fact in your case frag is probably due to UPDATE on Account ID.
When choosing VARCHAR the storage does store the data exactly at the
length of the data. Wich mean if you have choose VARCHAR(32) and
inserting a 8 char value, only 8 char will be use in the complete row.
After if you update this data to enlarge it, for instance by a data wich
is 12 char length, it is impossible to store the value of this column in
the original row. So a new storage emplacement is choose but the
complete row stay at the old place. Only the new value is store outside
and pointer are placed form the original row to say where this data has
been moved !
This is fragmentation.
So, in indexes, VARCHAR is not the good choice when updates can occur...
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************