SQL Server

Some tips for using SQL Server 2016 Database Console Commands

Some tips for using SQL Server 2016 Database Console Commands


Consider using a MAXDOP option with the DBCC CHECKTABLE, DBCC CHECKDB and
DBCC CHECKFILEGROUP.

This option was first introduced in SQL Server 2016. By using a MAXDOP option
with the DBCC CHECKTABLE, DBCC CHECKDB and DBCC CHECKFILEGROUP you can
specify the degree of parallelism.

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.

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.

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.

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.

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.

Facebooktwittergoogle_plusredditpinterestlinkedinmail