2

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!

Improve this question
2
  • 1
    You should look into using Ola Hallengren's scripts (ola.hallengren.com) . They allow you to specify a run-duration for the index maintenance jobs. And yes, they are still strongly recommended, but if you are using the Maintenance Plans that came with SQL Server, there are much better methods.
    – Jonathan Fite
    Commented Sep 26, 2016 at 15:02
  • thank you @JonathanFite! i checked out ola and will use it. I am not a dba, just a programmer, so a little afraid of it as I typically just used maintenance plans... can you tell me if I can just execute this script using a tsql maintenance plan? or must I use sqlcmd? Thans!
    – Greg Bala
    Commented Sep 27, 2016 at 12:19

1 Answer 1

Reset to default
4

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.

Improve this answer
1
  • To add to this, you need to learn your system and how it's used. Monitor and track your index usage, or lack of index usage. What works for me may not work for you. For example, we have a set of tables that support an application. If the tables get more than 15% fragmented, performance is very negatively affected. I can't modify the application code, so we have to monitor the indexes and stats closely to make any improvements that we can control.
    – Jason B.
    Commented Sep 26, 2016 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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