Note: updated 11/15/2021 to address comment feedback.
It seems SQL Server 2016 has some odd little corners when it comes to performance.
Version:
Microsoft SQL Server 2016 (SP2-CU12) (KB4536648) - 13.0.5698.0 (X64) Feb 15 2020 01:47:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)
I have identified a case where doing a select into runs in time 2886ms. Running the same query but doing an insert into an existing table takes approximately 10 times as long (20717ms). If I do a select into to a temporary table and then an insert into from a select * on the temporary table the total run time is on the same order as doing a select into (3167ms).
The destination table in all cases is a heap with no indexes which is created using select into.
The stats for the insert into with the full select query are causing excessive reads for the mysterious "Worktable" object:
Table 'Worktable'. Scan count 1, logical reads 197253, physical reads 1, read-ahead reads 0, lob logical reads 3500000, lob physical reads 0, lob read-ahead reads 0.
The analogous stats in the other two cases are all zeros.
The select into generates this query plan (Paste The Plan: https://www.brentozar.com/PasteThePlan/?id=HkiRkme_t):
And this is the query plan for the insert into (Paste The Plan: https://www.brentozar.com/pastetheplan/?id=BJnrBQldK):
The little caution icon has this message:
The query memory grant detected "ExcessiveGrant", which may impact the reliability. Grant size: Initial 1024 KB, Final 1024 KB, Used 0 KB.
Here is the SQL I'm using:
create table dbo.nb_test(batch_id varchar(100), record_number bigint, record_data varchar(max), primary key (batch_id, record_number))
declare @batch int = 1
while @batch <= 10
begin
set @batch += 1
;with e1(n) as
(
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1
), -- 10
e2(n) as (select 1 from e1 cross join e1 as b),
e3(n) as (select 1 from e1 cross join e2),
e4(n) as (select 1 from e2 cross join e3),
e100k as (select n = ROW_NUMBER() over (order by n) from e4)
insert into dbo.nb_test
select cast('batch-' + cast(@batch as varchar(100)) as varchar(100)) as batch_id
, cast(n as bigint) as record_number
, '[["' + + cast(n as varchar(max)) + '"' + replicate(',"some data"', 99) + ']]' as record_data
from e100k
end
select max(record_number), count(*) from dbo.nb_test
set statistics io on
set statistics time on
drop table if exists dbo.nb_parsed
go
print '
*********************** select into'
go
select batch_id, record_number, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30, f31, f32, f33, f34, f35, f36, f37, f38, f39, f40, f41, f42, f43, f44, f45, f46, f47, f48, f49, f50, f51, f52, f53, f54, f55, f56, f57, f58, f59, f60, f61, f62, f63, f64, f65, f66, f67, f68, f69, f70
into dbo.nb_parsed
from (
select rd.batch_id, rd.record_number, ca.*
from dbo.nb_test rd
cross apply openjson(record_data)
with (f1 nvarchar(max) '$[1]', f2 nvarchar(max) '$[2]', f3 nvarchar(max) '$[3]', f4 nvarchar(max) '$[4]', f5 nvarchar(max) '$[5]', f6 nvarchar(max) '$[6]', f7 nvarchar(max) '$[7]', f8 nvarchar(max) '$[8]', f9 nvarchar(max) '$[9]', f10 nvarchar(max) '$[10]', f11 nvarchar(max) '$[11]', f12 nvarchar(max) '$[12]', f13 nvarchar(max) '$[13]', f14 nvarchar(max) '$[14]', f15 nvarchar(max) '$[15]', f16 nvarchar(max) '$[16]', f17 nvarchar(max) '$[17]', f18 nvarchar(max) '$[18]', f19 nvarchar(max) '$[19]', f20 nvarchar(max) '$[20]', f21 nvarchar(max) '$[21]', f22 nvarchar(max) '$[22]', f23 nvarchar(max) '$[23]', f24 nvarchar(max) '$[24]', f25 nvarchar(max) '$[25]', f26 nvarchar(max) '$[26]', f27 nvarchar(max) '$[27]', f28 nvarchar(max) '$[28]', f29 nvarchar(max) '$[29]', f30 nvarchar(max) '$[30]', f31 nvarchar(max) '$[31]', f32 nvarchar(max) '$[32]', f33 nvarchar(max) '$[33]', f34 nvarchar(max) '$[34]', f35 nvarchar(max) '$[35]', f36 nvarchar(max) '$[36]', f37 nvarchar(max) '$[37]', f38 nvarchar(max) '$[38]', f39 nvarchar(max) '$[39]', f40 nvarchar(max) '$[40]', f41 nvarchar(max) '$[41]', f42 nvarchar(max) '$[42]', f43 nvarchar(max) '$[43]', f44 nvarchar(max) '$[44]', f45 nvarchar(max) '$[45]', f46 nvarchar(max) '$[46]', f47 nvarchar(max) '$[47]', f48 nvarchar(max) '$[48]', f49 nvarchar(max) '$[49]', f50 nvarchar(max) '$[50]', f51 nvarchar(max) '$[51]', f52 nvarchar(max) '$[52]', f53 nvarchar(max) '$[53]', f54 nvarchar(max) '$[54]', f55 nvarchar(max) '$[55]', f56 nvarchar(max) '$[56]', f57 nvarchar(max) '$[57]', f58 nvarchar(max) '$[58]', f59 nvarchar(max) '$[59]', f60 nvarchar(max) '$[60]', f61 nvarchar(max) '$[61]', f62 nvarchar(max) '$[62]', f63 nvarchar(max) '$[63]', f64 nvarchar(max) '$[64]', f65 nvarchar(max) '$[65]', f66 nvarchar(max) '$[66]', f67 nvarchar(max) '$[67]', f68 nvarchar(max) '$[68]', f69 nvarchar(max) '$[69]', f70 nvarchar(max) '$[70]') as ca
where rd.batch_id = 'batch-5' and rd.record_number between 1 and 50000
) data
go
print '
*********************** insert into inner'
go
insert into dbo.nb_parsed with(tablock)
select batch_id, record_number, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30, f31, f32, f33, f34, f35, f36, f37, f38, f39, f40, f41, f42, f43, f44, f45, f46, f47, f48, f49, f50, f51, f52, f53, f54, f55, f56, f57, f58, f59, f60, f61, f62, f63, f64, f65, f66, f67, f68, f69, f70
from (
select rd.batch_id, rd.record_number, ca.*
from dbo.nb_test rd
cross apply openjson(record_data)
with (f1 nvarchar(max) '$[1]', f2 nvarchar(max) '$[2]', f3 nvarchar(max) '$[3]', f4 nvarchar(max) '$[4]', f5 nvarchar(max) '$[5]', f6 nvarchar(max) '$[6]', f7 nvarchar(max) '$[7]', f8 nvarchar(max) '$[8]', f9 nvarchar(max) '$[9]', f10 nvarchar(max) '$[10]', f11 nvarchar(max) '$[11]', f12 nvarchar(max) '$[12]', f13 nvarchar(max) '$[13]', f14 nvarchar(max) '$[14]', f15 nvarchar(max) '$[15]', f16 nvarchar(max) '$[16]', f17 nvarchar(max) '$[17]', f18 nvarchar(max) '$[18]', f19 nvarchar(max) '$[19]', f20 nvarchar(max) '$[20]', f21 nvarchar(max) '$[21]', f22 nvarchar(max) '$[22]', f23 nvarchar(max) '$[23]', f24 nvarchar(max) '$[24]', f25 nvarchar(max) '$[25]', f26 nvarchar(max) '$[26]', f27 nvarchar(max) '$[27]', f28 nvarchar(max) '$[28]', f29 nvarchar(max) '$[29]', f30 nvarchar(max) '$[30]', f31 nvarchar(max) '$[31]', f32 nvarchar(max) '$[32]', f33 nvarchar(max) '$[33]', f34 nvarchar(max) '$[34]', f35 nvarchar(max) '$[35]', f36 nvarchar(max) '$[36]', f37 nvarchar(max) '$[37]', f38 nvarchar(max) '$[38]', f39 nvarchar(max) '$[39]', f40 nvarchar(max) '$[40]', f41 nvarchar(max) '$[41]', f42 nvarchar(max) '$[42]', f43 nvarchar(max) '$[43]', f44 nvarchar(max) '$[44]', f45 nvarchar(max) '$[45]', f46 nvarchar(max) '$[46]', f47 nvarchar(max) '$[47]', f48 nvarchar(max) '$[48]', f49 nvarchar(max) '$[49]', f50 nvarchar(max) '$[50]', f51 nvarchar(max) '$[51]', f52 nvarchar(max) '$[52]', f53 nvarchar(max) '$[53]', f54 nvarchar(max) '$[54]', f55 nvarchar(max) '$[55]', f56 nvarchar(max) '$[56]', f57 nvarchar(max) '$[57]', f58 nvarchar(max) '$[58]', f59 nvarchar(max) '$[59]', f60 nvarchar(max) '$[60]', f61 nvarchar(max) '$[61]', f62 nvarchar(max) '$[62]', f63 nvarchar(max) '$[63]', f64 nvarchar(max) '$[64]', f65 nvarchar(max) '$[65]', f66 nvarchar(max) '$[66]', f67 nvarchar(max) '$[67]', f68 nvarchar(max) '$[68]', f69 nvarchar(max) '$[69]', f70 nvarchar(max) '$[70]') as ca
where rd.batch_id = 'batch-5' and rd.record_number between 1 and 50000
) data
go
drop table if exists #t
go
print '
*********************** select into temp'
go
select batch_id, record_number, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12, f13, f14, f15, f16, f17, f18, f19, f20, f21, f22, f23, f24, f25, f26, f27, f28, f29, f30, f31, f32, f33, f34, f35, f36, f37, f38, f39, f40, f41, f42, f43, f44, f45, f46, f47, f48, f49, f50, f51, f52, f53, f54, f55, f56, f57, f58, f59, f60, f61, f62, f63, f64, f65, f66, f67, f68, f69, f70
into #t
from (
select rd.batch_id, rd.record_number, ca.*
from dbo.nb_test rd
cross apply openjson(record_data)
with (f1 nvarchar(max) '$[1]', f2 nvarchar(max) '$[2]', f3 nvarchar(max) '$[3]', f4 nvarchar(max) '$[4]', f5 nvarchar(max) '$[5]', f6 nvarchar(max) '$[6]', f7 nvarchar(max) '$[7]', f8 nvarchar(max) '$[8]', f9 nvarchar(max) '$[9]', f10 nvarchar(max) '$[10]', f11 nvarchar(max) '$[11]', f12 nvarchar(max) '$[12]', f13 nvarchar(max) '$[13]', f14 nvarchar(max) '$[14]', f15 nvarchar(max) '$[15]', f16 nvarchar(max) '$[16]', f17 nvarchar(max) '$[17]', f18 nvarchar(max) '$[18]', f19 nvarchar(max) '$[19]', f20 nvarchar(max) '$[20]', f21 nvarchar(max) '$[21]', f22 nvarchar(max) '$[22]', f23 nvarchar(max) '$[23]', f24 nvarchar(max) '$[24]', f25 nvarchar(max) '$[25]', f26 nvarchar(max) '$[26]', f27 nvarchar(max) '$[27]', f28 nvarchar(max) '$[28]', f29 nvarchar(max) '$[29]', f30 nvarchar(max) '$[30]', f31 nvarchar(max) '$[31]', f32 nvarchar(max) '$[32]', f33 nvarchar(max) '$[33]', f34 nvarchar(max) '$[34]', f35 nvarchar(max) '$[35]', f36 nvarchar(max) '$[36]', f37 nvarchar(max) '$[37]', f38 nvarchar(max) '$[38]', f39 nvarchar(max) '$[39]', f40 nvarchar(max) '$[40]', f41 nvarchar(max) '$[41]', f42 nvarchar(max) '$[42]', f43 nvarchar(max) '$[43]', f44 nvarchar(max) '$[44]', f45 nvarchar(max) '$[45]', f46 nvarchar(max) '$[46]', f47 nvarchar(max) '$[47]', f48 nvarchar(max) '$[48]', f49 nvarchar(max) '$[49]', f50 nvarchar(max) '$[50]', f51 nvarchar(max) '$[51]', f52 nvarchar(max) '$[52]', f53 nvarchar(max) '$[53]', f54 nvarchar(max) '$[54]', f55 nvarchar(max) '$[55]', f56 nvarchar(max) '$[56]', f57 nvarchar(max) '$[57]', f58 nvarchar(max) '$[58]', f59 nvarchar(max) '$[59]', f60 nvarchar(max) '$[60]', f61 nvarchar(max) '$[61]', f62 nvarchar(max) '$[62]', f63 nvarchar(max) '$[63]', f64 nvarchar(max) '$[64]', f65 nvarchar(max) '$[65]', f66 nvarchar(max) '$[66]', f67 nvarchar(max) '$[67]', f68 nvarchar(max) '$[68]', f69 nvarchar(max) '$[69]', f70 nvarchar(max) '$[70]') as ca
where rd.batch_id = 'batch-5' and rd.record_number between 1 and 50000
) data
go
print '
*********************** insert from temp (tablock)'
go
insert into dbo.nb_parsed with(tablock)
select * from #t
go
Here are the mildly edited statistics:
*********************** select into
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'nb_parsed'. Scan count 0, logical reads 60009, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nb_test'. Scan count 5, logical reads 8985, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(50000 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 11062 ms, elapsed time = 2886 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
*********************** insert into inner
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 7 ms, elapsed time = 7 ms.
Table 'nb_parsed'. Scan count 0, logical reads 60001, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nb_test'. Scan count 1, logical reads 8400, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 197253, physical reads 1, read-ahead reads 0, lob logical reads 3500000, lob physical reads 0, lob read-ahead reads 0.
(50000 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 20422 ms, elapsed time = 20717 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
*********************** select into temp
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 6 ms, elapsed time = 6 ms.
Table '#t__________________________________________________________________________________________________________________00000002D27D'. Scan count 0, logical reads 60004, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'nb_test'. Scan count 5, logical reads 8985, physical reads 0, read-ahead reads 796, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(50000 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 10845 ms, elapsed time = 2768 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
*********************** insert from temp (tablock)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table '#t__________________________________________________________________________________________________________________00000002D27D'. Scan count 5, logical reads 10002, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(50000 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 1453 ms, elapsed time = 399 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Completion time: 2021-11-12T18:21:48.5419289-05:00
'''
[1]: https://i.stack.imgur.com/xR3lM.png
[2]: https://i.stack.imgur.com/Ayx7F.png