index rebuild/reorganize frequency

Question

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.

Answer 1

Don't re-invent the wheel; just get a solution like Ola's in place now:

• Ola Hallengren's SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

You can tweak the settings and learn how it works over time.

Answer 2

You can't go wrong with Ola Hallengren's award winning solution. It's pretty awesome ;

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

You can schedule this to run once a day to begin with, and then you should re-evaluate your needs based on how busy the solution actually becomes regarding the needed index maintenance on your particular system.

Answer 3

I would suggest that you use Ola Hallengren's scripts, freely available and widely used.

His website is: https://ola.hallengren.com/

•DatabaseBackup: SQL Server Backup

•DatabaseIntegrityCheck: SQL Server Integrity Check

•IndexOptimize: SQL Server Index and Statistics Maintenance

He has the best practices coded into his scripts, so it should serve you well.

EDIT: For my servers I have found that once a week works fine for us.

With regard to the databases on your server, you can run separate jobs, or you can have a single job go through all the databases. Look at the MaintenanceSolution to see how to include and exclude databases.