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
- Use SQL Server Management Studio (SSMS) or your favorite query editor to connect to a SQL Server 2012 or 2014 instance.
- Paste the script below in a new query window
- Execute (ATL+X) the script and take note of the elapsed execution time.
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
August 13, 2016
WOW !!! - Anonymous
February 15, 2017
So it is now slower for 4 cores, so that it can be a lot faster on more expensive systems..... (Therefore if a company is just starting up and does not have much money, SQL Server is no longer aimed at them.....)- Anonymous
February 15, 2017
Sorry for any confusion. It is not 4 'CORES' but CPUs and the difference is minimal so SQL can meet your needs across a wide variety of hardware.
- Anonymous