Many of you have experienced (MULTI_OBJECT_SCANNER* based) waits while running DBCC CHECKS*(checkdb, checktable, …)
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.
MultiObjectScanner = Older design
CheckScanner = New design
Note:In addition to the no lock semantics the CheckScanner leverages advanced read-ahead capabilities.The same read-ahead advancements are included in parallel scans of a heap.
‘It Just Runs Faster’– Out of the box SQL Server 2016 DBCC provides you better performance, scale while shrinking your maintenance window(s.)
DEMO – It Just Runs: DBCC CheckDB
The DBCC CheckDB demonstration loads a table and demonstrates the performance improvement.
Use SQL Server Management Studio (SSMS) or your favorite query editor to connect to a SQL Server 2012 or 2014 instance.
Paste the script below in a new query window
Execute (ATL+X) the script and take note of the elapsed execution time.
On the same hardware/machine repeat steps 1 thru 3 using an instance of SQL Server 2016 CTP 3.0 or newer release.
Note: You may need to execute the dbcc a second time so buffer cache is hot, eliminating I/O sub-system variants.
SQL Server 2016 has been vetted by a wide range of customers.The positive impact of these changes has been realized by:
Every customer can reduce their maintenance window because of the DBCC performance improvements
A World Wide Shipping company using was able to reduce their maintenance window from 20 hours to 5 using SQL Server 2016.
Significant reduction in the maintenance window for the world’s largest ERP provider.