疑難排解封裝效能

更新： 2007 年 9 月 15 日

Integration Services 包含可用以疑難排解封裝效能的功能與工具，例如，記錄功能會擷取有關封裝的執行階段資訊，而效能計數器可讓您監視資料流程引擎。本主題提供有關這些功能的資訊，以及提供可避免常見效能問題的封裝設計建議。

設計效能更佳的資料流程

您可以在封裝中測試下列建議的結果，以便疑難排解封裝中的資料流程效能：

最佳化查詢

一些資料流程元件會在從來源擷取資料時或在查閱作業中使用查詢，以建立參考資料表。預設查詢會使用 SELECT * FROM <tableName> 語法。這種類型的查詢會傳回來源資料表中的所有資料行。讓所有資料行都在設計階段可用，您便可以選擇任何資料行作為查閱、傳遞或來源資料行。不過，在選擇想要使用的資料行後，您應該將查詢修訂為只納入使用的資料行。您可以輸入查詢或使用「查詢產生器」建構查詢。移除多餘的資料行可讓封裝中的資料流程更有效率，因為資料列愈小，就有愈多的資料列可以納入同一緩衝區，處理資料集中全部資料列所需的工作也就愈少。

附註：
當您在 Business Intelligence Development Studio 中執行封裝時，「SSIS 設計師」的 [進度] 索引標籤會列出警告，包括來源提供給資料流程使用的任何資料行，在下游資料流程元件後續作業中卻未使用的警告。您可以使用 RunInOptimizedMode 屬性自動移除這些資料行。

設定資料流程工作的屬性

您可以設定下列會影響效能的資料流程工作的屬性：

• 指定緩衝區資料的暫時儲存位置 (BufferTempStoragePath 屬性)，以及指定包含二進位大型物件 (BLOB) 資料之資料行的暫時儲存位置 (BLOBTempStoragePath 屬性)。根據預設，這個屬性的值是 TEMP 環境變數的值。您可能需要指定其他資料夾，以便將暫存檔放在不同的硬碟上，或是將暫存檔分散到多個磁碟機。您可以使用分號分隔目錄名稱，以指定多個目錄。
• 定義工作所使用之緩衝區的預設大小 (設定 DefaultBufferSize 屬性)，以及定義每個緩衝區中資料列的最大數目 (設定 DefaultBufferMaxRows 屬性)。預設緩衝區大小是 10 MB，最大緩衝區大小是 100 MB，預設最大資料列數目是 10,000。
• 設定執行期間工作可以使用的執行緒數目 (設定 EngineThreads 屬性)。這個屬性為資料流程引擎提供可使用之執行緒數目的建議，預設值是 5，最小值是 2。不過，無論這個屬性的值為何，引擎都不會使用超出它所需的執行緒數目。如果有需要，引擎也可能會使用超過這個屬性所指定的執行緒數目，以避免發生並行的問題。
• 指示資料流程工作是否以最佳化模式執行 (RunInOptimizedMode 屬性)。最佳化模式可藉由從資料流程中移除未使用的資料行、輸出和元件，來提升效能。

  附註：
  您可以在 Business Intelligence Development Studio 中的專案層級，設定具有相同名稱的屬性 (RunInOptimizedMode)，以指示資料流程工作在偵錯期間以最佳化模式執行。這個專案屬性會覆寫資料流程工作在設計階段的 RunInOptimizedMode 屬性。

瞭解資料流程工作設定緩衝區大小的方式

資料流程引擎是經由計算單一資料列的估計大小，開始設定緩衝區大小的工作，接著它會將資料列的估計大小乘以 DefaultBufferMaxRows 的值，以獲得初步可行的緩衝區大小值。

• 如果得到的結果超出 DefaultBufferSize 的值，引擎會減少資料列的數目。
• 如果得到的結果小於內部計算的最小緩衝區大小，引擎會增加資料列的數目。
• 如果得到的結果介於最小緩衝區大小和 DefaultBufferSize 的值之間，引擎會盡可能將緩衝區的大小設為接近資料列的估計大小乘以 DefaultBufferMaxRows 的值。

調整緩衝區大小

當您開始測試資料流程工作的效能時，請使用 DefaultBufferSize 和 DefaultBufferMaxRows 的預設值。啟用資料流程工作的記錄功能，並選取 BufferSizeTuning 事件，以查看每一個緩衝區中包含多少個資料列。

在開始調整緩衝區大小之前，您可以進行的最重要改善就是移除不需要的資料行，以及適當地設定資料類型，以減少每一個資料列的大小。

如果有足夠的記憶體可用，您應該使用數目較少但比較大的緩衝區，而不是使用數目較多但比較小的緩衝區。換句話說，您可以減少裝載資料所需的緩衝區總數，並盡可能在緩衝區中塞入更多的資料列，藉此改善效能。若要判斷緩衝區的最佳數目與大小，請使用 DefaultBufferSize 和 DefaultBufferMaxRows 的值進行測試，同時監視效能及 BufferSizeTuning 事件所報告的資訊。

附註：
您必須針對封裝中的每一個資料流程工作，個別設定此章節中所討論的資料流程工作屬性。

避免不必要的排序

排序本質上就是很慢的作業，避免不必要的排序可以改善封裝資料流程的效能。

如果來源資料已排序，無論是因為 SELECT 查詢使用了 ORDER BY 子句，還是因為資料已依照排序順序插入來源，您都可以提供資料已排序的提示，而避免使用「排序」轉換滿足某些下游轉換的排序需求。例如，「合併」和「合併聯結」轉換需要已排序的輸入。若要提供提示，您需要將上游資料流程元件之輸出的 IsSorted 屬性設為 True，並指定要排序資料的排序索引鍵資料行。如需詳細資訊，請參閱＜如何：設定輸出的排序屬性＞。

如果在資料流程中必須排序資料，您可以將資料流程設計為盡可能少使用排序作業，以改進效能。例如，如果資料流程使用「多點傳送」轉換來複製資料集，您可以在「多點傳送」轉換執行工作前，對資料集進行一次排序，而不應在轉換後排序多個輸出。

如需詳細資訊，請參閱＜排序轉換＞、＜合併轉換＞、＜合併聯結轉換＞和＜多重傳遞轉換＞。

最佳化緩時變維度轉換

「緩時變維度精靈」和「緩時變維度」轉換是符合大部分使用者需求的一般用途工具。但是，精靈產生的資料流並未針對效能最佳化。

在「緩時變維度」轉換中，最慢的元件通常是一次只針對單一資料列執行 UPDATE 的 OLE DB 命令轉換。使用目的地元件替換這些 OLE DB 命令轉換，通常最能夠改善「緩時變維度」轉換的效能。這些目的地元件會將所有要更新的資料列儲存到臨時資料表。然後您可以加入「執行 SQL」工作，該工作會同時對所有資料列執行單一、以集合為基礎之 Transact-SQL UPDATE。

