1

Topic: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Hello. Here such request:

DROP TABLE [dbo]. [Fct_Subconto]
GO
SELECT [IdNci_0062]
,[Date_Month]
,[Year_Prov]
,[Month_Prov]
,[IDDIM_0090]
,[IDDim_0050]
,[]
,[]
,[IdDim_0029Clc]
,[IdDIM_0021]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
INTO [dbo]. [Fct_Subconto]
FROM [dbo]. [Fct_0020_OSB_Subconto]

Leads to growth of basis TempDB to 12 . Really into the table are interposed about 2 million lines. After an insertion the size the occupied table is no more 4 . As it is possible to make so that the insertion of the data did not take how many a place in TempDB

2

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Pvase;
With the server precisely were not mistaken?

3

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

And what for the table ? Not better then truncate

4

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

If to interpose on slices, and model of basis Simple a broad gull on the size it will be not strong more than the biggest slice.
As the variant - can be preempted in a file and to interpose BULK Load (not ).

5

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

WarAnt wrote:

Pvase;
With the server precisely were not mistaken?

yes, here the version: Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64)

6

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Konst_One wrote:

and what for the table ? Not better then truncate

That it I test different variants, was so earlier:

TRUNCATE TABLE [dbo]. [Fct_Subconto]
GO
INSERT INTO [dbo]. [Fct_Subconto] WITH (TABLOCK)
([IdNci_0062]
,[Date_Month]
,[Year_Prov]
,[Month_Prov]
,[IDDIM_0090]
,[IDDim_0050]
,[]
,[]
,[IdDim_0029Clc]
,[IdDIM_0021]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_])
SELECT [IdNci_0062]
,[Date_Month]
,[Year_Prov]
,[Month_Prov]
,[IDDIM_0090]
,[IDDim_0050]
,[]
,[]
,[IdDim_0029Clc]
,[IdDIM_0021]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
,[_]
FROM [dbo]. [Fct_0020_OSB_Subconto]

7

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

LSV wrote:

If to interpose on slices, and model of basis Simple a broad gull on the size it will be not strong more than the biggest slice.
As the variant - can be preempted in a file and to interpose BULK Load (not ).

Thanks, but increase not the transaction log, namely basis TempDB.

8

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

IdNci_0062 Is PK?

9

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

+ Model of recovery for basis - Simple.

10

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Konst_One wrote:

IdNci_0062 is PK?

Here Primary Key is not present, in the second request is, it ID, the simple counter + 1.

11

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

And itself  on million records from [dbo]. [Fct_0020_OSB_Subconto] do not want to beat and interpose on pieces in a cycle

12

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Pvase wrote:

it is passed...
Thanks, but increase not the transaction log, namely basis TempDB.

Well in specified council TempDb too will not grow. smile
can be involved, - if the big table/sample needs to be sorted with difficulty. There are also other cases when TempDB is used.
This temporal data store.

13

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Pvase;
At the request plan look, whether is there "table spool"

14

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

The Data Loading Performance Guide:
https://technet.microsoft.com/ru-ru/library/dd425070 (v=sql.100).aspx

15

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

wrote:

Pvase;
At the request plan look, whether is there "table spool"

[img=https://1drv.ms/i/s!AqSmed45oz1JhdZiVNRZ-_rlKIwu9w]

16

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

wrote:

Pvase;
At the request plan look, whether is there "table spool"

Here the link: https://onedrive.live.com/?authkey=!AFT … mp;o=OneUp

17

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Pvase;
While will is indexes tempdb to swell, of it not to get rid, it is possible to lower only the size of a tumor using an insertion pieces.

18

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Konst_One wrote:

IdNci_0062 is PK?

Was mistaken, here so looks PK:

CREATE UNIQUE CLUSTERED INDEX [IX_Fct_0020_AccObor_ID] ON [dbo]. [Fct_0020_AccObor]
(
[Date_Month] ASC;
[IdNci_0062] ASC;
[IdDIM_0090] ASC;
[IdDim_0050] ASC;
[IdDim_0029Clc] ASC;
[IdDim_0029Acc] ASC;
[IdDim_0029Doc] ASC;
[IdDim_0021] ASC;
[IdDim_0021_0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

19

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Pvase wrote:

it is passed...
Here the link: https://onedrive.live.com/?authkey=!AFT … mp;o=OneUp

you specially inform us, what the problem is caused by one request, and fulfill another?

20

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Sergey Alekseevich wrote:

it is passed...
You specially inform us, what the problem is caused by one request, and fulfill another?

, did not want you to mislead, the problem arises in both requests that INSERT that SELECT INTO - the problem same, very strongly grows basis tempdb.

21

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

It I test at once three requests, they similar, and gave the reference to other table. But the problem exists under all tables where many lines (in the resulted example are interposed 19 million lines for 9 minutes with growth tempdb to 8 ), in an example in the beginning an insertion  for 40 minutes with growth Tempdb is interposed much more.

22

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Still a problem that if requests to fulfill parallely total growth TempDB it is similar more to multiplyings. So TempDB grows to 60 , the place on a disk comes to an end and anything good from this does not happen.

23

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

Disks add on the server that he at you suffers the poor creature

24

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

wrote:

leads to growth of basis TempDB to 12 . Really into the table are interposed about 2 million lines. After an insertion the size the occupied table is no more 4 . As it is possible to make so that the insertion of the data did not take how many a place in TempDB

Refuse indexes, add RAM

25

Re: The SQL Server 2013, INSERT more than 1 million lines, takes a lot of place in tempdb

On a picture sorting and a spool that not clear is drawn. Sorting because of an insertion in clustered index. Storages add, as already wrote.