SQL Maintenance Plans – Rebuild or Reorganize?

Over time, customers may begin to notice a slowdown in the responsiveness of their Dynamics GP or CRM systems. One of the culprits can be SQL indexes that are no longer valid. It’s like telling your SQL Server to drive to California and then handing it a 20 year old road map – it’s going to take a little longer than it should!

This is where maintenance plans come in. In a perfect world, each of our customers would either hire a SQL DBA to constantly monitor the server, tweaking and optimizing or else pay us to do this. Yeah…right. This is where Maintenance Plans come in – ways of automating and scheduling those cleanup tasks so that the system doesn’t deteriorate over time.

Paul Thurrott of SuperSite for Windows posits that the brain is like a stack. Put a new memory in at the top and an old memory has to drop out the bottom. As I get older, I’m starting to believe this more and more! Memorize that esoteric SQL command and out drops that memory of Skippy, my pet turtle. (Did I have a turtle? I seem to remember something like that…)

That’s where this blog post comes in. I need to document those common tasks that I use every time I setup a new maintenance plan and there’s no reason you shouldn’t benefit as well.

Nightly sub plan:

• Full backup of all user databases
• Delete any existing backups older than 1 week (depending on disk space)
• Reorganize Indexes
• Update Statistics

Daily sub plan (every hour):

• Transaction log backup of all user databases
• Delete any existing backups older than 1 week (depending on disk space)

Weekly sub plan:

• DB Integrity Check
• Full backup of all databases (including system)
• Rebuild Indexes
• Update Statistics
• Delete history older than 8 weeks
• Shrink Databases

According to Pinalkumar Dave, indexes should be rebuilt when greater than 40% fragmented. Between 10% and 40%, use reorganize.