Maintenance checks for SQL Server

A typical database maintenance schedule involves the following activities. Click on any activity for DBCC commands and system stored procedures associated with it.

TABLE OF CONTENTS    1. Check database consistency and data integrity    2. Rebuild indexes or remove index fragmentation    3. Update statistics    4. Report on space utilization in data and log files

 
Check database consistency and integrity

DBCC CHECKDB is the most extensive check of all objects within the database. This statement checks allocation and structural integrity of all data and index pages for each table and indexed view, as well as text and image objects. DBCC CHECKDB ensures that all data and index pages are correctly linked and pointers are consistent. Both user and system tables are checked within the specified database. DBCC CHECKDB executes both DBCC CHECKALLOC and DBCC CHECKTABLE statements for every object in the database , so if you use DBCC CHECKDB you do not need to execute DBCC CHECKALLOC or DBCC CHECKTABLE.

Certain options for DBCC CHECKDB (REPAIR_FAST, REPAIR_REBUILD and REPAIR_ALLOW_DATA_LOSS) require the database to be in single-user mode. If it is not, the statement will fail. Notice that you can't put a database in single-user mode while users are connected to it.

If you execute DBCC CHECKDB with no parameters and find errors, you should place your database in single-user mode and attempt to repair the problems. First be sure to execute the statements that create no risk of data loss – REPAIR_FAST and REPAIR_REBUILD. If you still have errors, execute DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. Also be sure to enclose the statement within an explicit transaction. If acceptable data loss occurs, you can commit the transaction. Otherwise you can rollback changes made by the statement.

Please note that running DBCC CHECKDB is a very resource-intensive operation. You should run this statement during limited user activity on your database server.

DBCC CHECKTABLE is identical to DBCC CHECKDB, with the exception that it checks a single table, indexed view or even a particular index, rather than the entire database.

DBCC CHECKALLOC checks the consistency of disk space allocation structures for a particular database. Since DBCC CHECKDB includes the same checks as DBCC CHECKALLOC, it is not necessary to execute DBCC CHECKALLOC if CHECKDB has been executed. In fact, it is recommended to use DBCC CHECKALLOC only if allocation problems are reported by DBCC CHECKDB or DBCC CHECKTABLE.

DBCC CHECKCONSTRAINTS checks the integrity of a specified constraint or all constraints within a specified database. DBCC CHECKCONSTRAINTS always executes in the context of the current database.

Notice that DBCC CHECKCONSTRAINTS does not check the consistency on disk and file level; rather it ensures integrity defined by foreign keys and check constraints – it simply validates data. If you wish to check table and index integrity on disk, you should execute DBCC CHECKDB or a combination of DBCC CHECKALLOC and DBCC CHECKTABLE on all tables.

How can constraint violations occur in the first place? Foreign keys and check constraints may not have existed when the database was created. Developers and database administrators might have created constraints using WITH NOCHECK option, which only prevents constraints violations going forward, but does not check the existing data. Furthermore, data that was linked through foreign keys might have become outdated and deleted from the parent table, but kept in related tables since it has associated records in subordinate tables.

DBCC CHECKCATALOG checks consistency in and between system tables of a specified database. Much like DBCC CHECKCONSTRAINTS this command does not check the integrity of the page allocations; rather it checks data in the system tables. DBCC CHECKCATALOG reporting errors mean that someone manually added, modified or removed records from system tables. If you're not aware of such activity you should tighten up your security – examine who has system administrator and database owner privileges and evaluate your security policy.

 
Rebuild indexes

As data rows are INSERTED, UPDATED and DELETED from tables, indexes get fragmented. The greater the fragmentation, the less effective the index is. DBAs must ensure that the fragmentation level is low or non-existent. Fragmentation level can be found by executing DBCC SHOWCONTIG statement against a particular index.

There are three ways to remove fragmentation:

DBCC DBREINDEX rebuilds a specified index or all indexes on the specified table. This statement allows indexes enforcing PRIMARY KEY and UNIQUE constraints to be rebuilt without dropping constraints. Not having to know index types and names can be useful as well. Using DBCC DBREINDEX is easier than coding individual DROP INDEX and CREATE INDEX statements for each index on a table. Keep in mind that rebuilding the clustered index also causes all non-clustered indexes to be rebuilt.

DBCC INDEXDEFRAG removes fragmentation from a specified clustered or non-clustered index. Unlike DBCC DBREINDEX, this statement requires a particular index be specified and can not be run against all indexes of the table. Removing fragmentation is also an online operation and therefore won't block users from working with the table. DBCC INDEXDEFRAG does add overhead to the system because it produces additional I/O load. It also compacts index pages and removes any pages that have no data left after compaction.

How often you remove fragmentation depends on the level of data modifications within your database. Systems that handle millions of transactions daily should have indexes rebuilt at least every week. On the other hand, databases that see few changes each week may perform fine even if you only rebuild indexes once each month.

 
Update statistics

Statistics contain information about the distribution of values within a particular index or columns of a table. You can create statistics using CREATE STATISTICS statement or using the sp_createstatistics system procedure. Statistics track information about whether indexes have good or poor selectivity, which determines the effectiveness of an index for satisfying queries. SQL Server creates statistics automatically when you create indexes. In addition, SQL Server creates statistics for columns that do not have indexes defined on them.

Statistics for a particular index can be viewed using DBCC SHOW_STATISTICS statement. As data changes, statistics become outdated. Stale statistics can cause SQL Server to make sub-optimal decisions when choosing indexes for satisfying queries.

By default SQL Server 2000 updates statistics automatically on every table. However, in some cases it makes sense to turn off automatic statistics' updates. For example, let's suppose you have some sort of batch-processing routine that adds millions of rows to your table on weekends, when the system usage is minimal. Automatic statistics' updating will simply slow your batch process and provide no benefit to the system. Instead you can turn off the automatic updates for the weekend and update statistics on that table first thing Monday morning. You can enable or disable automatic statistics' updates using sp_autostats procedure.

If you wish to update statistics for a single table or index, you can use UPDATE STATISTICS command. Alternatively you can execute sp_updatestats system procedure to update statistics on all tables in the current database.

 
Report on space utilization in data and log files

You may be compelled to expand or shrink a data file or a transaction log file, possibly when using the DBCC SHRINKDATABASE or DBCC SHRINKFILE command.

The sysindexes table can become inaccurate over time, especially in databases that grow frequently and/or shrink frequently. The DBCC UPDATEUSAGE command reports and corrects inaccuracies in the sysindexes table. You should execute this statement if you expect your database or table size to be different than what is reported by sp_spaceused system procedure.

It's a good idea to execute DBCC UPDATEUSAGE after each time you shrink database files with DBCC SHRINKDATABASE or DBCC SHRINKFILE, or as a regularly scheduled maintenance task.

ABOUT THE AUTHORS
Kevin Kline is the director of SQL Server solutions at Quest Software Inc. He also presides as president of the international Professional Association for SQL Server (PASS) and frequently contributes to database technology magazines, Web sites and discussion forums. He is the author of "SQL in a Nutshell" published by O'Reilly & Associates. Kline welcomes your questions as SearchSQLServer.com's Monitoring/Administration expert.

Baya Pavliashvili is a DBA manager with Healthstream, an online healthcare education company, where he oversees database operations supporting over one million users. Pavliashvili's primary areas of expertise include performance tuning, replication and data warehousing. He can be reached at baya.pavliashvili@healthstream.com.

More information from SearchSQLServer.com

• Tip: SQL Server backups made easy using maintenance plans
• Tip: Use the database maintenance plan wizard
• Ask the Experts: Ask Kevin Kline your SQL Server maintenance questions

•