Topic: Time comparing insert in 2012 vs 2016
It is given
On one server stand equally configured 2012 and 2016:
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) A Standard Edition (64-bit)
Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) AN Enterprise Edition (64-bit)
2. sp_configure on identical (to within new to 2016 options).
3. On everyone there is the identical empty DB aimed at the same folder on a disk, the initial size and increment parameters are put the identical.
4. In a DB autocreation and update of statistics are disconnected.
5. The test with insert 10 thousand lines in the table (I give an example with a heap, but it is not basic) is fulfilled:
set nocount on go exec (' 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) exec (' drop table TestInsert ')
Lag in 2016 is stablly played back.
On the average in tests duration on 2012 = 530 msec, duration on 2016 = 600 msec.
6. It is adjusted for sampling statistics collection (the filter on TextData like <ours insert>) in the table (i.e. we will look duration in microseconds).
The histogram separate insert 2012 vs 2016: