SQL 2016 - It Just Runs Faster: DBCC Scales 7x Better

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.

 

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.

 

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.)

 

Ryan Stonecipher - Principle SQL Server Software Engineer

Bob Dorr - Principal SQL Server Escalation Engineer

 

 

DEMO - It Just Runs: DBCC CheckDB

 

Overview

The DBCC CheckDB demonstration loads a table and demonstrates the performance improvement.

 

Steps

1. Use SQL Server Management Studio (SSMS) or your favorite query editor to connect to a SQL Server 2012 or 2014 instance.
2. Paste the script below in a new query window
3. Execute (ATL+X) the script and take note of the elapsed execution time.

 

1. 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.
    

Actual Scenarios

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.

 

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

 

--------------------------------------

-- Demonstration showing performance of CheckDB

--------------------------------------

use tempdb

go

 

set nocount on

go

 

if(0 <> (select count(*) from tempdb.sys.objects where name = 'tblDBCC') )

begin

drop table tblDBCC

end

go

 

create table tblDBCC

(

iID int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,

strData nvarchar(2000) NOT NULL

)

go

 

-- Insert data to expand to a table that allows DOP activities

print 'Populating Data'

go

 

begin tran

go

 

insert into tblDBCC (strData) values ( replicate(N'X', 2000) )

while(SCOPE_IDENTITY() < 100000)

begin

insert into tblDBCC (strData) values ( replicate(N'X', 2000) )

end

go

 

commit tran

go

 

--------------------------------------

-- CheckDB

--------------------------------------

declare @dtStart datetime

set @dtStart = GETUTCDATE();

dbcc checkdb(tempdb)

select datediff(ms, @dtStart, GetUTCDate()) as [Elapsed DBCC checkdb (ms)]

go

Comments

• Anonymous
  February 25, 2016
  Very cool... keep them coming...
• Anonymous
  February 25, 2016
  Cool! Another reason for going directly to 2016.
• Anonymous
  February 29, 2016
  This looks like a very cool speed up. But your benchmark does not address anissue we have been having with SQL Server. I would like to see a benchmarkthat shows that an application running gets about the same response time withor without DBCC running.Correctness before optimization. In my work, application response time is partof correctness.Thanks,
• Anonymous
  March 28, 2016
  Very nice - Keep up the great work!!
• Anonymous
  April 27, 2016
  Excellent !! Breather in reducing maintenance time on Giant tables/DB
• Anonymous
  May 26, 2016
  Does this improvement work if the database on a SQL2016 server is from a lower version IE SQL2012 with compatability level 110?Chris
• Anonymous
  May 26, 2016
  Database version does not matter. This is an internal change on how we access the raw data and build facts.
  • Anonymous
    May 26, 2016
    Great,Just checking that for a SQL2012 SP3 set of databases on SQL2016 RC2 server.
• Anonymous
  November 25, 2016
  Has this also been changed for SQL 2014 SP2 or higher?I'm asking because when I run the demo script against my 2014 instance (12.0.5000) I get the same speed as on my 2016 instance.