最佳化大量匯入的指導方針

發行項
12/15/2008

更新： 2006 年 4 月 14 日

此主題考慮最佳化數個大量匯入狀況效能的指導方針：

將資料從單一用戶端 (或資料流) 匯入空白資料表。
將資料從單一用戶端 (或資料流) 匯入已部分擴展、非空白的資料表。

附註：

將資料匯入非空白的資料表，稱為累加大量匯入。累加大量匯入的主要問題在於是否應事先卸除索引。
利用資料表層次鎖定，從多個用戶端 (或資料流) 平行匯入資料。
複製 Microsoft SQL Server 執行個體間的資料。

附註：
將資料匯入非空白的資料表，稱為累加大量匯入。累加大量匯入的主要問題在於是否應事先卸除索引。

這個主題也會提供進行大量匯入作業期間資料表鎖定和記錄的摘要。

將資料從單一用戶端 (或資料流) 匯入空白資料表

當您從單一用戶端 (或資料流) 將資料匯入空白資料表時，Microsoft 建議您執行下列步驟

指定 TABLOCK 限定詞 (如同三種大量匯入方法上可用的提示或選項)。使用 TABLOCK 會在大量作業期間採取資料表層次鎖定，並消除鎖定個別資料列的負擔。如需詳細資訊，請參閱＜控制大量匯入的鎖定行為＞。
使記錄量降到最小。如需詳細資訊，請參閱＜大量匯入採用最低限度記錄的必要條件＞。

如下所示來處理索引。
當您使用 bcp、BULK INSERT 或 INSERT ...SELECT * FROM OPENROWSET(BULK...) 時，如果資料表為空白且擁有叢集索引，而資料檔案中的資料排列順序要與叢集索引鍵資料行相符，請另外執行下列動作：

將已備妥叢集索引的資料大量匯入。
指定 ORDER 提示以及 TABLOCK 提示。如需詳細資訊，請參閱＜控制大量匯入資料時的排序順序＞。
對於空白資料表而言，此方法可大幅加快在匯入資料之後建立叢集索引的速度，因為該方法不需要進行排序步驟。

附註：
如果非空白資料表內有索引，即使是在大量記錄復原模式下，也會完整記錄大量匯入作業。若要決定是否要移除索引，請考慮不含索引大量匯入資料表的好處，是否大於卸除與重新建立索引的成本。

如果將資料連同索引大量匯入空白資料表，而且指定批次大小，則在第一個批次後該資料表就會變成非空白的資料表。開始進行第二個批次，就會完整記錄資料。對於空白的索引資料表，請考慮以單一批次執行大量匯入。

附註：
未指定批次大小時，依預設 SQL Server 查詢最佳化工具會假設預設大小就是資料檔案的大小。若要提升效能，您可以使用 ROWS_PER_BATCH 或 KILOBYTES_PER_BATCH 限定詞，作為最佳化工具有關資料檔案中大約資料列數的提示。如需詳細資訊，請參閱＜Managing Bulk Copy Batch Sizes＞。

一般而言，大量匯入不需索引之資料表的速度，會比大量匯入具有索引的資料表更迅速。因此，如果空白資料表有任何索引，則應該在將資料匯入資料表之前捨棄它們，之後再重新建立。如果未排序叢集索引資料行上的資料，而且資料表為空白，請卸除所有索引、匯入資料，然後建立新的索引。

將資料從單一用戶端 (或資料流) 匯入非空白資料表

將資料匯入已包含資料的資料表 (非空白資料表)，稱為累加大量匯入。累加大量匯入的主要問題在於是否應事先卸除索引。您有兩個選項。您可以保留索引，也可以卸除索引，之後再重新建立。

將資料從單一用戶端 (或資料流) 匯入非空白資料表時，是否保留索引要視所匯入的新資料量與資料表內現有資料相關的數量而定。

若要匯入少量與現有資料量相關的新資料，那麼捨棄並重新建立索引反而會比較有效率。重新建立索引所需的時間，可能會比大量作業期間節省的時間要長。
相反的，如果匯入相當大量的新資料，執行大量作業前捨棄資料表的索引可以提升效率，而不會大大增加製作索引所需的時間。

資料表內應有下表列出的新資料量下限，才可以捨棄索引。資料量下限與資料表的總資料量成比例。比例會視索引的類別與組合而有所不同。如果新的資料超過指定索引類型或索引分組建議的百分比，就可以考慮在大量作業之前捨棄索引，之後再重新建立它們。這些數字與現有資料和要載入之資料的資料模式有關。因此，數字只能做為一般方針。

索引	相關的新資料量
僅限叢集索引	30%
叢集與一個非叢集索引	25%
叢集與兩個非叢集索引	25%
僅限一個非叢集索引	100%
兩個非叢集索引	60%

利用資料表層次鎖定，從多個用戶端 (或資料流) 平行匯入資料

