Index recreation takes long time

Index recreation takes long time

Post by harvinde » Sat, 17 Aug 2002 16:05:35



Hi,

I have a table with 480 million rows and size of 70GB.
I started the dbcc reindex (table_name,'') to rebuild all
indexes (4) each having 1 column........
It is running since last 25 hrs and using 98% cpu.....
is it looks normal with this big size of table......
is dropping the indexes and recreate again using create
index command will be fast than dbreindex......
what is difference between running dbcc reindex .....and
drop index then create index command?????
This is on test machine , we normally use dbcc indexdefrag
on prod machine????

Thanks
--Harvinder

 
 
 

Index recreation takes long time

Post by Andrew J. Kell » Sat, 17 Aug 2002 17:55:04


One of the things that is probably taking so long is the fact all of this is
logged.  With a table of that size you may be better off dropping all of
them and recreating them one at a time starting with the Clustered Index.
If you have tempdb on a separate drive array them you might get better
performance by using the Sort In TempDb option.  Of coarse making sure the
log file is large enough to start and on it's own Array will help a lot.

--
Andrew J. Kelly   SQL MVP
Targitinteractive, Inc.


Quote:> Hi,

> I have a table with 480 million rows and size of 70GB.
> I started the dbcc reindex (table_name,'') to rebuild all
> indexes (4) each having 1 column........
> It is running since last 25 hrs and using 98% cpu.....
> is it looks normal with this big size of table......
> is dropping the indexes and recreate again using create
> index command will be fast than dbreindex......
> what is difference between running dbcc reindex .....and
> drop index then create index command?????
> This is on test machine , we normally use dbcc indexdefrag
> on prod machine????

> Thanks
> --Harvinder