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.Hi
"phancey" 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%.
>
Could you post DDL and the command(s) you have used to show the
fragmentation with their output?
> 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.
Disc fragmentation may have an effect on performance, but is not really
related to index fragmentation which could have a more critical effect. To
remove disc fragmentation you would need to stop SQL Server or detatch the
database and run the defragmenter.
> 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'
No, incrementing with smaller amounts increases the risk of disc
fragmentation, and slows down the process of inserting rows as you will have
to wait while the file expands. Planned pre-allocation of the data files is
the best option. Do you have autoshrink on? Have you shrunk the files
manually?
Is the log file on the same drive?
> 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
You don't mention the value for extent fragmentation! What you could do is
load into a staging table, then do the inserts into each table in one go
rather than on a row per row basis. Could you drop the clustered index during
the upload?
> 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).
What fragmentation value do you get after the DBREINDEX?
> There would have been hundreds of thousands of records inserted during
> the transaction.
> Thanks for any help.
>
John|||Hi
I have just seen the other post, and seen that you are using SQL 2005, look
at the function sys.dm_db_index_physical_stats, information can be obtained
from books online. You may also want to read about index fragmentation in
Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
0735621055
John|||Doh! The application (which I have no control over), despite asking
them to insert in int,char(32) asc order, was actually inserting in
int,char(32) desc order. So that would probably explain it.
Thanks anyway
On 27 Apr, 08:36, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> I have just seen the other post, and seen that you are using SQL 2005, look
> at the function sys.dm_db_index_physical_stats, information can be obtained
> from books online. You may also want to read aboutindexfragmentationin
> Inside Microsoft SQL Server 2005 :The Storage Engine by Kalen Delaney ISBN
> 0735621055
> John|||Hi
"phancey" wrote:
> Doh! The application (which I have no control over), despite asking
> them to insert in int,char(32) asc order, was actually inserting in
> int,char(32) desc order. So that would probably explain it.
> Thanks anyway
>
In which case dropping the index may be your best option.
John|||Better may be to order the index columns in desc order instead. See BOL for
the CREATE INDEX statement.
--
TheSQLGuru
President
Indicium Resources, Inc.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:0EFD52C4-245E-4551-BD6E-87E9924C29B4@.microsoft.com...
> Hi
> "phancey" wrote:
>> Doh! The application (which I have no control over), despite asking
>> them to insert in int,char(32) asc order, was actually inserting in
>> int,char(32) desc order. So that would probably explain it.
>> Thanks anyway
> In which case dropping the index may be your best option.
> John|||On 27 Apr 2007 05:34:09 -0700, phancey <deja@.2bytes.co.uk> wrote:
>Doh! The application (which I have no control over), despite asking
>them to insert in int,char(32) asc order, was actually inserting in
>int,char(32) desc order. So that would probably explain it.
>Thanks anyway
:)
J.