I am a dba by accident. I am doing index rebuild/reorganize. I would love to use some scripts once I become comfortable doing these tasks. I did go through some of the forum pages regarding this topic. I have a sql server 2008R2 instance which has around 25 databases of different size and needs. I have been assigned to do index tuning for 8 databases. Since the size and the fragmentation level and the no of indexes and also the rate at which de-fragmentation happens differs from database to database, I understand that some databases need an index maintenance on a monthly basis and few others on a weekly basis. I have two questions:
How frequently should I schedule for the rebuild/reorganize process? For eg: Last month I rebuilt indexes for fragment level above 30% and reorganized and updated stats for 5-30% fragment level. Then I checked it after a week, i found one or two fragmented indexes, I checked it after a month and I found roughly around 10 fragmented indexes..is it the right time to schedule for an index maintenance again?
If I develop a common script for the instance, how should I make it work for all the databases on the instance, because not every database would need an index maintenance at the same time.