Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.

My question is this:

Would it be an acceptable strategy to stagger the reindexing over the week? Say, 5 DBs per night?

Thanks.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

As long as the indexes that need to be rebuilt get rebuilt it should be fine to stagger them.

This will of course vary based on your usage - if you have a ton of updates/inserts/deletes run on Fridays then there may be a good reason that you currently reindex on Saturdays.

Something else you can do, instead of blindly rebuilding every index every week, is using some queries to check fragmentation levels and only reindexing when a specific index is over a certain threshold of fragmentation. The usual threshold (which was chosen somewhat randomly by Paul Randal many years ago) is 30%. There are dozens if not hundreds of sample scripts available for this.

share|improve this answer
4  
Two popular ones are by Ola Hallengren and SQL Fool –  Martin Smith Jan 23 '12 at 15:29
    
Great. Thank you both. –  swasheck Jan 23 '12 at 15:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.