Database Maintenance Plans

Database Maintenance Plans for SQL Server 2000

Database Maintenance Plans offer the following features in SQL Server 2000:

1. Backing up databases
2. Reorganization of indexes and data pages
3. Statistics Update
4. Shrinking the database
5. Database integrity checks

The above tasks of the maintenance plans would have associated with respective SQL Agent jobs and also these could be scheduled to suit your needs. Furthermore, these jobs could be created manually also without the help of maintenance plans.

Database Maintenance Plans for SQL Server 2005
In addition to the above mentioned features, SQL Server 2005 Enterprise Edition provides the option of rebuilding the indexes as an online operation. This makes it possible for the indexes to remain online while a rebuild index task is being performed on the database.


ONLINE = { ON OFF }
Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. The default is OFF.
Note: Online index operations are available only in SQL Server 2005 Enterprise Edition.

ON
Long-term table locks are not held for the duration of the index operation. During the main phase of the index operation, only an Intent Share (IS) lock is held on the source table. This enables queries or updates to the underlying table and indexes to continue. At the start of the operation, a Shared (S) lock is held on the source object for a very short amount of time. At the end of the operation, for a short time, an S (Shared) lock is acquired on the source if a nonclustered index is being created; or an SCH-M (Schema Modification) lock is acquired when a clustered index is created or dropped online and when a clustered or nonclustered index is being rebuilt. ONLINE cannot be set to ON when an index is being created on a local temporary table.

OFF
Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.


The frequency of these tasks and which tasks are needed need to be performed need to be decided on the following factors:
1. Importance of the database in question
2. Size of the database
3. Maintenance window frequency
4. Load on the server
5. Whether it is an OLTP or OLAP database or both
6. The kind of transactions that take place on the database (Point 5 and 6 would decide how often index reorganization/index rebuild/statistics updates are required and whether a shrink database/file is required)