List of SQL Server DBCC Commands and Description related to Information
As a SQL Server DBA, it is very command to use various DBCC commands to retrieve data required to understand problems or to get the certain status information or details. DBCC stands for Database Console Commands. There are many documented and undocumented DBCC commands in SQL Server which are very useful for DBAs and are used in different usage like to retrieve usage information, to run maintenance tasks, To validate databases, and other Miscellaneous purposes.
Below are some of the DBCC commands related to Informational Tasks that gather and display various types of information.
DBCC INPUTBUFFER – Displays the last statement sent from a client to an instance of Microsoft SQL Server.
DBCC OPENTRAN – DBCC OPENTRAN helps to identify active transactions that may be preventing log truncation. DBCC OPENTRAN displays information about the oldest active transaction and the oldest distributed and nondistributed replicated transactions, if any, within the transaction log of the specified database. Results are displayed only if there is an active transaction that exists in the log or if the database contains replication information. An informational message is displayed if there are no active transactions in the log. Use DBCC OPENTRAN to determine whether an open transaction exists within the transaction log. When you use the BACKUP LOG statement, only the inactive part of the log can be truncated; an open transaction can prevent the log from truncating completely. To identify an open transaction, use sp_who to obtain the system process ID.
DBCC OUTPUTBUFFER – Returns the current output buffer in hexadecimal and ASCII format for the specified session_id. DBCC OUTPUTBUFFER displays the results sent to the specified client (session_id). For processes that do not contain output streams, an error message is returned.
To show the statement executed that returned the results displayed by DBCC OUTPUTBUFFER, execute DBCC INPUTBUFFER.
DBCC PROCCACHE – Displays information in a table format about the procedure cache. The procedure cache is used to cache the compiled and executable plans to speed up the execution of batches. The entries in a procedure cache are at a batch level. The procedure cache includes the entries such as Compiled plans, Execution plans, Algebrizer tree, Extended procedures. The SQL Server Performance Monitor uses DBCC PROCCACHE to obtain information about the procedure cache.
DBCC SHOW_STATISTICS – DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the index scan operator in the query plan, improving query performance by avoiding a resource-intensive index scan. The query optimizer stores statistics for a table or indexed view in a statistics object. For a table, the statistics object is created on either an index or a list of table columns. The statistics object includes a header with metadata about the statistics, a histogram with the distribution of values in the first key column of the statistics object, and a density vector to measure cross-column correlation. The Database Engine can compute cardinality estimates with any of the data in the statistics object. DBCC SHOW_STATISTICS displays the header, histogram, and density vector based on data stored in the statistics object. The syntax lets you specify a table or indexed view along with a target index name, statistics name, or column name. This topic describes how to display the statistics and how to understand the displayed results.
DBCC SHOWCONTIG – Displays fragmentation information for the data and indexes of the specified table or view. The DBCC SHOWCONTIG statement traverses the page chain at the leaf level of the specified index when index_id is specified. If only table_id is specified or if index_id is 0, the data pages of the specified table are scanned. The operation only requires an intent-shared (IS) table lock. This way all updates and inserts can be performed, except those that require an exclusive (X) table lock. This allows for a tradeoff between speed of execution and no reduction in concurrency against the number of statistics returned. However, if the command is being used only to gauge fragmentation, we recommend that you use the WITH FAST option for optimal performance. A fast scan does not read the leaf or data level pages of the index. The WITH FAST option does not apply to a heap.
DBCC SQLPERF – Provides transaction log space usage statistics for all databases. It can also be used to reset wait and latch statistics. The transaction log records each transaction made in a database.
DBCC TRACESTATUS – Displays the status of trace flags. In SQL Server, there are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only for that connection. Global trace flags are set at the server level and are visible to every connection on the server.
DBCC USEROPTIONS – Returns the SET options active (set) for the current connection.DBCC USEROPTIONS reports an isolation level of ‘read committed snapshot’ when the database option READ_COMMITTED_SNAPSHOT is set to ON and the transaction isolation level is set to ‘read committed’. The actual isolation level is read committed.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.