共用方式為

  • 發行項

管理大量匯入的批次

本章節討論如何判斷大量匯入作業的批次大小。依預設，資料檔中的所有資料列都會在單一交易中以未知大小的單一批次匯入。在這種情況下，如果匯入作業在完成之前發生錯誤，整個交易將會回復，並且沒有任何資料會加入目的地資料表。失敗的作業必須從資料檔開頭處重新啟動。

以單一批次匯入大型資料檔可能會發生問題，因此 bcp 和 BULK INSERT 可以讓您以一系列的批次匯入資料，而且每個批次都小於資料檔。每個批次都在個別的交易中分別匯入及記錄，在認可給定的交易之後，便會認可該筆交易匯入的資料列。如果作業失敗，只會回復從目前批次匯入的資料列，因此您可以從失敗批次的開頭繼續匯入資料，而不是從資料檔的開頭。

[!附註]

如需批次運作方式的詳細資訊，請參閱＜批次＞。

或者，如果選擇不限制批次的大小，您也可以在命令中預估資料檔的大小，進而增進效能。建立作業的查詢計畫時，將由查詢處理器使用這個預估。

[!附註]

指定批次大小或資料檔大小時，正確度並不是最重要的。

下表摘要列出支援這些替代方案的限定詞。

命令

批次大小

每個批次傳送的資料列數

每個批次傳送的 KB 數

bcp1

-bbatch_size

-h "ROWS_PER_BATCH = bb"

-h "KILOBYTES_PER_BATCH = cc"

BULK INSERT2

BATCHSIZE = batch_size

ROWS_PER_BATCH = rows_per_batch

KILOBYTES_PER_BATCH = kilobytes_per_batch

附註附註
將這個選項與 BATCHSIZE 搭配使用，會產生錯誤。

INSERT ...SELECT * FROM OPENROWSET(BULK...)

—3

ROWS_PER_BATCH = rows_per_batch

—3

1 在 bcp 命令中，請勿將 -bbatch_size 參數和 ROWS_PER_BATCH 或 KILOBYTES_PER_BATCH 提示一起使用。結合這些項目會引發錯誤。

2 在 BULK INSERT 命令中，如果使用 BATCHSIZE 搭配 ROWS_PER_BATCH 或 KILOBYTES_PER_BATCH，將優先使用 BATCHSIZE。

3 OPENROWSET 沒有 BATCHSIZE 或 KILOBYTES_PER_BATCH 選項。

以下段落將描述這些限定詞的用法。

指定大概的批次大小

若要匯入非常大量的資料列，將資料分成批次會更有幫助。每個批次完成之後，便會記錄該筆交易。若由於任何原因，大量匯入作業在完成之前提早結束，則只有目前的交易 (批次) 會被回復。

[!附註]

大量記錄包括交易記錄檔中已匯入資料的副本。這樣子會讓記錄快速成長，但在每個批次之後，您可以備份記錄以回收記錄空間。

若要以大略指定的大小，將資料檔分為一系列批次匯入，請使用下列限定詞：

  • 若使用 bcp：-b

  • 若使用 BULK INSERT：BATCHSIZE

每個資料列批次都是以個別的交易插入。若由於任何原因，大量匯入作業在完成之前提早結束，則只有目前的交易會被回復。例如，如果資料檔擁有 1000 個資料列，並且使用 100 作為批次大小，則 Microsoft SQL Server 會將此作業記錄成 10 個個別的交易，每個交易要將 100 個資料列插入目的地資料表內。如果大量匯入作業在載入第 750 個資料列時終止了，則當 SQL Server 回復目前的交易時，只有之前的 49 個資料列會被移除。目的地資料表仍包含前 700 個資料列。

SQL Server 可自動根據批次大小值將載入最佳化，以產生較佳的效能。一般來說，您應該盡量增加批次大小。通常，批次大小越大，大量匯入作業的效能就會越好。不過有一些例外狀況。如果目標資料表中有一或多個索引，則較大的批次大小可能會在排序時造成記憶體壓力。此外，在執行未使用 TABLOCK 選項的平行載入期間，較大的批次大小可能導致更多封鎖。

[!附註]

在將 SQL Server 執行個體的資料大量匯出到資料檔時，將無法使用批次大小。

指定資料檔的大概大小

如果未指定大量匯入作業的批次大小，您可指出資料檔的大概大小，讓查詢處理器可以有效地在查詢計畫中配置資源。若要指出資料檔的大概大小，請預估資料的資料列數或資料的 KB 數，如下所示：

  • 預估每個批次的資料列數

    若要預估資料列數，請使用 ROWS_PER_BATCH 提示或選項。如果指定 > 0 的值，查詢處理器會使用 ROWS_PER_BATCH 值當作提示，在查詢計畫中配置資源。這個值應該與實際的資料列數差不多。

    雖然資料檔的所有資料列都在同一個批次內複製到 SQL Server 的執行個體中，bcp 仍會在每 1000 個資料列之後顯示訊息 "1000 rows sent to SQL Server"。此訊息僅提供資訊，並且不管批次大小為何都會出現。

  • 預估每個批次的 KB 數

    若要以 KB 為單位預估資料檔的大小，請使用 KILOBYTES_PER_BATCH 提示或選項，SQL Server 會根據指定的值來最佳化大量匯入作業。

[!附註]

當您大量匯入大型資料檔而沒有指定批次大小，或任何最低限度記錄最佳化項目時，在大量匯入作業完成之前，交易記錄檔可能就已經滿了。若要避免這個狀況，您可以增加交易記錄檔的大小，或讓交易記錄檔可以自動增加。