Faster DBCC CHECKDB with SQL Server 2016

By:   |   Comments (3)   |   Related: > Database Console Commands DBCCs


Data consistency is one of the main factors clients depend on when deciding which database product they will trust to store their data. Microsoft SQL Server can be considered as the best choice for most of clients, as it maintains the database consistency, integrity and availability. DBCC CHECKDB is a built-in SQL Server command that allows you to check the database integrity and consistency in one simple command. And as a SQL Server DBA, you should make sure to run that command regularly to detect and fix any data issues in the databases you administer. But running the DBCC CHECKDB on large databases is a nightmare for the Database Administrators, as it may require a long time to complete and slow down the systems.  Are there any enhancements to the DBCC CHECKDB command in SQL Server 2016?


The DBCC CHECKDB command is mainly used to make sure that your databases are storing consistent data, and let you directly allocate and fix any corruption in these databases. But running that command to check the integrity and consistency of a large database, or a database with tables containing Persisted Computed columns, Filtered Indexes or UDT columns will take a long time, consume SQL Server resources and cause slowness to any user connecting to that database, as the DBCC CHECKDB command will scan the database page-by-page and the Persisted Computed column, UDT column and Filter Index values will be computed again.

The old methods that we used to follow to overcome the DBCC CHECKDB performance effects, are running that command less frequent, using the PHYSICAL_ONLY option to skip the logical consistency check, disabling the large tablesí indexes before running that command, which required rebuilding all disabled indexes or backing up and restoring the database to a test environment, then running DBCC CHECKDB.

SQL Server 2016 introduces a new enhancement in the DBCC CHECKDB command that includes skipping the Persisted columns, Filtered Indexes and UDT columns checks, minimizing the command's expensive cost and long duration. If you need to check the consistency for objects in addition to the default consistency check, you need to provide the EXTENDED_LOGICAL_CHECKS option to the DBCC CHECKDB. The physical consistency check for these expensive objects will always be performed.

Compare SQL Server DBCC CHECKDB in SQL Server 2016 vs. 2014

Letís work through a small demo to see these enhancements in practice. We will apply a DBCC CHECKDB command on two SQL server instances located in the same machine, hosting the same MSSQLTipsDemo database. The first instance with SQL Server 2014 and the second one is SQL Server 2016.

To prepare for the demo, the STATISTICS TIME will be turned on to get the elapsed time and CPU time for the queries. The elapsed time is also available in the result window by subtracting the start time from the current time once the query is completed.

The final query that will check the MSSQLTipsDemo database consistency and integrity will be:

SELECT @@version AS SQLServerVersion
SET @StartTime = GETDATE();
SELECT DATEDIFF(ms, @StartTime, GETDATE()) as ElapsedTime

Running the query on a SQL Server 2014 instance, the result will be:

SQL Server DBCC CHECKDB run on SQL Server 2014

Also the STATISTICS TIME result from the output message will be:

DBCC CHECKDB run on SQL Server 2014 to calculate the CPU and Elapsed Time

Now, we will run the query on a SQL Server 2016 instance, the result will be like:

SQL Server DBCC CHECKDB run on SQL Server 2016

And the STATISTICS TIME result from the output message will be:

DBCC CHECKDB run on SQL Server 2016 to calculate the CPU and Elapsed Time

You can easily see the major performance enhancement for the DBCC CHECKDB command in SQL Server 2016 from the previous results. Where the command took about 446ms using SQL Server 2016 and the same command took 2903ms in SQL Server 2014 for the same database.  This is a 6 times improvement. The CPU time variation also is clear in the demo, where the query consumed 188ms from the CPU time using SQL Server 2016 and 2281ms from the CPU time using SQL Server 2014, a 12 times improvement.

This enhancement is gained when running the DBCC CHECKDB command on a simple database using my personal machine. So you can imagine the big difference with the DBCC CHECKDB command on your large production databases with enterprise class hardware.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, September 28, 2016 - 11:19:00 AM - Fraz Malik Back To Top (43438)

 Excellent article and good examples. Thanks for sharing DBCC CHECKDB improvements in SQL Server 2016 with the group.


Saturday, September 10, 2016 - 1:16:49 AM - Ahmad Yaseen Back To Top (43295)

 Hi Ameena,

Thank you for your comment here. Please note that this database is a test database that i used to build its component each time i use it. So just create a new database with that name and proceed with the scripts in the tip.


Best Regards,



Friday, September 9, 2016 - 4:02:38 PM - Ameena Lalani Back To Top (43290)

 Is MSSQLTIPSDemo is sample database? If yes, how can I download it? Thanks.

get free sql tips
agree to terms