As a SQL database administrator, you know how important it is to use DBCC CHECDKB to check databases' physical and logical integrity. However, some of you might be hesitant to execute the DBCC CHECKDB command regularly, as it can impact the performance of SQL Server. But, it's crucial to run the command as part of your daily, weekly, or monthly database routine to detect database corruption as early as possible.
You can, however, minimize disruptions caused by the DBCC CHECKDB command using the right parameters, such as NO_INFOMSGS and others.
DBCC CHECKDB with NO_INFOMSGS
DBCC CHECKDB dumps page after page of results that don't matter. For instance, executing the command against a test database returns thousands of lines of output, as shown in the image below:
You may miss out on a critical error message in the thousands of lines of code. But, you can avoid all the irrelevant output and only see the errors by executing DBCC CHECKDB with NO_INFOMSGS. The syntax is as follows:
DBCC CHECKDB(Stellar_TestDB) with NO_INFOMSGS;
The output of the above command is as follows:
As you can see in this image, running the CHECKDB command with NO_INFOMSGS option displays only the problematic part, i.e., the consistency errors found by the command. So now you can proceed with fixing the errors. Check out a post that advises on how to fix DBCC CHECKDB consistency errors.
Essentially, using CHECKDB with NO_INFOMSGS can cut down the processing time considerably when integrity checks are performed on small databases in SQL Server Management Studio (SSMS).
Note: that using NO_INFOMSGS does not minimize a performance issue but can prevent you from running the integrity checks again by showing you only the errors. Also, use this option for small databases. For larger databases (1 TB or more), consider using other *options.
*Besides NO_INFOMSGS, you can use DBCC CHECKDB with the following parameters:
DATA_PURITY
Use this option to check data integrity in a column, particularly for databases upgraded from previous versions of SQL Server.
Note: DBCC CHECKDB performs Column-value integrity checks by default. You will need to use the DATA_PURITY option for upgraded databases.
Run the following command to run DBCC CHECKDB with the DATA_PURITY option:
DBCC CHECKDB(Stellar_TestDB) with DATA_PURITY;
EXTENDED_LOGICAL_CHECKS
This makes DBCC CHECKDB performs logical checks on indexed views, spatial indexes, or XML indexes. The syntax is as follows:
DBCC CHECKDB(Stellar_TestDB) with EXTENDED_LOGICAL_CHECKS;
Note: Microsoft recommends performing EXTENDED_LOGICAL_CHECKS only on databases you suspect to be corrupted or if you've hit a corruption bug. However, if you are running mission-critical databases, you must use this option to check XML indexes and spatial indexes.
In SQL Server 2016, running the DBCC CHECKDB command with the EXTENDED_LOGICAL_CHECKS option may fail on a table that has a "filtered nonclustered index (NCI) over a clustered columnstore index (CCI)". Also, it will result in SQL error 5297. Find more information about this SQL bug here.
PHYSICAL_ONLY
You can reduce the overhead of DBCC CHECKDB and make it faster for larger databases using it with "PHYSICAL_ONLY" option. DBCC CHECKDB skips all the logical checks and limits the checking to physical structure of a page. It also helps page checksums for failure, torn pages, and common hardware failures that can lead to data corruption. To use DBCC CHECKDB with PHYSICAL_ONLY, use this syntax:
DBCC CHECKDB(Stellar_TestDB) with PHYSICAL_ONLY;
Other Best Practices to Optimize DBCC CHECKDB Usage
1. Use Trace Flags 2562 and 2549
Microsoft recommends using the 2562 and 2549 trace flags to improve disk I/O resource usage while executing the DBCC CHECKDB command with the PHYSICAL_ONLY option.
- Trace flag 2562: It optimizes DBCC CHCKDB performance by running the command as a single batch. Further, it helps determine the pages to be read from a database. However, one problem associated with this trace flag is that it may increase the utilization of tempdb (i.e., 5 percent of the database size).
- Trace flag 2549: Using this trace flag helps improve performance of the DBCC CHECKDB command. It does so by assuming that each database file resides on a unique underlying disk. Do not use this trace flag for the database that has multiple data files.
Refer to this link to find more details about these trace flags.
2. Optimize TempDB
DBCC CHECKDB can make heavy usage of temporary database (TempDB). To properly utilize it, put TempDB on a fast I/O subsystem. Essentially, put the TempDB database on its own set of fast disks or spindles.
3. Cut Down on CPU Usage
If you can run DBCC CHECKDB for a longer duration to limit CPU usage, try reducing parallelism by following these practices:
- Turn off parallelism for DBCC CHECKDB (and its commands CHECKFILEGROUP and CHECKTABLE) by using trace flag 2528.
- Use the SQL Server maximum degree of parallelism option to limit the number of processors to be used for parallel queries.
4 min read
