如何讓我的SSIS進程消耗更多資源並且運作得更快？

Question 1

在 bilinkc 提出很好的建議之後，在不知道瓶頸在哪裡的情況下，我會嘗試其他一些事情。

正如您已經指出的，您應該致力於並行性，而不是在同一資料流中處理更多資料（數月）。您已經使轉換並行運行，但來源和目標（以及聚合）沒有並行運行！因此，請讀到最後並記住，您也應該讓它們並行運行，以便利用您的 CPU 能力。並且不要忘記你是記憶體限制（無法在一批中聚合無限多個月份），因此要採取的方法（「橫向擴展」）是獲取資料區塊，對其進行處理並儘快將其放入目標資料庫。這需要消除公共元件（一個來源，一個聯合所有），因為每個資料塊都受到這些公共元件的速度限制。

原始碼相關最佳化：

嘗試在同一資料流中使用多個來源（和目標）而不是平衡資料分配器 - 您在日期列上使用聚集索引，以便您的資料庫伺服器能夠快速檢索基於日期範圍內的資料；如果您在資料庫所在的不同伺服器上執行包，您將提高網路利用率

轉換相關優化：

您真的需要在聚合之前進行 Union All 操作嗎？如果沒有，請查看有關多個目的地的目的地相關最佳化
放鍵、KeyScale 和 AutoExtendFactor聚合組件以避免重新散列 - 如果這些屬性設定不正確，那麼您將在套件執行期間看到警告；請注意，預測固定月數批次的最佳值比無限月數的批次更容易（例如您的情況 18 和提高）
考慮在 SQL Server 中進行聚合和（取消）透視，而不是在 SSIS 套件中進行 - SQL Server 在這些任務中優於 Integration Services；當然，轉換邏輯可能會禁止在套件中執行某些轉換之前進行聚合
如果您可以聚合（以及透視/逆透視）（例如）資料庫中的每月數據，請嘗試使用 SQL 在來源查詢或目標資料庫中執行此操作；根據您的環境，寫入目標資料庫中的單獨資料表、建立索引、使用 SQL 聚合的 SELECT INTO 可能比在套件中執行更快；請注意，並行此類活動會對您的儲存造成很大壓力
最後你有一個多播；我不知道有多少行到達那裡，但請考慮以下內容：寫入右側的目標（在螢幕截圖上），然後將記錄填入SQL 查詢中左側的目標（以消除第二次聚合並釋放資源- SQL Server可能會做得更快）

目的地相關最佳化：

使用SQL Server 目標如果可能（套件必須與資料庫在同一台伺服器上執行，且目標資料庫必須是 SQL Server）；請注意，它需要精確的列資料類型匹配（管道 -> 表列）
考慮設置恢復模型目標（資料倉儲）資料庫上的 Simple
並行化目的地 - 使用單獨的聚合和單獨的目的地（到同一個表），而不是聯合所有 + 聚合 + 目的地；這裡你應該考慮劃分您的目標表並將分區放在單獨的文件組上；如果您逐月處理數據，請按月進行分區並使用分區切換

似乎我不清楚並行性該走哪條路。你可以試試：

將多個來源放入單一資料流中需要您複製並貼上每個來源的轉換邏輯和目標
並行運行多個資料流，每個資料流僅處理一個月
並行運行多個包，其中每個包都有一個僅處理一個月的資料流；一個主包來控制每個（月）包的執行 - 這是首選方式，因為一旦投入生產，您可能只會運行包一個月
或與之前相同，但具有平衡資料分配器以及 Union All 和 Aggregate

在執行其他操作之前，您可能需要進行快速測試：獲取原始包，將其更改為使用 1 個月，製作精確的副本以處理另一個月並並行運行這些包。與您原來的包裹處理時間相比需要 2 個月。一次對 2 個單獨的 6 個月套餐和單一 12 個月套餐執行相同操作。它應該以滿 CPU 使用率運行您的伺服器。

盡量不要過度並行化，因為您將對目標進行多次寫入，因此您不想啟動 18 個並行每月包，而是啟動 3 或 4 個包。

最後，我堅信記憶體和目標 I/O 壓力是需要消除的壓力。

請告知我們您的進展。

Answer

在 bilinkc 提出很好的建議之後，在不知道瓶頸在哪裡的情況下，我會嘗試其他一些事情。

正如您已經指出的，您應該致力於並行性，而不是在同一資料流中處理更多資料（數月）。您已經使轉換並行運行，但來源和目標（以及聚合）沒有並行運行！因此，請讀到最後並記住，您也應該讓它們並行運行，以便利用您的 CPU 能力。並且不要忘記你是記憶體限制（無法在一批中聚合無限多個月份），因此要採取的方法（「橫向擴展」）是獲取資料區塊，對其進行處理並儘快將其放入目標資料庫。這需要消除公共元件（一個來源，一個聯合所有），因為每個資料塊都受到這些公共元件的速度限制。

