dba.se - q/220173/dbcc-checktable

##foo.csv

how-long-does-checktable-run-at-load.sql

select 128222 as cs, '21820 ms elapsed' as ts, getutcdate() as now into ##foo;
while 1=1
begin
    declare @now datetime2 = getutcdate();
    dbcc checktable(x) with no_infomsgs;

    insert ##foo
    select cs = count(*)  -- 2159
          ,ts = convert(varchar(100),datediff(millisecond,@now,getutcdate()))+' ms elapsed' 
          ,now =  getutcdate()
    from sys.dm_db_partition_stats;
-- 18000
-- 21000
-- 
-- 
end
go

partition-checktable.sql

-- https://dba.stackexchange.com/questions/220173/dbcc-checktable-taking-15-minutes-to-run-on-an-empty-table/

use [master]
drop database if exists dbase;
create database dbase;
alter authorization on database::dbase to sa;
go
use dbase
create table x ( i int );
go
declare @now datetime2 = getutcdate();
dbcc checktable(x);
print convert(varchar(100),datediff(millisecond,@now,getutcdate()))+' ms elapsed' -- 374
go
declare @sql nvarchar(max) =  N'
create partition function pf (int) 
as range right for values (
';
with cte as ( 
    select 1 as i
    union all
    select i + 1
    from cte
    where i < 2000
)
select @sql = @sql + string_agg(convert(nvarchar(max),i),N',') + convert(nvarchar(max),N'
);')
from cte
option (maxrecursion 0);
exec sp_executesql @sql;
go
create partition scheme ps as partition pf all to ([PRIMARY]);
select cs = count(*) from sys.dm_db_partition_stats; -- 158
go
set nocount on;

declare @i int = 1
       ,@sql nvarchar(max);

while @i < 200
begin 
    set @i+=1;
    raiserror ('%i',0,0,@i) with nowait;
    
    set @sql = N'create table t'+convert(nvarchar(10),@i)+' ( i int ) on ps ( i );';

    exec sp_executesql @sql;

    set @sql = N'
with cte as ( 
    select 1 as i
    union all
    select i + 1
    from cte
    where i < 2000
)
insert t'+convert(nvarchar(10),@i)+' (i)
select i
from cte
option (maxrecursion 0);';

    exec sp_executesql @sql;
end
go
select cs = count(*) from sys.dm_db_partition_stats; -- 2159
declare @now datetime2 = getutcdate();
dbcc checktable(x);
print convert(varchar(100),datediff(millisecond,@now,getutcdate()))+' ms elapsed' -- 4123
go
create table z ( i int ) on ps ( i );
go
with cte as ( 
    select 1 as i
    union all
    select i + 1
    from cte
    where i < 2000
)
insert z (i)
select i
from cte
option (maxrecursion 0);
go
select cs = count(*) from sys.dm_db_partition_stats; -- 4160
declare @now datetime2 = getutcdate();
dbcc checktable(x);
print convert(varchar(100),datediff(millisecond,@now,getutcdate()))+' ms elapsed' -- 443
go
backup database dbase 
    to disk = 'c:\temp\dbase.bak'
    with compression, checksum;
go
use [master]
drop database if exists dbase;

select-star-from-foo.sql

set nocount on
select * from ##foo

backup zipped to petervandivier/hello-world@6b6398b

approx 30GB uncompressed