Monday, February 28, 2022

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

Moved from: bobsql.com

 

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

 

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

 

Posted at https://sl.advdat.com/3Md76h8https://sl.advdat.com/3Md76h8
Labels: ,