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.

 

clip_image001

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 (9)

  1. Very cool… keep them coming…

  2. Pedro B says:

    Cool! Another reason for going directly to 2016.

  3. Brian Beuning says:

    This looks like a very cool speed up. But your benchmark does not address an
    issue we have been having with SQL Server. I would like to see a benchmark
    that shows that an application running gets about the same response time with
    or without DBCC running.

    Correctness before optimization. In my work, application response time is part
    of correctness.

    Thanks,

  4. jared says:

    Very nice – Keep up the great work!!

  5. Neeraj Mittal says:

    Excellent !! Breather in reducing maintenance time on Giant tables/DB

  6. Chris Wood says:

    Does this improvement work if the database on a SQL2016 server is from a lower version IE SQL2012 with compatability level 110?

    Chris

  7. Bob Dorr says:

    Database version does not matter. This is an internal change on how we access the raw data and build facts.

    1. Chris Wood says:

      Great,

      Just checking that for a SQL2012 SP3 set of databases on SQL2016 RC2 server.

  8. Markus Bohse says:

    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.

Skip to main content