如果 SQL Server 在有多個處理器的電腦上執行，而且要大量匯入資料表的資料可以分割成不同的資料檔案，您可以從多個用戶端將資料平行匯入資料表，來提升效能。從多個用戶端大量匯入到一個資料表時，每一個用戶端必須有它自己的輸入資料檔。

從多個用戶端將資料匯入資料表時，請考慮以下項目：

多個大量匯入資料流很有可能會相互封鎖。
若要避免這種狀況，SQL Server 提供一種特殊的內部鎖定，稱為大量更新 (BU) 鎖定。若要取得 BU 鎖定，您需要使用 TABLOCK 選項指定每個大量匯入資料流，而不是封鎖其他大量匯入資料流。這可以避免用戶端之間發生資料表存取衝突。不過，BU 鎖定只能在沒有索引的資料表 (無論是空白或非空白) 上使用。如果您對含有索引的資料表指定 TABLOCK，則不可能執行平行大量匯入。如需詳細資訊，請參閱＜控制大量匯入的鎖定行為＞。
如果該資料表有任何索引，您可以在大量匯入資料前捨棄所有索引，這樣就可以利用 BU 鎖定。接著，您可以使用 TABLOCK 平行地大量匯入資料，然後再重新建立索引。請注意，如果非空白資料表內有索引，即使是在大量記錄復原模式下，也會完整記錄大量匯入作業。若要決定是否要移除索引，請考慮不含索引大量匯入資料表的好處，是否大於卸除與重新建立索引的成本。

附註：

如果捨棄了次要索引，請考慮從個別用戶端建立各個次要索引，平行地重新建立索引。

若要避免捨棄和重新建立索引，您可以執行平行匯入，而不需指定 TABLOCK 提示。不過，在此狀況下，多個大量匯入資料流可能會相互封鎖，而且無法使用大量記錄最佳化。若要使發生封鎖的情況降到最少，您可以指定較小的批次大小，而且使用 ORDER 提示，以在大量匯入作業期間省去排序步驟。
資料必須分割成多個輸入檔，每一個用戶端一個檔案。若要更有效率地使用 CPU，各個資料檔案的大小應該差不多。

附註：
如果捨棄了次要索引，請考慮從個別用戶端建立各個次要索引，平行地重新建立索引。

如需詳細資訊，請參閱＜使用資料表層級鎖定平行匯入資料＞。

進行大量匯入期間的資料表鎖定和記錄

下表將摘要說明進行大量匯入作業期間，資料表結構描述如何決定鎖定的類型。此外，下表也會指出資料表是否空白、是否針對作業設定 TABLOCK，以及當資料庫使用大量記錄復原模式時，會發生何種記錄類型。

附註：
進行第一次成功的批次處理後，資料表便不再空白了。

大量匯入目標資料表	資料表是否空白？	是否設定 TABLOCK？	鎖定	在大量記錄和簡單復原模式下記錄
堆積	是	是	BU-Tab	大量記錄
堆積	是	否	IX-Tab	完整記錄
堆積	否	是	BU-tab	大量記錄
堆積	否	否	IX-Tab	完整記錄
含有一個非叢集索引的堆積	是	是	SCH-M	大量記錄
含有一個非叢集索引的堆積	是	否	IX-Tab	完整記錄
含有一個非叢集索引的堆積	否	是	SCH-M	資料—大量記錄索引—完整記錄
含有一個非叢集索引的堆積	否	否	IX-Tab	完整記錄
叢集索引	是	是	SCH-M	大量記錄
叢集索引	是	否	IX-Tab	完整記錄
叢集索引	否	是	X-TAB	完整記錄
叢集索引	否	否	IX-Tab	完整記錄

複製 SQL Server 執行個體間的資料

若要在 SQL Server 執行個體間大量複製資料，請使用 bcp 以將資料表資料匯出到資料檔案。接著，使用其中一種大量匯入方法，將資料從檔案匯入到資料表。請使用原生或 Unicode 原生格式來執行大量匯出與大量匯入作業。

附註：
如需這些格式的詳細資訊，請參閱＜使用原生格式匯入或匯出資料＞和＜使用 Unicode 原生格式匯入或匯出資料＞。

如果來源資料表有叢集索引，或試圖將資料大量匯入到有叢集索引的資料表：

對 SELECT 陳述式使用 bcp 搭配 query 選項，並使用適當的 ORDER BY 子句以建立已排序的資料檔案，將資料大量匯出來源資料表。如需詳細資訊，請參閱＜bcp 公用程式＞。
當大量匯入資料到 SQL Server 時。請使用 ORDER 限定詞，只有 bcp 和 BULK INSERT 支援它。如需詳細資訊，請參閱＜控制大量匯入資料時的排序順序＞。

如需詳細資訊，請參閱＜在伺服器之間複製資料＞。

請參閱

變更歷程記錄

版本	歷程記錄
2006 年 4 月 14 日	新增內容：加入「進行大量匯入期間的資料表鎖定和記錄」一節。

共用方式為