October 25, 2006 at 12:11 pm
Guys,
I have a very large database in SOL2005. At present, when I re-index, it take sometime 24 hours to complete.
Do anyone have an idea of a faster method of re-index on a large table?
October 25, 2006 at 12:19 pm
In 2005 Enterprise Edition you can use the REBUILD WITH ONLINE = ON option to rebuild your indexes without taking them offline. I've really like it.
Other than that you might try adding more disk subsystems and partitioning your index across them and then rebuilding the partitions one at a time. That might help with both index performance as well as maintenance. Your mileage may vary...
October 26, 2006 at 5:32 am
Why dont you try the option ALTER INDEX REORGANISE option to defragment the index.
Otherwise Upgrade the system configuration. If possible have a Dual processor and place the index on a separate disk other and process the reindexing. If possible increase the space of the transaction log.
October 26, 2006 at 7:25 am
Thanks guys.
October 26, 2006 at 7:44 am
SQL 2005 has some better options, but I'll assume because this is a SQL 2000 forum that they don't do you any good.
If adding hardware is an option then go for it, the more disks and memory the better. Keep in mind that it might not solve the problem.
Trying to improve your configuration might help. Reindexing generates a huge amount of log activity, are your transaction log files on separate drives than your data files? It takes a lot of free space to rearrange tables, are your data files too full? Are you using multiple files per filegroup to spread the load?
Do you have a maintenance window for reindexing where nothing else is going on, or is the reindexing getting blocked by other users or by backups? If there is competing activity, DBCC INDEXDEFRAG might work better than DBCC DBREINDEX. This is not a maintenance plan option, you would have to script it yourself.
You can schedule reindexing more frequently so there is less work to do. Instead of reindexing the entire database, you can write a script to look at table fragmentation and only reindex the indexes that need it. (Look up DBCC SHOWCONTIG in BOL, there is a good example.) If you log this activity, you can think about changing the index structure or fill factor of indexes that get fragmented quickly.
Finally, if you absolutely have to defragment large tables and have a dedicated maintenance window, you can dump each table to a native BCP file, truncate it, and read them back in. This will also involve dropping and recreating foreign keys. If your database is in simple or bulk logged recovery mode this will be very quick. With simple recovery mode you should do a full backup first, otherwise you need a full backup afterwards.
October 26, 2006 at 7:51 am
Thank you Scott.
October 26, 2006 at 10:01 am
When you say large what do you mean?
Also are you trying to reindex a table (therefore all indexes) or an individual index. you could use a targeted index approach based on which indexes needed it most.
Probably no overall performance benefit but it does all you to do it in chunks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply