Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
dba.se - q/220173/dbcc-checktable
cs ts now
128222 21820 ms elapsed 2019-06-06 08:14:02.407
162239 28326 ms elapsed 2019-06-06 08:14:39.107
166241 38966 ms elapsed 2019-06-06 08:15:19.487
172244 48630 ms elapsed 2019-06-06 08:16:09.833
178247 40980 ms elapsed 2019-06-06 08:16:52.537
182249 32990 ms elapsed 2019-06-06 08:17:27.120
188252 37053 ms elapsed 2019-06-06 08:18:05.903
192254 23653 ms elapsed 2019-06-06 08:18:31.337
194255 27570 ms elapsed 2019-06-06 08:19:00.740
198257 26883 ms elapsed 2019-06-06 08:19:29.483
204260 32354 ms elapsed 2019-06-06 08:20:04.000
208262 41260 ms elapsed 2019-06-06 08:20:47.647
214265 30996 ms elapsed 2019-06-06 08:21:21.047
216266 35947 ms elapsed 2019-06-06 08:21:59.523
220268 28790 ms elapsed 2019-06-06 08:22:30.737
226271 37900 ms elapsed 2019-06-06 08:23:11.157
232274 32353 ms elapsed 2019-06-06 08:23:46.123
242279 49777 ms elapsed 2019-06-06 08:24:38.623
246281 27323 ms elapsed 2019-06-06 08:25:08.673
252284 60873 ms elapsed 2019-06-06 08:26:12.467
256286 28880 ms elapsed 2019-06-06 08:26:44.220
262289 29903 ms elapsed 2019-06-06 08:27:17.493
268292 36626 ms elapsed 2019-06-06 08:27:57.377
274295 53997 ms elapsed 2019-06-06 08:28:54.580
284300 61354 ms elapsed 2019-06-06 08:29:59.630
290303 39317 ms elapsed 2019-06-06 08:30:45.153
296306 33017 ms elapsed 2019-06-06 08:31:21.780
302309 51943 ms elapsed 2019-06-06 08:32:17.363
310313 46414 ms elapsed 2019-06-06 08:33:07.460
316316 76127 ms elapsed 2019-06-06 08:34:27.570
320318 70887 ms elapsed 2019-06-06 08:35:42.470
326321 67230 ms elapsed 2019-06-06 08:36:55.017
334325 40443 ms elapsed 2019-06-06 08:37:39.687
342329 51816 ms elapsed 2019-06-06 08:38:36.167
348332 49760 ms elapsed 2019-06-06 08:39:30.460
354335 39654 ms elapsed 2019-06-06 08:40:14.760
360338 35634 ms elapsed 2019-06-06 08:40:55.140
368342 44764 ms elapsed 2019-06-06 08:41:44.990
372344 45333 ms elapsed 2019-06-06 08:42:35.543
376346 44426 ms elapsed 2019-06-06 08:43:24.967
382349 35274 ms elapsed 2019-06-06 08:44:09.560
388352 43343 ms elapsed 2019-06-06 08:44:58.273
392354 40763 ms elapsed 2019-06-06 08:45:48.743
396356 45160 ms elapsed 2019-06-06 08:46:39.240
398357 25940 ms elapsed 2019-06-06 08:47:10.690
398357 19594 ms elapsed 2019-06-06 08:47:35.650
398357 14713 ms elapsed 2019-06-06 08:47:55.683
398357 14367 ms elapsed 2019-06-06 08:48:15.533
398357 12020 ms elapsed 2019-06-06 08:48:32.950
398357 13537 ms elapsed 2019-06-06 08:48:51.830
398357 11830 ms elapsed 2019-06-06 08:49:08.973
398357 12474 ms elapsed 2019-06-06 08:49:26.740
398357 12100 ms elapsed 2019-06-06 08:49:44.240
398357 12050 ms elapsed 2019-06-06 08:50:01.643
398357 12520 ms elapsed 2019-06-06 08:50:19.420
398357 12296 ms elapsed 2019-06-06 08:50:37.067
398357 13076 ms elapsed 2019-06-06 08:50:55.447
398357 14090 ms elapsed 2019-06-06 08:51:14.910
398357 11886 ms elapsed 2019-06-06 08:51:32.557
398357 12103 ms elapsed 2019-06-06 08:51:49.937
398357 17630 ms elapsed 2019-06-06 08:52:12.937
398357 14710 ms elapsed 2019-06-06 08:52:33.220
398357 14433 ms elapsed 2019-06-06 08:52:52.903
398357 11646 ms elapsed 2019-06-06 08:53:10.317
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
-- 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;
set nocount on
select * from ##foo
@petervandivier

This comment has been minimized.

Copy link
Owner Author

petervandivier commented Jun 6, 2019

backup zipped to petervandivier/hello-world@6b6398b

approx 30GB uncompressed

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.