SQL Server Maintenance Plans

Maintenance Scripts are better

I’ve just been reviewing our Maintenance Plans (MPs). After readings a few best practice articles, it seems I’d be better off using scripts instead of MPs.

When using the Reorganise and Rebuild tasks within a MP, they will run regardless of the index fragmentation. Using scripts I could check to see if indexes actually need a little TLC, based on the level of fragmentation, eg.

  • < 5% = no reorganise/rebuild
  • 5-30% = reorganise
  • > 30% = rebuild