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.
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
Actual Scenarios
SQL Server 2016 has been vetted by a wide range of customers. The positive impact of these changes has been realized by:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.