Re-indexing in during Christmas Dowtime - planning help?

[TWillard]

We have several user databases, servers and tables that run in a 24x7 environment. There will be approximately 1.5 days of downtime over Christmas. I would like to re-index several large tables that are not re-indexed ever because of the environment. I have a limited amount of time to perform tasks.

1. Is there a way that I can estimate how long it will take to reindex a table?

2. Is it ok to re-index several tables at the same time with different spids. Should I re-index one table at a time?

3. As a back-out plan, if I am reindexing a table and production manufacturing resumes. How should I handle the stopping of re-indexing, will this cause negative effects.

4. Purely from a user database maintenance role, should I be concerned with other tasks in addition to re-indexing during a downtime opportunity?

Your help would be greatly appreciated. I have been searching this site and other web sites for information. I have gained some insight, but I do not have direct answers to some of my questions. Thanks. Tim

 

[mrdenny]

1. Not really. Best bet will be to restore to another server and try it in dev first.

2. That depends on the amount of CPU power and if the indexes are storred on different physical disks. If they are on the same disk I wouldn't think about it. If they are on different disks it could be a possibility depending on how the CPUs react.

3. If you kill a reindex it has to roll back just like any other transaction.

4. I'd also do some DBCC CHECKDBs, etc. Check your fragmentation on the indexes. If they are very fraggmented it may be faster to drop and recreate them than it will be to defrag and update them.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TWillard]

Great advice mrdenny!

1. I kind of suspected that it would be difficult to estimate the time to rebuild.

2. I had not thought about the physical disk concerns for the rebuild. All of the data and log files are on the same disk. Yes, I know this is not good especially with the size of our datbases. I plan to correct this issue early next year.


Thanks again.

 

[grimwyre]

We always backup everthing (to disk) before starting our regular re-index, done as part of the job. Transaction Log increases drmatically during the process. We re-index each table in turn, it's easier that way as all the commands are in a SQL job (it generates a list of tables on each run). Consider another backup (database and Logs) after the re-index as the next TLog backup may be very large and take more time than normal. We do our re-index in a maintainance slot to ensure there is minimal activity on the node as our sequence would affect any user activity. Hope this helps.