In a recent article from Microsoft, we learn that changes to the way DBCC operates allow this important utility to run up to 7 times faster in SQL Server 2016 than in SQL Server 2005. Some customer workloads in older SQL Server instances have reported 10x slower performance for CHECKDB if the database has filtered indexes, if the database tables being checked contain one of the following data types, or specific indexes:
Persisted Computed columns
UDT columns based on CLR assemblies (such as clearing has_unchecked_assembly_data value)
In this example, they compare the results between SQL Server 2014 and SQL Server 2016:
Sample Results (7 times faster)
|Machine||32GB RAM, 4 Core Hyper-threaded enabled 2.8Ghz, SSD Storage|
|SQL Server||Out of the box, default installation|
|SQL Server 2014||12880ms|
|SQL Server 2016||1676ms|
Internally DBCC CHECK* uses a page scanning coordinator design (MultiObjectScanner.) SQL Server 2016 changes the internal design to (CheckScanner), applying no lock semantics and a design similar to those used with In-Memory Optimized (Hekaton) objects, allowing DBCC operations to scale far better than previous releases.
The following chart shows the same 1TB database testing.
- MultiObjectScanner = Older design
- CheckScanner = New design
The visual is powerful, showing the older design does not scale and with more than 8 DOP CPUs, significant negative scaling occurs while the new design provides far better results.