MS SQL Data Warehousing - improving dbcc indexdefrag processing time?

Author

improving dbcc indexdefrag processing time?

Jéjé

2005-09-16, 3:23 am

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

2005-09-16, 7:23 am

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

"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:%23yt2TSmuFHA.992@TK2MSFTNGP12.phx.gbl...
> 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.
>
>

Andrew J. Kelly

2005-09-16, 9:23 am

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.

--
Andrew J. Kelly SQL MVP

"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:%23yt2TSmuFHA.992@TK2MSFTNGP12.phx.gbl...
> 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.
>
>

fnguy

2005-09-16, 11:23 am

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.

"Andrew J. Kelly" wrote:

> 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.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "JÃ©jÃ©" < willgart@BBBhotmailA
AA.com> wrote in message
> news:%23yt2TSmuFHA.992@TK2MSFTNGP12.phx.gbl...
>
>
>

2005-09-16, 11:23 am

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.

"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:%23yt2TSmuFHA.992@TK2MSFTNGP12.phx.gbl...
> 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.
>
>

Paul S Randal [MS]

2005-09-16, 8:23 pm

> 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.

--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine

This posting is provided "AS IS" with no warranties, and confers no rights.

"Danny" <someone@nowhere.com> wrote in message
news:aIyWe.39284$vQ3.6401@trnddc08...
> There's the usual answer around IO performance. Avoid Raid 5, separarte
> your data from logs, and separate your nonclustered indexes. > "Jéjé"
> < willgart@BBBhotmailA
AA.com> wrote in message
> news:%23yt2TSmuFHA.992@TK2MSFTNGP12.phx.gbl...
>
>

Sponsored Links