Index fragmentation

I've got 99% fragmentation on a clustered index of a table with 29699
rows (there have been just 29699 inserts, no updates, no deletes).
There are 8 columns on the table with an int and a char(32) being the
unique clustered index - in that order - and I'm pretty sure that the
rows are inserted in int,char(32) order. Average row size is 852,
there are 3769 pages with average fullness being 83%.
The database file was created as 3.5GB and has 51% space free. The
data file itself is only fragmented into 3 pieces according to Disk
Fragmenter.
I don't really understand why it has fragmented so much. All index
rows should be appended because they are inserted in key order, there
is plenty of space in the file - would it have been better to have
made the file smaller and done AutoGrow by 1MB'
The table also has a non-clustered index, which is hardly fragmented
at all.
The insert transaction consisted of many inserts on many different
tables, all with similar indexes, all inserted in key value order.
There are maybe 100 tables - inserts take place across the 100 tables,
then another group of inserts take place against all the tables. But
for any specific table any insert will be in key value order and
should therefore be appended (which seems to be supported by the page
fullness being 83%). But how can I avoid the logical fragmentation? I
know I can use DBCC DBREINDEX but this is happening so quickly in the
system that it is not reasonable to have to do it this soon (and it's
actually happening three quarters of the way through a data load).
There would have been hundreds of thousands of records inserted during
the transaction.
Thanks for any help.On Apr 26, 10:05 pm, phancey <d...@.2bytes.co.uk> wrote:
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
actually the database file was created without a size, then changed to
500MB, then changed again to 3.5GB, all before the majority of the
data was written to the tables (and certainly before any data was
written to this particular table).|||Can you give the exact statement you are executing to get this information,
and the exact, complete output you receive? Perhaps (hopefully) you are
misinterpreting the data.
TheSQLGuru
President
Indicium Resources, Inc.
"phancey" <deja@.2bytes.co.uk> wrote in message
news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
> I've got 99% fragmentation on a clustered index of a table with 29699
> rows (there have been just 29699 inserts, no updates, no deletes).
> There are 8 columns on the table with an int and a char(32) being the
> unique clustered index - in that order - and I'm pretty sure that the
> rows are inserted in int,char(32) order. Average row size is 852,
> there are 3769 pages with average fullness being 83%.
> The database file was created as 3.5GB and has 51% space free. The
> data file itself is only fragmented into 3 pieces according to Disk
> Fragmenter.
> I don't really understand why it has fragmented so much. All index
> rows should be appended because they are inserted in key order, there
> is plenty of space in the file - would it have been better to have
> made the file smaller and done AutoGrow by 1MB'
> The table also has a non-clustered index, which is hardly fragmented
> at all.
> The insert transaction consisted of many inserts on many different
> tables, all with similar indexes, all inserted in key value order.
> There are maybe 100 tables - inserts take place across the 100 tables,
> then another group of inserts take place against all the tables. But
> for any specific table any insert will be in key value order and
> should therefore be appended (which seems to be supported by the page
> fullness being 83%). But how can I avoid the logical fragmentation? I
> know I can use DBCC DBREINDEX but this is happening so quickly in the
> system that it is not reasonable to have to do it this soon (and it's
> actually happening three quarters of the way through a data load).
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>|||Using SQL 2005, I view the properties of the index, then
"Fragmentation".
Also, my data load (which does a check on existing records before
inserting) grinds to a halt presumably because of this query. I have
managed to run the data load a few times without any problem but
unfortunately did not make enough notes on the exact setup because I
did not think there was going to be a problem (I'm pretty sure I did
check fragmentation of the indexes though and am pretty sure that
fragmentation was less than 10%). Now I cannot recreate that fast load
and am at my wits end to figure out what is different.
The database file sizes again are got from the properties page of the
database in Management Studio.
thanks
On Apr 26, 11:57 pm, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> Can you give the exact statement you are executing to get this information
,
> and the exact, complete output you receive? Perhaps (hopefully) you are
> misinterpreting the data.
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "phancey" <d...@.2bytes.co.uk> wrote in message
> news:1177621507.322821.214250@.o40g2000prh.googlegroups.com...
>
>
>
>
>
>
>