原始碼相關最佳化：

嘗試在同一資料流中使用多個來源（和目標）而不是平衡資料分配器 - 您在日期列上使用聚集索引，以便您的資料庫伺服器能夠快速檢索基於日期範圍內的資料；如果您在資料庫所在的不同伺服器上執行包，您將提高網路利用率

轉換相關優化：

您真的需要在聚合之前進行 Union All 操作嗎？如果沒有，請查看有關多個目的地的目的地相關最佳化
放鍵、KeyScale 和 AutoExtendFactor聚合組件以避免重新散列 - 如果這些屬性設定不正確，那麼您將在套件執行期間看到警告；請注意，預測固定月數批次的最佳值比無限月數的批次更容易（例如您的情況 18 和提高）
考慮在 SQL Server 中進行聚合和（取消）透視，而不是在 SSIS 套件中進行 - SQL Server 在這些任務中優於 Integration Services；當然，轉換邏輯可能會禁止在套件中執行某些轉換之前進行聚合
如果您可以聚合（以及透視/逆透視）（例如）資料庫中的每月數據，請嘗試使用 SQL 在來源查詢或目標資料庫中執行此操作；根據您的環境，寫入目標資料庫中的單獨資料表、建立索引、使用 SQL 聚合的 SELECT INTO 可能比在套件中執行更快；請注意，並行此類活動會對您的儲存造成很大壓力
最後你有一個多播；我不知道有多少行到達那裡，但請考慮以下內容：寫入右側的目標（在螢幕截圖上），然後將記錄填入SQL 查詢中左側的目標（以消除第二次聚合並釋放資源- SQL Server可能會做得更快）

目的地相關最佳化：

使用SQL Server 目標如果可能（套件必須與資料庫在同一台伺服器上執行，且目標資料庫必須是 SQL Server）；請注意，它需要精確的列資料類型匹配（管道 -> 表列）
考慮設置恢復模型目標（資料倉儲）資料庫上的 Simple
並行化目的地 - 使用單獨的聚合和單獨的目的地（到同一個表），而不是聯合所有 + 聚合 + 目的地；這裡你應該考慮劃分您的目標表並將分區放在單獨的文件組上；如果您逐月處理數據，請按月進行分區並使用分區切換

似乎我不清楚並行性該走哪條路。你可以試試：

將多個來源放入單一資料流中需要您複製並貼上每個來源的轉換邏輯和目標
並行運行多個資料流，每個資料流僅處理一個月
並行運行多個包，其中每個包都有一個僅處理一個月的資料流；一個主包來控制每個（月）包的執行 - 這是首選方式，因為一旦投入生產，您可能只會運行包一個月
或與之前相同，但具有平衡資料分配器以及 Union All 和 Aggregate

在執行其他操作之前，您可能需要進行快速測試：獲取原始包，將其更改為使用 1 個月，製作精確的副本以處理另一個月並並行運行這些包。與您原來的包裹處理時間相比需要 2 個月。一次對 2 個單獨的 6 個月套餐和單一 12 個月套餐執行相同操作。它應該以滿 CPU 使用率運行您的伺服器。

盡量不要過度並行化，因為您將對目標進行多次寫入，因此您不想啟動 18 個並行每月包，而是啟動 3 或 4 個包。

最後，我堅信記憶體和目標 I/O 壓力是需要消除的壓力。

請告知我們您的進展。

Question 2

使用流程瀏覽器揭示更多資源使用情況（記憶體和 IO）。請注意，磁碟 IO 圖表可能有點誤導，因為圖表中的峰值通常是由於硬碟快取功能造成的，因此當磁碟 IO 是瓶頸時，它並不總是立即在圖表中顯示出來。

在某些情況下，您可以受益於安裝記憶體磁碟機並將臨時目錄放在那裡。我已經成功使用了這個減少我們的建置機器用於進行完整的夜間建置和運行測試的時間。我不確定 SSIS 是否會受益。

Answer

使用流程瀏覽器揭示更多資源使用情況（記憶體和 IO）。請注意，磁碟 IO 圖表可能有點誤導，因為圖表中的峰值通常是由於硬碟快取功能造成的，因此當磁碟 IO 是瓶頸時，它並不總是立即在圖表中顯示出來。

在某些情況下，您可以受益於安裝記憶體磁碟機並將臨時目錄放在那裡。我已經成功使用了這個減少我們的建置機器用於進行完整的夜間建置和運行測試的時間。我不確定 SSIS 是否會受益。

Question 3

（重新發布我最初的回复，沒有考慮 BDD）

