DBCC Scales 7x Better in SQL Server 2016

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:

  • Filtered indexes
  • Persisted Computed columns
  • UDT 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.

 

Advertisements

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