Sql-server – how often to run reorganize index & update statistic maintenance task on sql server 2012

index-maintenancemaintenancesql-server-2012

I know this is a hard question to answer without more details, but I am hoping for some general guidelines or rule of thumb ideas, or perhaps a how-to determine what would be best for my set of databases.

I have a system of about 100 databases, that since its creation 8 years ago, i did index reorg, stats update, check integrity task on all databases, every night. most databases are about 1GB in size, one is 10GB

As the databases grew, this task takes more and more time, and now has adverse effects on performance, and there is no good time when I can handle the performance effect.

I am afraid of just turning this task off, and I am looking for some guidance – I don't even know if doing such maintenance task is necessary! Should I be doing such maintenance job? how often?

Thank you!

Best Answer

You should intelligently do index reorg and rebuilds. As a general (widely accepted) rule of thumb is

If your indexes are fragmented :

  • If index has less than 1000 pages - do not perform any index maintenance operation.
  • Less than 10% - then do nothing
  • between 10% and 30% - then do a reorg
  • more than 30% - then rebuild them.

I would highly recommend to use Ola's maintenance solution (index and checkdb)

Since your databases are 1GB to max 10GB, depending on your server spec it should not take very long time.

Related Question