0

My database is growing slower even though I have a bunch of indexes setup. Yesterday I figured out that I need to setup a maintenance plan to build the indexes etc.

So my question is what's the ultimate configuration for this?

  • Do I need All: "Rebuild idex task", "Reorganize index task" and "update statistics task". Anything else I need to setup. Shrink database? (Today, the only maintenance plans I have is backup)

  • Does it matter in what order I run them?

  • Any configuration options I should be aware of?

  • I've read of problems with log growing wild, how do I fix that? My transaction log is quite small and is usually a problem for me.

    • -
Joel Coel
  • 12,932
  • 14
  • 62
  • 100
Niels Bosma
  • 243
  • 1
  • 4
  • 15

1 Answers1

2

  • You do NOT need to run Update Statistics if you rebuild indexes. This happens by default with an index rebuild.

  • It matters a great deal. You will waste a lot of time if you update stats after an index rebuild, because it's completely redundant.

  • Rebuild indexes on tables with high clustered index fragmentation only. Defrag the others. Rebuilding everything is also a waste of time if it's unnecessary.

  • Put clustered indexes on ALL tables (that's just good practice)

  • Log growing wild is due to Full Recovery Mode and no transaction log maintenance. Check out Books Online regarding Simple versus Full versus Bulk Logging, and make your decision based on that. If you stick with Full, remember to back up your transaction logs regularly.

  • Finally, please check out Paul Randal's blog. All of this is covered there. In epic detail.

  • 2
    In addition to this please know that having "a bunch of indexes" can also be detrimental depending on how many you have. There comes a point where you have too many indexes slowing down any attempts to insert, update and delete. Also the more indexes you have on a table the larger of a deal it is to make sure you are rebuilding them. – Dave Holland Dec 15 '09 at 14:32
  • Nice comment. I didn't want to go too heavily into this because OP was more about maintenance tasks, but it doesn't hurt to mention it. +1 for you. –  Dec 15 '09 at 14:53