進階使用者可以為針對大型維度最佳化的緩時變維度程序，設計自訂資料流。如需這個方法的相關討論和範例，請參閱＜獨特的維度狀況＞一節 (位於 Microsoft 白皮書《真實專案：商務智慧 ETL 設計實務》(英文)。

最佳化彙總轉換中的彙總

「彙總」轉換包含一些可用以改進效能的屬性。如果您知道資料集中索引鍵值的準確或近似數目，便可以設定 Keys 和 KeysScale 屬性。您也可以設定 CountDistinctKeys 和 CountDistinctScale 屬性，以指定轉換應當針對 COUNT DISTINCT 作業進行處理之索引鍵的準確或近似數目。使用這些屬性，轉換就會避免重新組織快取的總數，從而改進效能。

如果需要在資料流程中建立多個彙總，您應考慮使用一個「彙總」轉換來建立多個彙總，而不是建立多個轉換。在彙總是其他彙總的子集時，這個方法特別能夠改進效能，因為轉換可以最佳化內部儲存體，並且只會掃描一次傳入的資料。例如，如果彙總使用 GROUP BY 子句和 AVG 彙總，則將它們組合成一個轉換可以改進效能。不過，只有在記憶體是一個條件約束時才應考慮使用此方法，因為在一個「彙總」轉換內執行多個彙總會序列化彙總作業。

如需詳細資訊，請參閱＜彙總轉換＞。

設定合併聯結轉換中的緩衝區調整

合併聯結轉換包括 MaxBuffersPerInput 屬性，用以指定每一次輸入時所容許的使用中緩衝區數目上限。您可以使用這個屬性來微調緩衝區使用的記憶體數量，並進而微調轉換的效能。緩衝區的數目愈大，轉換使用的記憶體就愈多，效能也會愈高。MaxBuffersPerInput 的預設值 5 是多數案例中能夠達到最佳效果的緩衝區數目。若要微調效能，您可以嘗試使用稍有不同的緩衝區數目，例如 4 或 6。如果可能，您應該避免使用過小的緩衝區數目。例如，將 MaxBuffersPerInput 由 5 改設為 1 會對效能造成重大影響。另外，您也不應該將 MaxBuffersPerInput 設為 0。這個值表示不會進行調整，而且依據資料負荷量和可用記憶體數量而定，封裝可能不會完成。

為了避免發生死結，合併聯結轉換可能會暫時增加其所使用的緩衝區數目，使其超過 MaxBuffersPerInput 的值。解決死結狀況之後，MaxBuffersPerInput 便會回復成原先的設定值。

如需詳細資訊，請參閱＜合併聯結轉換＞。

測試目的地的效能

您可能會發現將資料儲存至目的地所花費的時間超出預期。若要識別速度很慢是否是因為目的地無法夠快地處理資料，您可以暫時使用「資料列計數器」轉換來取代目的地。如果輸送量顯著提高，則很可能是正在載入資料的目的地導致速度變慢。如需詳細資訊，請參閱＜資料列計數轉換＞。

監視封裝效能

Integration Services 包含可用以監視封裝效能的工具與功能。請使用下列建議判斷封裝對效能影響最大的部分：

檢視進度索引標籤上的資訊

「SSIS 設計師」會提供在 Business Intelligence Development Studio 中執行封裝時，有關控制流程和資料流程的資訊。[進度] 索引標籤會以執行順序列出工作和容器，並包含每個工作和容器 (包括封裝本身) 的開始與完成時間、警告及錯誤訊息。此外，還會以執行順序列出資料流程元件，並包含有關進度 (以完成百分比顯示) 以及已經處理的資料列數目等資訊。

設定封裝的記錄功能

Integration Services 包含各種記錄提供者，它們可讓封裝將執行階段的資訊記錄至不同類型的檔案或 SQL Server。您可以啟用封裝和個別封裝物件 (例如工作和容器) 的記錄項目。Integration Services 包含各種不同的工作和容器，每個工作和容器都有自己的一組描述性記錄項目。例如，包含執行 SQL 工作的封裝可以寫入記錄項目，而此記錄項目會列出工作執行的 SQL 陳述式，包括陳述式的參數值。

記錄項目包含諸如封裝和封裝物件的開始和結束時間等資訊，這樣就能識別執行速度較慢的工作和容器。如需詳細資訊，請參閱＜記錄封裝執行＞、＜在封裝中實作記錄＞和＜自訂訊息以進行記錄＞。

設定資料流程工作的記錄功能

資料流程工作提供許多可用以監視及調整效能的自訂記錄項目。例如，您可以監視可能會造成記憶體遺漏的元件，或是追蹤執行某特定元件所花費的時間。如需這些自訂記錄項目的清單以及記錄輸出範例，請參閱＜資料流程工作＞。

監視資料流程引擎的效能

Integration Services 包含一組監視資料流程引擎效能的效能計數器。例如，您可以追蹤所有緩衝區使用的記憶體總數 (以位元組為單位)，並檢查元件是否記憶體不足。緩衝區是元件用以儲存資料的記憶體區塊。如需詳細資訊，請參閱＜監視資料流程引擎的效能＞。

請參閱

工作

疑難排解封裝開發

概念

疑難排解封裝執行
疑難排解 Integration Services 服務

說明及資訊

取得 SQL Server 2005 協助

變更歷程記錄