Concept and basics of DBCC Commands in SQL Server

Many DBA’s and database developers aren’t very familiar with DBCC commands (aka Database Console Commands) because they don’t always have the chance to work with them. In this article, we’ll provide a simple primer on DBCC commands with specific examples

Note: These commands many of them require sysadmin or db_owner permission.

Basic Commands:

These are a good place to begin if you have not worked with DBCC commands previously starting from the upcoming ones:

DBCC HELP:

Provides syntax for a specific DBCC command, or lists all commands.
By default, only supported commands listed
This command has no impact on performance or data
Requires sysadmin role

We have two options to run this DBCC Command:

When you just run it as it is, it will list all of the supported DBCC commands

DBCC HELP ('?');

Figure 1 DBCC HELP command

When you run it, providing a specific command, it will give you the syntax for that:

DBCC HELP (CHECKDB);

Figure 2 DBCC HELP (CHECKDB) command

It will be very helpful if you don’t have internet access to get easy the syntax of any DBCC command just execute DBCC Help.

DBCC TRACEON, DBCC TRACEOFF, and DBCC TRACESTATUS:

If you have worked with trace flags before, you probably know that you can apply them as a startup parameter for the SQL Server service. Well, that is all well and good until you realize that for them to take effect, you have to restart the service. That is not always something you can easily do so that I can list some of the useful features of these commands:

DBCC TRACEON and DBCC TRACEOFF can be used to enable/disable a trace flag without requiring a service restart.
With DBCC TRACEON and DBCC TRACEOFF, you can enable/disable trace flags at either the session or the global level.
Be aware that when you enable or disable a trace flag, it’s quite possible that you can affect performance, depending on what functionality the trace flag changes.
Both commands (DBCC TRACEON and DBCC TRACEOFF) require the sysadmin role.
DBCC TRACESTATUS provides status for a specific trace flag, or all of them, and notes whether they are enabled for a session or globally.
Running DBCC TRACESTATUS does not affect performance or data, nor does it alter the configuration of the instance.
DBCC TRACESTATUS only needs the public role to be run.

Now, Let us see some examples of using these commands:

Use the following code to list all trace flags enabled just for this connection:

DBCC TRACESTATUS ();

Use this code to list all trace flags enabled globally:

DBCC TRACESTATUS (-1);

Follow the following scenario to test both of DBCC TRACEON and DBCC TRACEOFF

Run a full backup operation for one of your databases:

DECLARE @BackupPath NVARCHAR (100);

SET @BackupPath = 'C:\Backup\SQL_Shack2014_' +

REPLACE (CONVERT (nvarchar (19), SYSDATETIME (), 126), ':','') + '.bak';

BACKUP DATABASE [SQL_Shack2014]

TO DISK = @BackupPath

WITH NOFORMAT,

INIT,

COMPRESSION,

STATS = 05;

Then run a transaction log backup operation on the same database:

DECLARE @BackupPath NVARCHAR (100);

SET @BackupPath = 'C:\Backup\SQL_Shack2014_' +

REPLACE (CONVERT (nvarchar (19), SYSDATETIME (), 126), ':','') + '.trn';

BACKUP LOG [SQL_Shack2014]

TO DISK = @BackupPath

WITH NOFORMAT,

INIT,

COMPRESSION,

STATS = 05;

Now, when you check the ERRORLOG, you will notice entries to the SQL Server error log for every successful backup operation (local connection) as in the following figure:

Figure 3 Log file viewer (1)

We can suppress all successful in SQL Server error log by enabling trace flag 3226 using DBCC TRACEON command:

DBCC TRACEON (3226);

Then we can use DBCC TRACESTATUS command to verify that the trace is enabled:

DBCC TRACESTATUS ();

Run a transaction log Backup operation again and check SQL Server log to see the impact of enabling this trace flag:

DECLARE @BackupPath NVARCHAR(100);

SET @BackupPath = 'C:\Backup\SQL_Shack2014_' +

REPLACE (CONVERT (nvarchar (19), SYSDATETIME (), 126), ':','') + '.trn';

BACKUP LOG [SQL_Shack2014]

