Faster DBCC CHECKDB with SQL Server 2016

Problem

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?

Solution

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:

SET STATISTICS TIME ON
SELECT @@version AS SQLServerVersion
GO
DECLARE @StartTime DATETIME
SET @StartTime = GETDATE();
DBCC CHECKDB(MSSQLTipsDemo)
SELECT DATEDIFF(ms, @StartTime, GETDATE()) as ElapsedTime
GO
SET STATISTICS TIME OFF 

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

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

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

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

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

• Read more about Minimize performance impact of SQL Server DBCC CHECKDB.
• Check out Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor.
• Check also SQL Server 2016 Tips.