Sql-server – SQL Server 2016 vs 2012 insert performance

• I have two SQL Server instances on the same server:

  • Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64) Standard Edition
    (64-bit)
  • Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) –
    13.0.4451.0 (X64) Enterprise Edition (64-bit)

• sp_configure results are the same on both instances (except of new 2016 options).

• I have created new databases on both instances on the same disk folder. Autogrowth parameters are the same.

• Autocreate and autoupdate statistics options are turned off.

• Then I have made a test with 10000 inserts into a heap:

set nocount on
go

create table dbo.TestInsert ( i int not null, s varchar(50) not null )

declare @d1 datetime, @d2 datetime, @i int
set @d1 = getdate() 
set @i = 1

while @i <= 10000
begin
  insert into dbo.TestInsert ( i, s ) select @i, replicate( 'a', 50 )
  set @i = @i + 1
end

set @d2 = getdate()
select datediff(ms, @d1, @d2)
drop table dbo.TestInsert

Result 1

The average test time

• 2012 – 530 ms
• 2016 – 600 ms

So, 2016 is about 11% slower.

• Then I have made an SQL Profiler trace with results saved to the table to see the single insert duration in microseconds.

Result 2

The histogram of a single insert duration 2012 vs 2016:

Transaction logs' growth from sys.dm_io_virtual_file_stats is:

• 2012 – 5174784 bytes
• 2016 – 5171200 bytes

During these tests both instances are started. But one test is running only at the one instance each time. I have allocated 8Gb RAM per instance. The query plans are the same. It would be interesting to run each instance on its own box. But probably the one machine is better, because here we have no hardware and enviromnent hidden differences.

Questions

• Why is 2016 slower?
• May someone reproduce this test?