SQL Server 2005 DBCC Optimization Tips (Part 1)
Consider using DBCC CHECKALLOC command instead of DBCC CHECKDB command if
you need to check the consistency of disk space allocation structures only.
In comparison with DBCC CHECKALLOC command, DBCC CHECKDB command checks the integrity of the
data and index pages for each table in the database, and much more slowly than DBCC CHECKALLOC
command. So, you should use DBCC CHECKALLOC command instead of DBCC CHECKDB command whenever
possible.
Run DBCC commands during periods of low database access.
Because DBCC commands usually are very resource effective, try to schedule them during CPU
idle time and slow production periods.
Consider using the REPAIR_REBUILD option with DBCC CHECKTABLE command.
Using the REPAIR_REBUILD option provides repairing without risk of data loss, so this option
should be used whenever possible.
Note. Replace the REPAIR_FAST option with the REPAIR_REBUILD option because the REPAIR_FAST
option is maintained for backward compatibility only.
Do not forget to specify the index_name or index_id option with DBCC SHOWCONTIG,
if you do not need to display fragmentation information for all table's indexes.
When you specify the index, DBCC SHOWCONTIG runs integrity display fragmentation information
only on that index. Using this option can improve the DBCC SHOWCONTIG performance and should
be used whenever possible.
Note. Microsoft recommends use the sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG
command because DBCC SHOWCONTIG can be removed in a future version of Microsoft SQL Server.
Consider using DBCC INDEXDEFRAG command instead of DBCC DBREINDEX to defragment
clustered and secondary indexes of the specified table or view.
Unlike DBCC DBREINDEX, DBCC INDEXDEFRAG does not hold locks long term and thus will not block
running queries or updates.
Note. Microsoft recommends use the ALTER INDEX instead of DBCC INDEXDEFRAG and DBCC DBREINDEX
commands because these commands can be removed in a future version of Microsoft SQL Server.
Before executing DBCC CHECKDB command, run this command with the ESTIMATEONLY
option to estimate the tempdb space needed for CHECKALLOC and CHECKTABLES.
So, you can calculate the tempdb database size and run the DBCC CHECKDB command without
performance degradation. When this option is used, the actual database check is not performed.
Consider using the NOINDEX option with DBCC CHECKFILEGROUP command.
This option specifies that intensive checks of nonclustered indexes for user tables should not
be performed. Using the NOINDEX option decreases the DBCC CHECKFILEGROUP execution time and
should be used whenever possible.
If you need to check the integrity of the data and index pages for each table
in the database and check the consistency of disk space allocation structures
for the database, use DBCC CHECKDB command instead of using DBCC CHECKTABLE
and DBCC CHECKALLOC.
In this case, using DBCC CHECKDB command is more efficient, because DBCC CHECKDB eliminates
the need to run DBCC CHECKTABLE and DBCC CHECKALLOC separately.
Consider using the REPAIR_FAST option with DBCC CHECKALLOC command.
Using the REPAIR_FAST option provides quick repairing without risk of data loss. By the way,
you should use this option carefully, because in SQL Server 2005 the REPAIR_FAST option is
maintained for backward compatibility only.
If you need to run DBCC CHECKTABLE faster, consider using the TABLOCK option
with DBCC CHECKTABLE command.
The TABLOCK option causes DBCC CHECKTABLE to obtain locks instead of using an internal database
snapshot. Using this option causes DBCC CHECKTABLE command to run faster on a table under heavy
load, but decreases the concurrency available on the table while DBCC CHECKTABLE is running.
Consider using the NO_INFOMSGS option with DBCC CHECKDB, DBCC CHECKFILEGROUP,
DBCC CHECKTABLE, DBCC CHECKALLOC, DBCC DBREINDEX, DBCC UPDATEUSAGE, DBCC INDEXDEFRAG,
or DBCC CHECKCATALOG statements.
Using the NO_INFOMSGS option suppresses informational messages and the report of space used.
So, using this option can reduce processing and increase performance of the DBCC commands.
Try to avoid database repairing by using DBCC CHECKDB command if you have
database backup.
If you have SQL Server 2005 backup, Microsoft recommends restore the database from the last
backup instead of the repair database by using DBCC CHECKDB command. This is because repair
operations do not consider any of the constraints that may exist on or between tables.
