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
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.
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.
- Why is 2016 slower?
- May someone reproduce this test?