I have separate Maintenance plan (Differentialbackup, Fullbackup, Transactionalbackup and Weeklybackup) where I backup 10 databases. Well in order to rebuild index task do I create a separate maintenance plan and include the rebuild index task of all 10 databases?
Regards
Chitra
IMO I would do this sep to the backups. Doing this index rebuild can effect performance of the server if users are current using the DB, where as backups have min effect (most of the time).
Ola has a excellent script to rebuild/reorganise indexes based on indexes fragmentation. I use this script in my production env and it does the trick.
Rebuilding indexes can have a very substantial impact on performance and can also create some locking. If you have the luxury of a dedicated maintenance window, then you should do all rebuilding/reorganizing in that time period. On the other hand, backups, especially log backups, have relatively little performance impact most of the time, so you can normally do those safely without necessarily waiting for a dedicated maintenance window.
The other question of course is whether or how often you really need to do a rebuild at all. There is a good discussion of that on simple-talk at Index Fragmentation Anxiety: a Doctor Speaks and another thorough overview at Defragmenting Indexes in SQL Server 2005 and 2008 . Unless you have vast maintenance windows to play with so you know it can always finish everything before users start getting on the system, then I would recommend programmatically checking the fragmentation level and only defragging if it is unacceptably high.