TO DISK = @BackupPath

WITH NOFORMAT,

INIT,

COMPRESSION,

STATS = 05;

You will notice that there is a log record for enabling the trace flag 3226, and the last transaction log backup has not logged as the following figure:

Figure 4 Log file viewer (2)

You can disable this trace flag using the following code:

DBCC TRACEOFF (3226);

To make these commands affect on SQL server instance globally which will be effective till the next restart of the instance, you can use the following codes:

Enable trace flag (3226) globally

DBCC TRACEON (3226, -1);

Verify again that the trace flag is enabled globally

DBCC TRACESTATUS (-1);

Turn off 3226 globally

DBCC TRACEOFF (3226,-1);

Informational Commands:

These commands will not change the configuration of our environment, but they will give us information about our environment.

DBCC SQLPERF:

ProvidesTransaction log space usage for all log files on an instance
Used to clear out data related to weight statistics, latch statistics, or spinlock statistics
To run this command, you need to have( VIEW SERVER STATE, ALTER SERVER STATE )
Execution does not affect system performance

To check log space utilization:

DBCC SQLPERF (LOGSPACE);

Figure 5 Result of DBCC SQLPERF Command

To clear wait statistics:

DBCC SQLPERF ("sys.dm_os_wait_stats", CLEAR);

To clear latch statistics:

DBCC SQLPERF ("sys.dm_os_latch_stats", CLEAR);

To clear spinlock statistics:

DBCC SQLPERF ("sys.dm_os_spinlock_stats", CLEAR);

DBCC SHOW_STATISTICS:

Displays statistics object for an index, indexed view, or column statistic
Use when troubleshooting and understanding the estimates from a plan and comparing against actual values
Viewing statistics does not introduce a performance load on the system
Require sysadmin, db_owner, or db_ddladmin roles, or tablet ownership

To implement this DBCC Command go through the following steps:

First, you need to view all indexes contained in the specified table:

USE [SQL_Shack2014];

sp_helpindex '[dbo]. [Students]';

Then you can use DBCC SHOW_STATISTICS against the index name you got in step 1:

DBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338');

The result displays the header, histogram, and density vector based on data stored in the statistics object.

Figure 6 Result of DBCC SHOW_STATISTICS Command

And you can specify what section of the result you want to see by using one of the following options:

DBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338') WITH STAT_HEADER;

DBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338') WITH DENSITY_VECTOR;

DBCC SHOW_STATISTICS ('[dbo]. [Students]','NonClusteredIndex-20170307-095338') WITH HISTOGRAM;

DBCC USEROPTIONS:

Returns options set for the current connection like isolation level or QUOTED_IDENTIFIER
Use to verify setting for connection to confirm they are correct, or consistent across different connect methods
Only displays information, does not modify settings
Require public role

DBCC USEROPTIONS;

Figure 7 Result of DBCC USEROPTIONS Command

Summary

As DBA or Database Developer, you can see the benefits DBCC Commands can offer for you. You may need one or more of them, which can provide you with a simpler way to implement your needs.

In this article, I have covered only top Basic DBCC commands. Hopefully I can cover the more advanced commands in another article. I hope this article has been informative for you.

Author
Recent Posts

Mustafa EL-Masry

Mustafa EL-Masry is a Senior database consultant and one of the experts in Database performance tuning in the Middle East.

Currently, he is working as a Senior consultant production DBA and Development DBA in many projects in multiple government sectors. He is a Top SQL Server blogger in the Middle East, founder of the community mostafaelmasry.com, and is the second Arabic author on Microsoft MSDN in SQL Server.

Based on his current position, he solved fairly interesting problems on fairly large databases and highly sensitive performance cases.

View all posts by Mustafa EL-Masry

Latest posts by Mustafa EL-Masry (see all)

Concept and basics of DBCC Commands in SQL Server - March 31, 2017
Hybrid Cloud and Hekaton Features in SQL Server 2014 - February 28, 2017
How to analyze Storage Subsystem Performance in SQL Server - February 23, 2017

Basic Commands:

Informational Commands:

Summary

Related posts: