SQL Server 2008 Table Maintenance - Rebuild, Reorganize, Update Stats, Check Integrity etc

Question

I'm migrating a ~15GB database from SQL Server 2005 to a new server running SQL Server 2008, and along with that I need to create all the new Maintenance Plans. I can take care of all the backup stuff, but the table maintenance baffles me some. Does anyone have any input on how often I should (or how often you do would suffice too) the following tasks?

• Check Database Integrity
• Rebuild Indexes
• Reorganize Indexes
• Update Statistics
• Shrink Database?

Am I missing anything? Again if you can share how often you do these tasks that would be great...and/or share any general information about your approach to table maintenance that would be helpful. Lastly does it matter what order I run these tasks in (when setting up a job)?

Oh and I'm open to any links which might be of help!

Answer 1

Usually you'd do weekly or daily depending on your usage and maintenance windows.

You pretty much never shrink, especially not scheduled.

For rebuild/reorganise and statistics there are scripts (such as SQL Fool's one) that do a better job. Note: reorganise and rebuild are not exclusive but work different ways. 3rd party scripts can choose the best option (based on fragmentation).

We use weekly index/DBCC and daily statistics. And never shrink.

From Simple-talk: Don't Forget to Maintain Your Indexes

Answer 2

Re: Shrinking. I see so many people getting their claws out at the very mention of 'shrink' and 'database' in the same sentence, so time to clear things up a little.

Shrinking data is baaaaad. It literally turns your indexes into quivering shells of their former glory. Don't do it.

Shrinking log should not be done routinely, but if you have a ridiculously outsized log (i.e. in one case I saw a 40GB log for a 100MB database, due to whoever set it up putting recovery model to full then never dumping the transaction log), you can shrink the log (after BACKUP LOG) to reclaim that space without any ill effects (although, obviously, the SHRINK will chew up I/O while it's running).

PS: Speaking of log files, check your log file size and autogrowth settings. Small autogrowth settings can lead to underperforming log I/O (due to a poorly-explained feature in SQL Server called Virtual Log Files), particularly on batch transactions.

Answer 3

For scripts, Ola Hallengren has wicked awesome things maintenance. Use maintenance plans for now, once you become a little comfortable with the process of doing maintenance, switch to using scripts, running scheduled SQL agent jobs.

At my place of work, I am doing DBCC, reorg'ing indexes every night. On the weekends, it's DBCC, rebuild indexes, update stats.

Don't shrink anything. If you do, Brent Ozar will yell at you, it is never fun to have a SQL MCM yell at you.