up vote 0 down vote favorite

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.

share|improve this question
  • Looks like you are getting the same recommendation from everyone in the first 3 answers. – RLF Oct 14 '14 at 14:11
  • Thanks a lot for your answers. Do I need to always restart the application servers after index and statistics maintenance? – Info23 Oct 14 '14 at 19:13
  • No that is not necessary. – RLF Oct 14 '14 at 19:17
up vote 5 down vote

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

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

share|improve this answer
  • I guess all three of you gave same answer ? But no body specifically told him about frequency and I guess answer would be it depends you need to use script and evaluate yourself the frequency – Shanky Oct 14 '14 at 17:32
  • @Shanky The defaults are pretty reasonable, and this is what I meant by "tweak the settings and learn how it works over time." – Aaron Bertrand♦ Oct 14 '14 at 17:39
up vote 1 down vote

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.

share|improve this answer
up vote 1 down vote

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.

share|improve this answer
  • This does not answer OP question he is asking about how frequently he should rebuild indexes. – Shanky Oct 14 '14 at 17:31
  • @Shanky - Edited – RLF Oct 14 '14 at 18:23

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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