Minimizing SQL Server DBCC CHECKDB Performance Impacts

Overview

SQL Server is the most popular relational database management system, which is used by many organizations to maintain their data. Every user expects that data should be stored in a consistent and integrated form. SQL Server is equipped with an in-built DBCC command, which allow users to check the integrity of the database. One of the command, DBCC CHECKDB helps to check integrity and allocation of specific objects in a database. This command should be executed on a regular basis to check if there is any corruption issue in the database files. It will analyze the entire database and provides the report regarding consistency of files, integrity of indexes, catalog consistency. So, it can be considered as an alert to detect the corruption in the database. However, the performance related issues associated with this command are a major area of concern for most of the SQL users. The following section aims to discuss on how to Minimize Performance Impact of SQL Server DBCC CHECKDB.

Impact of DBCC CHECKDB on SQL Server Performance

Although DBCC CHECKDB command is quite useful command to check the consistency but a number of issues are associated with this command, which hampers the performance of SQL Server. The top three performance related issues faced due to DBCC CHECKDB include:

1. System Process Delay:

When DBCC CHECKDB command is issued, it performs a thorough page-by-page analysis of the database files. Therefore, it acquires many resources at a time that slow down other system processes.

2. Slow Backup Process:

Since backup is a bit-to-bit copy of the existing database, it consumes much of the allocated resources as well as I/O devices. The backup file has to maintain every header, so it consumes lots of time.

3. Slow Recovery Process:

During the database recovery process, a hidden screen shot is created by DBCC. However, the consistency check of the screen shot is much time taking and slows down the recovery process.

Ways to Minimize Performance Impact of SQL Server DBCC CHECKDB

DBCC CHECKDB command plays a crucial role in the corruption issues, so it cannot be avoided due to its impact on performance. However, users can opt for measures to reduce the impact on performance of SQL Server. Some of the practices that can be followed while using DBCC CHECKDB are:

1. TempDb Maintenance:

DBCC makes a continuous use of TempDB database. Hence, it is advisable to store the TempDB on its own spindles so that any of the I/O spooling process does not get disturbed due to database access.

2. Database Mirroring:

Users can create a mirror image of the database using Database Mirroring setup. This will allow users to run DBCC CHECKDB on that version of database. It will keep the performance of primary database intact.

3. Avoid Information Messages:

Make sure that you use use DBCC CHECKDB with WITH NO_INFOMSGS to suppress all the irrelevant outputs generated during DBCC processing. It will somehow improve the performance rate of command processing.

4. Use Trace Flags:

In order to improve the performance, DBCC CHECKDB can be used with the following trace flags:

  1. Trace Flag 2549: This trace flag optimizes the performance of SQL Server by treating each database file as it resides on a different disk. Thus, it makes a faster scan of the database files.
  2. Trace Flag 2562: This flag treats the entire database as a batch and makes the use of better page reading algorithms. It will lead to a faster database processing.

5. Reduce CPU Impact:

DBCC CHECKDB command supports multithreading by default. In order to limit its CPU usage, users can reduce the parallelism in following ways:

  • Trace Flag 2528 can be used to turn off parallelism during processing of DBCC CHECKDB.
  • User can use SQL Server in-built feature “Maximum Degree of Parallelism” to specify the limit of CPU allocation.

6. Use Optimized Options

On a regular basis, DBCC CHECKDB can be used with other attributes, which can optimize the DBCC processing. Various options such as PHYSICAL_ONLY combined with CHECKSUM allows user to evaluate only physical record, thus helps in saving time and resource allocation.

Conclusion

Database corruption is a major hack, which damages user’s relevant data. It is always advised to perform a regular check to detect any corruption related issue. DBCC CHECKDB command is a significant utility to acknowledge the corruption in database. Once a corruption issue is detected in the files, users can take immediate steps to perform database recovery. However, it is always advisable to maintain an updated backup, which can be used in case of database failure or database corruption.

Advertisements
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

One Response to Minimizing SQL Server DBCC CHECKDB Performance Impacts

  1. Avi says:

    Hi,

    nice article.
    question – do i need to enable those trace flag only when running the dbcc checkdb or they can be stay on all the time?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s