管理大容量匯入的批處理

本節介紹如何管理為大容量匯入操作確定批處理大小的方式。 預設情況下，資料檔案的所有行都作為未知大小的單個批處理在單個事務中匯入。 在這種情況下，如果匯入操作在完成之前失敗，則會回滾整個事務，並且不會將任何資料新增到目標表中。 失敗的操作必須隨後從資料檔案的開始位置重新啟動。

將大型資料檔案作為單個批處理匯入可能會出現問題，因此，可以使用 bcp 和 BULK INSERT 通過一系列批處理匯入資料，每個批處理的大小都小於該資料檔案。 每個批處理都將匯入並記錄在一個單獨的事務中，提交完給定的事務後，由該事務匯入的行也將提交。 如果操作失敗，則只會回滾通過當前批處理匯入的行，您可以從失敗批處理的開始位置而不是從資料檔案的開始位置恢復匯入資料的操作。

注意：
有關批處理如何工作的資訊，請參閱批處理。
 

另外，如果選擇不限制批處理大小，則可通過在命令中估計資料檔案的大小來提高效能。 為該操作建立查詢計劃時，查詢處理器會使用該估計值。

注意：
指定批處理大小或資料檔案大小時，不要求很準確。
 

下表彙總了支援這些方法的限定符。

命令  批處理大小  每批傳送的行數  每批傳送的千位元組數 
bcp 1
 -b batch_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 命令中，不要同時使用 -b batch_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 例項大容量匯出到資料檔案中時，批處理大小不適用。
 

 指定資料檔案的近似大小
如果未指定大容量匯入操作的批處理大小，可以指明資料檔案的近似大小，使查詢處理器在查詢計劃中有效地分配資源。 若要指明資料檔案的近似大小，請估計行數或資料的千位元組數，如下所示：

估計每批的行數
若要估計行數，請使用 ROWS_PER_BATCH 提示或選項。 如果指定的值 > 0，則查詢處理器將使用 ROWS_PER_BATCH 的值作為在查詢計劃中分配資源的提示。 此值應該與實際的行數具有相同的順序。
儘管資料檔案中的所有行在一個批處理中複製到 SQL Server 例項中，但是 bcp 每傳送 1000 行，就會顯示“已將 1000 行傳送到 SQL Server”的訊息。 此訊息僅供參考，並且使用任何大小的批處理均會出現。

估計每批的千位元組數
若要以千位元組數估計資料檔案的大小，請使用 KILOBYTES_PER_BATCH 提示或選項。SQL Server 將根據指定的值優化大容量匯入操作。

注意：
如果大容量匯入大型資料檔案而未指定批處理大小或任何最小日誌記錄優化，事務日誌可能會在大容量匯入操作完成之前就已填滿。 若要避免這種情況，可以擴大事務日誌或允許其自動增長。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-503398/，如需轉載，請註明出處，否則將追究法律責任。