歸根結底，所有處理都受到四個因素之一的約束

記憶
中央處理器
磁碟
網路

第一步是確定限制因素是什麼，然後確定您是否可以影響它（獲取更多或減少使用）

組件選擇

當您執行超過 18 個月時，您的伺服器記憶體就會耗盡，這與處理時間如此之長有關。這樞軸和聚合轉換是異步組件。來自來源元件的每一行都分配有 N 位元組的記憶體。同一個資料桶存取所有轉換，應用它們的操作並在目的地清空。該內存桶被一遍又一遍地重複使用。

當非同步組件進入競技場時，管道將被拆分。傳輸該行資料的儲存桶現在必須清空到新儲存桶中才能完成管道。就執行時間和記憶體而言，在執行樹之間複製資料是一項昂貴的操作（可能會增加一倍）。這也減少了引擎在等待非同步操作完成時並行執行某些執行機會的機會。由於轉型的性質，營運進一步放緩。聚合是一個完全阻塞的元件，因此全部在轉換向下游轉換釋放單行之前，資料必須到達並進行處理。

如果可能的話，您可以將資料透視表和/或聚合推送到伺服器嗎？這應該會減少資料流所花費的時間以及消耗的資源。

您可以嘗試增加引擎可以選擇的並行操作數量。傑米的文章,SQL CAT 的文章

如果您確實想知道時間花在資料流中的何處，請記錄 OnPipelineRowsSent 的執行情況。然後你可以使用這個詢問將其拆開（用 sysssislog 取代 sysdtslog90 後）

網路傳輸

根據您的圖表，CPU 或記憶體似乎都沒有被徵稅。我相信您已經指出來源伺服器和目標伺服器位於單一機器上，但 SSIS 套件在另一個機器上託管和處理。透過線路傳輸這些資料並再次返回時，您要付出不小的成本。是否可以在來源伺服器上處理資料？你需要為那個盒子分配更多的資源，我祈禱這是一個強大的虛擬機，這不是問題。

如果這不是一個選項，請嘗試設定資料包大小連接管理器的屬性到 32767 並與網路操作人員討論巨型幀是否適合您。這兩個技巧都位於「調整您的網路」部分。

我對磁碟計數器很感興趣，但您應該能夠查看等待類型是否與磁碟相關。

Answer

（重新發布我最初的回复，沒有考慮 BDD）

歸根結底，所有處理都受到四個因素之一的約束

記憶
中央處理器
磁碟
網路

第一步是確定限制因素是什麼，然後確定您是否可以影響它（獲取更多或減少使用）

組件選擇

當您執行超過 18 個月時，您的伺服器記憶體就會耗盡，這與處理時間如此之長有關。這樞軸和聚合轉換是異步組件。來自來源元件的每一行都分配有 N 位元組的記憶體。同一個資料桶存取所有轉換，應用它們的操作並在目的地清空。該內存桶被一遍又一遍地重複使用。

當非同步組件進入競技場時，管道將被拆分。傳輸該行資料的儲存桶現在必須清空到新儲存桶中才能完成管道。就執行時間和記憶體而言，在執行樹之間複製資料是一項昂貴的操作（可能會增加一倍）。這也減少了引擎在等待非同步操作完成時並行執行某些執行機會的機會。由於轉型的性質，營運進一步放緩。聚合是一個完全阻塞的元件，因此全部在轉換向下游轉換釋放單行之前，資料必須到達並進行處理。

如果可能的話，您可以將資料透視表和/或聚合推送到伺服器嗎？這應該會減少資料流所花費的時間以及消耗的資源。

您可以嘗試增加引擎可以選擇的並行操作數量。傑米的文章,SQL CAT 的文章

如果您確實想知道時間花在資料流中的何處，請記錄 OnPipelineRowsSent 的執行情況。然後你可以使用這個詢問將其拆開（用 sysssislog 取代 sysdtslog90 後）

網路傳輸

根據您的圖表，CPU 或記憶體似乎都沒有被徵稅。我相信您已經指出來源伺服器和目標伺服器位於單一機器上，但 SSIS 套件在另一個機器上託管和處理。透過線路傳輸這些資料並再次返回時，您要付出不小的成本。是否可以在來源伺服器上處理資料？你需要為那個盒子分配更多的資源，我祈禱這是一個強大的虛擬機，這不是問題。

如果這不是一個選項，請嘗試設定資料包大小連接管理器的屬性到 32767 並與網路操作人員討論巨型幀是否適合您。這兩個技巧都位於「調整您的網路」部分。

我對磁碟計數器很感興趣，但您應該能夠查看等待類型是否與磁碟相關。

如何讓我的SSIS進程消耗更多資源並且運作得更快？

答案1

答案2

答案3

組件選擇

網路傳輸

相關內容