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

Published Jan 15 2019 04:52 PM 164 Views
Microsoft
First published on MSDN on Feb 25, 2016

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

%3CLINGO-SUB%20id%3D%22lingo-sub-318689%22%20slang%3D%22en-US%22%3ESQL%202016%20-%20It%20Just%20Runs%20Faster%3A%20DBCC%20Scales%207x%20Better%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-318689%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Feb%2025%2C%202016%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EMany%20of%20you%20have%20experienced%20(MULTI_OBJECT_SCANNER*%20based)%20waits%20while%20running%20DBCC%20CHECKS*(checkdb%2C%20checktable%2C%20%E2%80%A6)%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EInternally%20DBCC%20CHECK*%20uses%20a%20page%20scanning%20coordinator%20design%20(MultiObjectScanner.)%26nbsp%3B%20SQL%20Server%202016%20changes%20the%20internal%20design%20to%20(CheckScanner)%2C%20applying%20no%20lock%20semantics%20and%20a%20design%20similar%20to%20those%20used%20with%20In-Memory%20Optimized%20(Hekaton)%20objects%2C%20allowing%20DBCC%20operations%20to%20scale%20far%20better%20than%20previous%20releases.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20following%20chart%20shows%20the%20same%201TB%20database%20testing.%3C%2FP%3E%0A%20%20%3CUL%3E%0A%20%20%20%3CLI%3EMultiObjectScanner%20%3D%20Older%20design%3C%2FLI%3E%0A%20%20%20%3CLI%3ECheckScanner%20%3D%20New%20design%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20visual%20is%20powerful%2C%20showing%20the%20older%20design%20does%20not%20scale%20and%20with%20more%20than%208%20DOP%20CPUs%2C%20significant%20negative%20scaling%20occurs%20while%20the%20new%20design%20provides%20far%20better%20results.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F68387iA487BCF7C7C57542%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3ENote%3A%20%3C%2FSTRONG%3E%20In%20addition%20to%20the%20no%20lock%20semantics%20the%20CheckScanner%20leverages%20advanced%20read-ahead%20capabilities.%26nbsp%3B%26nbsp%3B%20The%20same%20read-ahead%20advancements%20are%20included%20in%20parallel%20scans%20of%20a%20heap.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E'It%20Just%20Runs%20Faster'%20%3C%2FSTRONG%3E%20-%20Out%20of%20the%20box%20SQL%20Server%202016%20DBCC%20provides%20you%20better%20performance%2C%20scale%20while%20shrinking%20your%20maintenance%20window(s.)%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3ERyan%20Stonecipher%20-%20Principle%20SQL%20Server%20Software%20Engineer%3C%2FP%3E%0A%20%20%3CP%3EBob%20Dorr%20-%20Principal%20SQL%20Server%20Escalation%20Engineer%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20DEMO%20-%20It%20Just%20Runs%3A%20DBCC%20CheckDB%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Overview%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3EThe%20DBCC%20CheckDB%20demonstration%20loads%20a%20table%20and%20demonstrates%20the%20performance%20improvement.%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Steps%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3COL%3E%0A%20%20%20%3CLI%3EUse%20SQL%20Server%20Management%20Studio%20(SSMS)%20or%20your%20favorite%20query%20editor%20to%20connect%20to%20a%20SQL%20Server%202012%20or%202014%20instance.%20%3CBR%20%2F%3E%3C%2FLI%3E%0A%20%20%20%3CLI%3EPaste%20the%20script%20below%20in%20a%20new%20query%20window%20%3CBR%20%2F%3E%3C%2FLI%3E%0A%20%20%20%3CLI%3EExecute%20(ATL%2BX)%20the%20script%20and%20take%20note%20of%20the%20elapsed%20execution%20time.%3C%2FLI%3E%0A%20%20%3C%2FOL%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3COL%3E%0A%20%20%20%3CLI%3EOn%20the%20same%20hardware%2Fmachine%20repeat%20steps%201%20thru%203%20using%20an%20instance%20of%20SQL%20Server%202016%20CTP%203.0%20or%20newer%20release.%20%3CBR%20%2F%3E%20%3CBR%20%2F%3E%20%3CSTRONG%3ENote%3A%20%3C%2FSTRONG%3E%20You%20may%20need%20to%20execute%20the%20dbcc%20a%20second%20time%20so%20buffer%20cache%20is%20hot%2C%20eliminating%20I%2FO%20sub-system%20variants.%20%3CBR%20%2F%3E%3C%2FLI%3E%0A%20%20%3C%2FOL%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Actual%20Scenarios%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CP%3ESQL%20Server%202016%20has%20been%20vetted%20by%20a%20wide%20range%20of%20customers.%26nbsp%3B%26nbsp%3B%20The%20positive%20impact%20of%20these%20changes%20has%20been%20realized%20by%3A%20%3CBR%20%2F%3E%3C%2FP%3E%0A%20%20%3CUL%3E%0A%20%20%20%3CLI%3EEvery%20customer%20can%20reduce%20their%20maintenance%20window%20because%20of%20the%20DBCC%20performance%20improvements%20%3CBR%20%2F%3E%3C%2FLI%3E%0A%20%20%20%3CLI%3EA%20World%20Wide%20Shipping%20company%20using%20was%20able%20to%20reduce%20their%20maintenance%20window%20from%2020%20hours%20to%205%20using%20SQL%20Server%202016.%20%3CBR%20%2F%3E%3C%2FLI%3E%0A%20%20%20%3CLI%3ESignificant%20reduction%20in%20the%20maintenance%20window%20for%20the%20world's%20largest%20ERP%20provider.%20%3CBR%20%2F%3E%3C%2FLI%3E%0A%20%20%3C%2FUL%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E%3CSTRONG%3E%20Sample%20Results%26nbsp%3B%20(7%20times%20faster)%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%20%20%3CDIV%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3EMachine%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E32GB%20RAM%2C%204%20Core%20Hyper-threaded%20enabled%202.8Ghz%2C%20SSD%20Storage%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3ESQL%20Server%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3EOut%20of%20the%20box%2C%20default%20installation%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3C%2FDIV%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CDIV%3E%0A%20%20%20%3CTABLE%3E%0A%20%20%20%20%3CTBODY%3E%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3ESQL%20Server%202014%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E12880ms%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%20%3CTR%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3ESQL%20Server%202016%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%20%3CTD%3E%3CP%3E1676ms%3C%2FP%3E%0A%20%20%20%20%20%3C%2FTD%3E%0A%20%20%20%20%3C%2FTR%3E%0A%20%20%20%3C%2FTBODY%3E%3C%2FTABLE%3E%0A%20%20%3C%2FDIV%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E--------------------------------------%3C%2FP%3E%0A%20%20%3CP%3E--%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Demonstration%20showing%20performance%20of%20CheckDB%3C%2FP%3E%0A%20%20%3CP%3E--------------------------------------%3C%2FP%3E%0A%20%20%3CP%3Euse%20tempdb%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Eset%20nocount%20on%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Eif(0%20%26lt%3B%26gt%3B%20(select%20count(*)%20from%20tempdb.sys.objects%20where%20name%20%3D%20'tblDBCC')%20)%3C%2FP%3E%0A%20%20%3CP%3Ebegin%3C%2FP%3E%0A%20%20%3CP%3Edrop%20table%20tblDBCC%3C%2FP%3E%0A%20%20%3CP%3Eend%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Ecreate%20table%20tblDBCC%3C%2FP%3E%0A%20%20%3CP%3E(%3C%2FP%3E%0A%20%20%3CP%3EiID%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20int%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20NOT%20NULL%20IDENTITY(1%2C1)%20PRIMARY%20KEY%20CLUSTERED%2C%3C%2FP%3E%0A%20%20%3CP%3EstrData%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20nvarchar(2000)%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20NOT%20NULL%3C%2FP%3E%0A%20%20%3CP%3E)%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E--%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Insert%20data%20to%20expand%20to%20a%20table%20that%20allows%20DOP%20activities%3C%2FP%3E%0A%20%20%3CP%3Eprint%20'Populating%26nbsp%3B%20Data'%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Ebegin%20tran%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Einsert%20into%20tblDBCC%20(strData)%20values%20(%20replicate(N'X'%2C%202000)%20)%3C%2FP%3E%0A%20%20%3CP%3Ewhile(SCOPE_IDENTITY()%20%26lt%3B%20100000)%3C%2FP%3E%0A%20%20%3CP%3Ebegin%3C%2FP%3E%0A%20%20%3CP%3Einsert%20into%20tblDBCC%20(strData)%20values%20(%20replicate(N'X'%2C%202000)%20)%3C%2FP%3E%0A%20%20%3CP%3Eend%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3Ecommit%20tran%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%20%3CP%3E%3C%2FP%3E%0A%20%20%3CP%3E--------------------------------------%3C%2FP%3E%0A%20%20%3CP%3E--%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20CheckDB%3C%2FP%3E%0A%20%20%3CP%3E--------------------------------------%3C%2FP%3E%0A%20%20%3CP%3Edeclare%20%40dtStart%20datetime%3C%2FP%3E%0A%20%20%3CP%3Eset%20%40dtStart%20%3D%20GETUTCDATE()%3B%3C%2FP%3E%0A%20%20%3CP%3Edbcc%20checkdb(tempdb)%3C%2FP%3E%0A%20%20%3CP%3Eselect%20datediff(ms%2C%20%40dtStart%2C%20GetUTCDate())%20as%20%5BElapsed%20DBCC%20checkdb%20(ms)%5D%3C%2FP%3E%0A%20%20%3CP%3Ego%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-318689%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Feb%2025%2C%202016%20Many%20of%20you%20have%20experienced%20(MULTI_OBJECT_SCANNER*%20based)%20waits%20while%20running%20DBCC%20CHECKS*(checkdb%2C%20checktable%2C%20%E2%80%A6)%20%26nbsp%3B%20Internally%20DBCC%20CHECK*%20uses%20a%20page%20scanning%20coordinator%20design%20(MultiObjectScanner.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-318689%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Esql%202016%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EStorage%20Engine%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Jan 15 2019 04:52 PM
Updated by: