SQL Server 2016 vs 2012 insert performance

Question

• 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?

Answer 1

Obviously it is very difficult the coincidence of having exactly the same versions in the same server but... I hope my results will help you. I have two different machines configured both Windows Server 2012 R2 Standard. Unfortunately they don't have the same hardware but similar:

1. Machine 1 (SQL Server 2016)

   • Processor: Intel(R) Xeon(r) CPU X5650 @2.67GHz
   • Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: ) (Hypervisor)

2. Machine 2 (SQL Server 2012)

   • Processor: Intel(R) Xeon(R) CPU E5-2667 0 @ 2.9 GHz
   • Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)

And I run 5 times in both machines the same script you have provided and obtained the following average:

• 2012: 9961
• 2016: 8971

What is almost the opposite results that you obtain. Anyway, as you have seen, my 2012 machine has better processor but the hard disk that normally is what shows the difference is the same. So, even if 2012 has better resources is a bit slower for my case.

(Sorry, please, check again, my first version had some important erratums)

Answer 2

Can you add (TABLOCK) hint to enable minimally logging ? What is the difference after applying this hint?

INSERT INTO t_heap WITH (TABLOCK) 
SELECT * 
FROM t_source