Tips for using SQL Server 2014 Database Console Commands
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.
Use DBCC CHECKFILEGROUP instead of DBCC CHECKDB, if you need to check only specified filegroup,
not entire database.
If your database contains several filegroups and you need to check only single filegroup, using
DBCC CHECKFILEGROUP command provides a better performance in comparison with using DBCC CHECKDB
command.
Avoid using the DBCC DBREINDEX command.
This command can be used to rebuild one or more indexes for a table in the specified database.
Otherwise, Microsoft recommends avoid using this feature in new development work, and plan
to modify applications that currently use this feature because this command will be removed in
the next version of Microsoft SQL Server. Use REBUILD option of ALTER INDEX instead.
Use the DBCC SHOW_STATISTICS command to displays the current distribution statistics for
the specified target on the specified table.
You can use this DBCC command to see how distributed the data is and whether the index is really
a good candidate or not.
Avoid using the DBCC SHOWCONTIG command.
This command can be used to display fragmentation information for the data and indexes of the
specified table or view. Otherwise, Microsoft recommends avoid using this feature in new
development work, and plan to modify applications that currently use this feature because
this command will be removed in the next version of Microsoft SQL Server.
Use sys.dm_db_index_physical_stats instead.
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.
Use the DBCC PROCCACHE command to displays information about the procedure cache.
This command returns the total number of pages used by all entries in the procedure cache, the
number of pages used by all entries that are currently being used, the total number of entries
in the procedure cache and the number of entries that are currently being used.
You can use the DBCC TRACESTATUS command to get the status information for the particular
trace flag(s) currently turned on.
This is the syntax:
DBCC TRACESTATUS ([ [trace# [,…n] ] [,] [-1] ]) [WITH NO_INFOMSGS]
To get the status information for all trace flags currently turned on, you can use -1 for trace#.
This is the example:
DBCC TRACESTATUS (-1)
Consider using the NO_INFOMSGS option with DBCC CHECKDB, DBCC CHECKFILEGROUP,
DBCC CHECKTABLE, DBCC CHECKALLOC, DBCC UPDATEUSAGE, or DBCC CHECKCATALOG
commands.
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.
Before running the DBCC CHECKDB command, run this command with the ESTIMATEONLY
clause 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.
Use the DBCC UPDATEUSAGE command to correct pages and row count inaccuracies in the
catalog views.
When these inaccuracies occur, the sp_spaceused system stored procedure returns incorrect space
usage. In this example, the DBCC UPDATEUSAGE command corrects pages and row count inaccuracies
in the TestDB database:
DBCC UPDATEUSAGE (TestDB)
If you want to turn off the specified trace flag(s), you can use the DBCC TRACEOFF command.
This is the syntax:
DBCC TRACEOFF (trace# [,…n] [,-1]) [WITH NO_INFOMSGS]
You can use the DBCC TRACEON command to turn on the specified trace flag.
This is the syntax:
DBCC TRACEON (trace# [,…n][, -1]) [WITH NO_INFOMSGS]
For example, you can turn on the trace flag 1204 to return the resources and types of locks
participating in a deadlock. This is the example:
DBCC TRACEON (1204)
Use the REPAIR_FAST option with DBCC CHECKDB, DBCC CHECKTABLE or DBCC CHECKALLOC
commands.
Using the REPAIR_FAST option provides quick repairing without risk of data loss, so this option
should be used whenever possible.
Avoid using the DBCC INDEXDEFRAG command.
This command can be used to defragment indexes of the specified table or view. Otherwise,
Microsoft recommends avoid using this feature in new development work, and plan to modify
applications that currently use this feature because this command will be removed in
the next version of Microsoft SQL Server. Use REORGANIZE option of ALTER INDEX instead.
If you don’t need to check the nonclustered indexes for user tables, use the NOINDEX
option with the DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE and
DBCC CHECKALLOC commands.
The NOINDEX option specifies that intensive checks of nonclustered indexes for user tables
should not be performed. By using this option, you can decrease the overall execution time
of the DBCC command and boost total SQL Server performance.