1

Topic: Time comparing insert in 2012 vs 2016

Good afternoon;
It is given
On one server stand equally configured  2012 and 2016:
1. Versions
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 ')

Result 1
Lag in 2016 is stablly played back.
On the average in tests duration on 2012 = 530 msec, duration on 2016 = 600 msec.
Further;
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).
Result 2
The histogram  separate insert 2012 vs 2016:

2

Re: Time comparing insert in 2012 vs 2016

Questions -
1. Whether someone can play back at itself(himself) similar?
2. What thoughts on lag 2016 are what to esteem?

3

Re: Time comparing insert in 2012 vs 2016

If to the code to add some more lines - will faster not work.
So here, 2016 it is added 2012.
For the extremely rare exception, new versions of a software more slowly old, for for . Gadgets it is necessary to pay.

4

Re: Time comparing insert in 2012 vs 2016

I will add still convergence:
Volumes of records in a broad gull through sys.dm_io_virtual_file_stats are approximately equal:

2012 - 5174784 byte
2016 - 5171200 byte

a cycle in transaction does not change the general ratio
One thick insert at once all 10  records on 2016 faster percent on 30.

5

Re: Time comparing insert in 2012 vs 2016

Try on 2016 to translate basis in compatibility level 2012. Also repeat insert'.

6

Re: Time comparing insert in 2012 vs 2016

Prolog wrote:

Try on 2016 to translate basis in compatibility level 2012. Also repeat insert'.

For single  any other plan is possible?

7

Re: Time comparing insert in 2012 vs 2016

o-o wrote:

it is passed...
For single  any other plan is possible?

Oh I do not know! However we had a noticeable degradation of productivity at passage on 2014. Recovered (according to the advice of Microsoft) transfer of a database into compatibility level 2012.

8

Re: Time comparing insert in 2012 vs 2016

Prolog wrote:

it is passed...
Oh I do not know! However we had a noticeable degradation of productivity at passage on 2014. Recovered (according to the advice of Microsoft) transfer of a database into compatibility level 2012.

You understand as degradation  executions apparently

9

Re: Time comparing insert in 2012 vs 2016

TaPaK, yes, I understand magnification of runtime of requests as degradation of productivity.
Miscrosoft somewhere wrote that if on 2014 requests are fulfilled more slowly, than on prior versions it is necessary to install compatibility with 2012.

10

Re: Time comparing insert in 2012 vs 2016

Prolog wrote:

TaPaK, yes, I understand magnification of runtime of requests as degradation of productivity.
Miscrosoft somewhere wrote that if on 2014 requests are fulfilled more slowly, than on prior versions it is necessary to install compatibility with 2012.

Well questions are not present more

11

Re: Time comparing insert in 2012 vs 2016

Prolog;
And you at all did not clarify the reason of it, and simply made as told . Such subjects often in an Internet jump out and quite often the reason new cardinality estimator, but at you probably Google .

12

Re: Time comparing insert in 2012 vs 2016

aleksrov wrote:

Prolog;
And you at all did not clarify the reason of it, and simply made as told . Such subjects often in an Internet jump out and quite often the reason new cardinality estimator, but at you probably Google .

Degradation, the sir smile

13

Re: Time comparing insert in 2012 vs 2016

Prolog wrote:

it is passed...
Oh I do not know! However we had a noticeable degradation of productivity at passage on 2014. Recovered (according to the advice of Microsoft) transfer of a database into compatibility level 2012.

What ridiculous companion.
Plans did not try to compare?
If something exchanged, thanks to something.
And something in 2014 is it new ;
other plans for at it rules of estimations of cardinality exchanged.
And changing compatibility level, you return old .
Well and now tell that there it is irregularly possible to estimate, interposing 1 value into a heap;
And the main thing, what other plan can be invented for this purpose ?

14

Re: Time comparing insert in 2012 vs 2016

Here at invm there was a sensible idea;
That can in a broad gull in 2016 registers more.
Well so the HARDWARE results digits, how many in a broad gull left in both cases.

15

Re: Time comparing insert in 2012 vs 2016

o-o;
And why in 2016 to a broad gull it can be written more, like algorithm with 2012 did not change or I something missed?

16

Re: Time comparing insert in 2012 vs 2016

aleksrov wrote:

o-o;
And why in 2016 to a broad gull it can be written more, like algorithm with 2012 did not change or I something missed?

Can-can.
Simply really more records falls,  I will look, which.
Well also can simply change a format of records for this reason  goes only to one side;
With low on the high version.
Reversely does not go, a format of records of a broad gull another

17

Re: Time comparing insert in 2012 vs 2016

Times I can not find in written form, means, it was in any video.
I will look already from the house.
There the sense was such:  in a broad gull 2012 showed any types of records on the;
And at me this type of records it was not simple, for 2008.
Here not that that the basis could not live without these records, but for the sake of any convenience to itself added

18

Re: Time comparing insert in 2012 vs 2016

o-o;
Thanks, I will look in the evening.

19

Re: Time comparing insert in 2012 vs 2016

In general if to do the test from video of Rendala about Page Split a type

create table test (c1 int, c2 chAR (1000))
create clustered index test_cl on test (c1)
insert into test values (1, ' a ')
insert into test values (2, ' b ')
insert into test values (3, ' c ')
insert into test values (5, ' e ')
insert into test values (6, ' f ')
insert into test values (7, ' g ')
insert into test values (8, ' h ')
insert into test values (9, ' i ')
checkpoint
insert into test values (4, ' d ')
select *
from sys.fn_dblog (null, null)

On 2008 and on 2012 on 2012 nearly there are more than records in a broad gull,
For added LOP_INSYSXACT

20

Re: Time comparing insert in 2012 vs 2016

o-o wrote:

from video of Rendala about Page Split

and the link share?

21

Re: Time comparing insert in 2012 vs 2016

[spoiler] us do not kill for it?
Hold [pluralsight.com] a SQL Server: Logging, Recovery, and the Transaction Log [2012, ENG] [/spoiler]

22

Re: Time comparing insert in 2012 vs 2016

o-o;
Thanks.

23

Re: Time comparing insert in 2012 vs 2016

..And 16 more add 32-64  storages....
Than  - especially greedy algorithms to storage.
Calculation on that is more than brains.

24

Re: Time comparing insert in 2012 vs 2016

...It on 2008  8-16-24  storages on the server...
Now, the more, the it is less ...

25

Re: Time comparing insert in 2012 vs 2016

...And it is still told that  are configured equally.
But especially advanced can tell;
What for at a row-wise insertion of 10 thousand lines in the table it is necessary to add 64 storages.
I am afraid to ask, how many it is necessary to add storages to manage to interpose gigabyte of the data.
Here without storage terabyte all a fuck-up, probably