是否有任何关于“ ROW PER BATCH”的信息和“最大插入提交大小”；在SSIS包装中？ [英] Is there any releavance for "ROW PER BATCH" AND "MAX INSERT COMMIT SIZE" IN SSIS PACKAGES?

问题描述

我有SSIS软件包，可以将包含1000万条记录的2.5 GB数据导出到Sql Server数据库中，该数据库具有10个分区（包括PRIMARY FILE GROUP）。

I've have SSIS Package that is exporting 2.5 GB OF DATA containing 10 million records into Sql Server Database which has 10 partitions including PRIMARY FILE GROUP.

更改默认最大插入提交大小之前，即 2147483647和每批行。这需要7分钟的时间

Before Changing default Max Insert Commit size i.e."2147483647" and Row per batch.It was taking 7 mins for completed transformation with fast load option.

但是在使用一些公式更改了一些不错的值之后，执行仅用了2分钟。

But After chaning it some decent value with some formula, the execution was done in only 2 minutes.

FYI- DefaultMaxBufferRows & DefaultMaxBufferSize 是场景设置中的默认值，分别是10000和10 MB。

FYI- DefaultMaxBufferRows & DefaultMaxBufferSize were default value in both scenorio i.e. 10000 and 10 MB respectively.

要计算最大插入提交大小 ; 每批次的行
使用低于计算值的值。

To calculate Max Insert Commit size & Row per batch Below calucation are used.

1）计算的要从源转移的记录长度。大约1038个字节。

1) Calculated length of records from source that is being transfered. which comes around 1038 bytes.

CREATE TABLE [dbo].[Game_DATA2](
    [ID] [int] IDENTITY(1,1) NOT NULL, -- AUTO CALCULATED
    [Number] [varchar](255) NOT NULL, -- 255 bytes
    [AccountTypeId] [int] NOT NULL, -- 4 bytes
    [Amount] [float] NOT NULL,-- 4 bytes
    [CashAccountNumber] [varchar](255) NULL, -- 255 bytes
    [StartDate] [datetime] NULL,-- 8 bytes
    [Status] [varchar](255) NOT NULL,-- 255 bytes
    [ClientCardNumber] [varchar](255) NULL -- 255 bytes
)

2）每批的行数= packate_size /每条记录的字节数= 32767/1038 = 32左右。

2) Rows per batch = packate_size/bytes per record =32767/1038 =32 approx.

3）最大插入提交大小=包装大小*事务数= 32767 * 100 = 3276700
（包装大小和事务数可以根据要求更改）

3) Max insert commit size = packate size *number of transaction = 32767*100=3276700 (Packate size and number transaction are variable can change as per requirement)

问题：

• 每个批次的行数和最大插入提交大小是否相关？ ？由于没有在存档中提及的信息 article 用于调整DFT（数据流任务） ）执行。

• Is there any relevance of rows per batch and max insert commit size? As there's no information mentioned in an archive article for tunning DFT(DATA FLOW TASK) execution.

这些配置是否可以与DefaultBuffermaxzie和

DefualtBuffermaxrows一起使用？如果可以，如何？

Are these configuration works along with DefaultBuffermaxzie and
DefualtBuffermaxrows?if yes how?

推荐答案

这些参数仅引用DFT OLE DB目标。 OLE DB目标发出插入批量命令。这两个参数通过以下方式对其进行控制：

These parameters refer to DFT OLE DB Destination only. OLE DB Destination issues an insert bulk command. These two parameters control it in the following way:

• 最大插入提交大小-控制在其中插入多少数据一批。因此，如果将MICS设置为5000，并且有9000行，并且在前5000个结果中遇到错误，则将回滚整个5000个批次。 MISC等于BULK INSERT transact-sql命令中的BATCHSIZE参数。


• 每批行数-只是查询优化器的提示。此值应设置为实际的预期行数。 RPB等同于BULK INSERT transact-sql命令的ROWS_PER_BATCH参数。
  
  指定MICS的值会产生一些影响。每个批次都将复制到事务日志中，这将使它迅速增长，但是可以在每个批次之后备份该事务日志。此外，如果目标表上有索引，并且批处理量较大，则批处理将对内存产生负面影响，并且可能还会发生更多阻塞。

• Maximum insert commit size - controls how much data inserted in a single batch. So, if you have MICS set to 5000 and you have 9000 rows and you encounter an error in the first 5000 results, the entire batch of 5000 will be rolled back. MISC equates to the BATCHSIZE argument in the BULK INSERT transact-sql command.
• Rows Per Batch - merely a hint to the query optimizer. The value of this should be set to the actual expected number of rows. RPB equates to the ROWS_PER_BATCH argument to the BULK INSERT transact-sql command.
  Specifying a value for the MICS will have a few effects. Each batch is copied to the transaction log, which will cause it to grow quickly, but offers the ability to back up that transaction log after each batch. Also, having a large batch will negatively affect memory if you have indexes on the target table, and if you are not using table locking, you might have more blocking going on.

在此命令上批量插入（Transact-SQL）-MS文章。

DefaultBuffermaxsize 和 DefaultBuffermaxrows 控制DFT本身内部的RAM缓冲区管理。

DefaultBuffermaxsize and DefaultBuffermaxrows controls RAM buffer management inside DFT itself, and has no interference with options mentioned above.

这篇关于是否有任何关于“ ROW PER BATCH”的信息和“最大插入提交大小”；在SSIS包装中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！