Faster DBCC CHECKDB with SQL Server 2016


By:   |   Updated: 2016-09-08   |   Comments (3)   |   Related: More > Database Console Commands DBCCs

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:

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


Last Updated: 2016-09-08


get scripts

next tip button



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.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

 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

 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,

Ahmad

 


Friday, September 09, 2016 - 4:02:38 PM - Ameena Lalani Back To Top

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



download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools