sql server maintenance plan

Question

What are the best practices for optimal database maintenance?

is updating statistics necessary? (i think statistic update is on by default) how about reorganize indexes or just rebuild them? shrink database? integrity check?

How often do you run them?

Answer 1

is updating statistics necessary?

In most cases the default update statistics option on is just enough but depending on your system, your workload and the performance issues, you may have to run the explicit update statistics.

how about reorganize indexes or just rebuild them?

The normal best practices for this in the industry is run reorganize if the avg. fragmentation level is between 10-30% and rebuild if fragmentation > 30%. The numbers I listed here are NOT absolute cut off but only best practises.

shrink database?

You don't want to turn on this option. Take the time to turn this option off as this can cause long term performance issues in the long run.

integrity check?

This is an absolute must to run periodically. What is periodically mean i.e at-least once in every week. Make sure you torn of page_verify option to CHECKSUM like below if the database was created in versions prior to SQL Server 2005.

ALTER DATABASE DBNAME SET PAGE_VERIFY CHECKSUM

I don't prefer to use Maintenance plans for these tasks and its best to use SQL Scripts. I use scripts from Ola Hallengren and they work great and recommend to anyone out there. You can get those scripts from

http://ola.hallengren.com/

Answer 2

This technet magazine article Top Tips for Effective Database Maintenance (and follow up podcast from runas radio) answers all this and more

Here's the summary from the article:

As you can see, to ensure your database stays healthy and available there are a few "'must do" tasks. Here's my final checklist for an involuntary DBA taking over a database:

•Remove excessive transaction log file fragmentation.

•Set auto-growth correctly.

•Turn off any scheduled shrink operations.

•Turn on instant file initialization.

•Put a regular process in place to detect and remove index fragmentation.

•Turn on AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, plus have a regular process in place to update statistics.

•Turn on page checksums (or least torn-page detection on SQL Server 2000).

•Have a regular process to run DBCC CHECKDB.

•Have a regular process in place to take full database backups, plus differential and log backups for point-in-time recovery.