sql-serversql-server-2005maintenance-plan

Database Maintenance

I am trying to do some basic maintenance on a SQL Server 2005 database. It's fairly small (10gb in size, 50 tables or so).

I am first doing the following on all tables:

update statistics [table name] with fullscan

followed by reindexing all tables.

DBCC REINDEX ([table name], ...)

Is this correct? Is the sequence right? Do I need to do both or is one enough?

Solution

  • You can do these steps in your database, but it depends whether they are needed or not.

    For example if your database is configured to automatically update statistics, the first step usually will not be needed. (This first step can also be done easier by running the sp_updatestats stored procedure.)

    The second step (recreating the indexes) is an expensive step, and I would only consider doing it, if there are performance problems with the database.