- Managing data and transaction log files
- Eliminating index fragmentation
- Ensuring accurate, up-to-date statistics
- Detecting corrupted database pages
- Establishing an effective backup strategy
- Data and log file management
- Index fragmentation
- Corruption detection
- The data and log files are separated from each other and isolated from everything else as well
- Auto-growth is configured correctly
- Instant file initialization is configured
- Auto-shrink is not enabled and shrink is not part of any maintenance plan
ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;
- Fragmentation within individual data and index pages (sometimes called internal fragmentation)
- Fragmentation within index or table structures consisting of pages (called logical scan fragmentation and extent scan fragmentation)
- Analyze indexes and determine which indexes to operate on and how to do the fragmentation removal.
- For all indexes that were not rebuilt, update the statistics.
- Update statistics for all of the non-indexed columns.
ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;
ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;
DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, ALL_ERRORMSGS;
- 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.