Hi,
there is anything to do to improve the DBCC indexdefrag command?
currently I defrag my indexes after every datawarehouse load.
(only if I have 10% of fragmentation or more)
but the process is long, this takes 35minutes.
I'm using 4 SCSI disks in Raid 5
most of my indexes are unique clustered indexes.
thanks
jerome.
Danny - 16 Sep 2005 13:16 GMT
There's the usual answer around IO performance. Avoid Raid 5, separarte
your data from logs, and separate your nonclustered indexes. One thing you
might test is on any table that needs the clustered index defraged, drop the
nonclustered indexes, defrag, then rebuild the nonclustered indexes
> Hi,
>
[quoted text clipped - 10 lines]
>
> jerome.
Paul S Randal [MS] - 17 Sep 2005 00:16 GMT
> One thing you might test is on any table that needs the clustered index
> defraged, drop the nonclustered indexes, defrag, then rebuild the
> nonclustered indexes
That won't make any difference - defragging a clustered index has no effect
on non-clustered indexes as the cluster key doesn't change.

Signature
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
> There's the usual answer around IO performance. Avoid Raid 5, separarte
>> Hi,
[quoted text clipped - 11 lines]
>>
>> jerome.
Andrew J. Kelly - 16 Sep 2005 15:06 GMT
Danny hit the nail on the head. You need to place the log files on a
separate Raid 1 by them selves for peak write performance. And a RAID 5 is
a dog when it comes to writes. You should also try DBREINDEX instead of
DEFRAG to test the differences. Just make sure you have plenty of free
space in both the data and log files before executing the command.

Signature
Andrew J. Kelly SQL MVP
> Hi,
>
[quoted text clipped - 10 lines]
>
> jerome.
fnguy - 16 Sep 2005 15:39 GMT
Also note that DEFRAG does not update statictics so you'll need to consider
that as well as noting that dbreindex locks the table thus it's unavailable
for use during that time frame.
> Danny hit the nail on the head. You need to place the log files on a
> separate Raid 1 by them selves for peak write performance. And a RAID 5 is
[quoted text clipped - 16 lines]
> >
> > jerome.
JT - 16 Sep 2005 16:24 GMT
The most efficient way to improve the performance of a step is to eliminate
the step entirely or at least minimize the frequency that it occurs. When
bulk loading a table, try ordering the select to match the clustered index,
and perhaps this will reduce index fragmentation. You may also want to
consider dropping all indexes before the bulk load and then re-creating them
afterward. This will make fresh indexes in perhaps the same amount of time
and should also improve the performace of the bulk load since indexes are
not maintained as new rows are inserted.
> Hi,
>
[quoted text clipped - 10 lines]
>
> jerome.