SSIS プロセスでより多くのリソースを消費し、より高速に実行するにはどうすればよいですか?

Question 1

bilinkc から良い提案があったので、ボトルネックがどこにあるかはわからないものの、他のいくつかのことを試してみることにしました。

すでに述べたように、同じデータフローでより多くのデータ（月）を処理するのではなく、並列処理に取り組む必要があります。変換はすでに並列で実行されていますが、ソースと宛先（および集計）は並列で実行されていません。最後まで読んで、CPUパワーを活用するためにも並列で実行する必要があることを覚えておいてください。そして、メモリ制限（1 つのバッチで無限の月数を集計できない）のため、実行すべき方法（「スケールアウト」）は、データのチャンクを取得し、それを処理して、できるだけ早く宛先データベースに格納することです。各データチャンクは共通コンポーネントの速度に制限されるため、共通コンポーネント (1 つのソース、1 つのユニオン All) を削除する必要があります。

ソース関連の最適化:

Balanced Data Distributor の代わりに、同じデータフローで複数のソース (および宛先) を試してください。日付列にクラスター化インデックスを使用すると、データベースサーバーは日付ベースの範囲でデータをすばやく取得できます。データベースが存在するサーバーとは別のサーバーでパッケージを実行すると、ネットワーク使用率が向上します。

変換関連の最適化:

集約の前に本当にUnion Allを実行する必要がありますか？そうでない場合は、複数の宛先に関する宛先関連の最適化を確認してください。
セットキー、KeyScale、および AutoExtendFactor再ハッシュを回避するための集計コンポーネントの場合 - これらのプロパティが誤って設定されている場合、パッケージ実行中に警告が表示されます。固定数の月数のバッチでは最適値を予測するのが簡単であることに注意してください（ケース18と増加など）
SSIS パッケージではなく、SQL Server で集計と (アン) ピボットを行うことを検討してください。SQL Server はこれらのタスクで Integration Services よりも優れています。もちろん、変換ロジックは、パッケージでいくつかの変換を実行する前に集計を禁止するようなものです。
たとえば、データベース内の月次データを集計（およびピボット/アンピボット）できる場合は、ソースクエリまたは宛先データベースで SQL を使用して実行してみてください。環境によっては、宛先データベースの別のテーブルに書き込み、インデックスを作成し、SQL を使用して集計する SELECT INTO の方が、パッケージで実行するよりも高速になる可能性があります。このようなアクティビティを並列化すると、ストレージに大きな負荷がかかることに注意してください。
最後にマルチキャストがあります。そこに何行が届くかはわかりませんが、次のことを検討してください。右側の宛先に書き込み (スクリーンショット上)、次に SQL クエリで左側の宛先にレコードを入力します (2 番目の集計をなくし、リソースを解放するため - SQL Server ではおそらくはるかに高速に実行されます)。

目的地関連の最適化:

使用SQL Server 宛先可能であれば (パッケージはデータベースと同じサーバーで実行する必要があり、宛先データベースは SQL Server である必要があります)、列のデータ型が完全に一致する必要があることに注意してください (パイプライン -> テーブル列)。
設定を検討する復旧モデル宛先（データウェアハウス）データベースをシンプルにする
宛先を並列化する - すべてを結合する代わりに、集約と宛先を別々に使用し、同じテーブルに別の宛先を使用します。ここでは、次の点を考慮する必要があります。パーティション分割宛先テーブルとパーティションを別々のファイルグループに配置します。データを月ごとに処理する場合は、月ごとにパーティションを作成し、パーティション切り替え

並列処理でどの方法を採用するかについては不明瞭なままだったようです。以下を試してみてください。

複数のソースを単一のデータフローに配置するには、各ソースの変換ロジックと宛先をコピーして貼り付ける必要があります。
複数のデータフローを並列に実行し、各データフローが1か月分のみを処理する
複数のパッケージを並列で実行し、各パッケージに 1 か月だけを処理する 1 つのデータフローと、各 (月) パッケージの実行を制御する 1 つのマスターパッケージがあります。これは、本番環境に移行したらパッケージを 1 か月間だけ実行することになるため、推奨される方法です。
または前と同じですが、Balanced Data DistributorとUnion AllとAggregateを使用します

他の作業を行う前に、簡単なテストを行うことをお勧めします。元のパッケージを取得し、1 か月使用するように変更し、別の月を処理する正確なコピーを作成して、それらのパッケージを並行して実行します。元のパッケージが 2 か月を処理する場合と比較します。同じことを、2 つの個別の 6 か月パッケージと 1 つの 12 か月パッケージに対して同時に実行します。サーバーが CPU を最大使用率で実行するはずです。

宛先への書き込みが複数回行われるため、過度に並列化しないようにしてください。そのため、18 個の並列月次パッケージではなく、3 個または 4 個から開始することをお勧めします。

そして最後に、メモリと宛先 I/O の圧力は排除されるべきものであると強く信じています。

進捗状況をお知らせください。

Answer

bilinkc から良い提案があったので、ボトルネックがどこにあるかはわからないものの、他のいくつかのことを試してみることにしました。

すでに述べたように、同じデータフローでより多くのデータ（月）を処理するのではなく、並列処理に取り組む必要があります。変換はすでに並列で実行されていますが、ソースと宛先（および集計）は並列で実行されていません。最後まで読んで、CPUパワーを活用するためにも並列で実行する必要があることを覚えておいてください。そして、メモリ制限（1 つのバッチで無限の月数を集計できない）のため、実行すべき方法（「スケールアウト」）は、データのチャンクを取得し、それを処理して、できるだけ早く宛先データベースに格納することです。各データチャンクは共通コンポーネントの速度に制限されるため、共通コンポーネント (1 つのソース、1 つのユニオン All) を削除する必要があります。

ソース関連の最適化:

Balanced Data Distributor の代わりに、同じデータフローで複数のソース (および宛先) を試してください。日付列にクラスター化インデックスを使用すると、データベースサーバーは日付ベースの範囲でデータをすばやく取得できます。データベースが存在するサーバーとは別のサーバーでパッケージを実行すると、ネットワーク使用率が向上します。

変換関連の最適化:

集約の前に本当にUnion Allを実行する必要がありますか？そうでない場合は、複数の宛先に関する宛先関連の最適化を確認してください。
セットキー、KeyScale、および AutoExtendFactor再ハッシュを回避するための集計コンポーネントの場合 - これらのプロパティが誤って設定されている場合、パッケージ実行中に警告が表示されます。固定数の月数のバッチでは最適値を予測するのが簡単であることに注意してください（ケース18と増加など）
SSIS パッケージではなく、SQL Server で集計と (アン) ピボットを行うことを検討してください。SQL Server はこれらのタスクで Integration Services よりも優れています。もちろん、変換ロジックは、パッケージでいくつかの変換を実行する前に集計を禁止するようなものです。
たとえば、データベース内の月次データを集計（およびピボット/アンピボット）できる場合は、ソースクエリまたは宛先データベースで SQL を使用して実行してみてください。環境によっては、宛先データベースの別のテーブルに書き込み、インデックスを作成し、SQL を使用して集計する SELECT INTO の方が、パッケージで実行するよりも高速になる可能性があります。このようなアクティビティを並列化すると、ストレージに大きな負荷がかかることに注意してください。
最後にマルチキャストがあります。そこに何行が届くかはわかりませんが、次のことを検討してください。右側の宛先に書き込み (スクリーンショット上)、次に SQL クエリで左側の宛先にレコードを入力します (2 番目の集計をなくし、リソースを解放するため - SQL Server ではおそらくはるかに高速に実行されます)。

目的地関連の最適化:

使用SQL Server 宛先可能であれば (パッケージはデータベースと同じサーバーで実行する必要があり、宛先データベースは SQL Server である必要があります)、列のデータ型が完全に一致する必要があることに注意してください (パイプライン -> テーブル列)。
設定を検討する復旧モデル宛先（データウェアハウス）データベースをシンプルにする
宛先を並列化する - すべてを結合する代わりに、集約と宛先を別々に使用し、同じテーブルに別の宛先を使用します。ここでは、次の点を考慮する必要があります。パーティション分割宛先テーブルとパーティションを別々のファイルグループに配置します。データを月ごとに処理する場合は、月ごとにパーティションを作成し、パーティション切り替え

並列処理でどの方法を採用するかについては不明瞭なままだったようです。以下を試してみてください。

複数のソースを単一のデータフローに配置するには、各ソースの変換ロジックと宛先をコピーして貼り付ける必要があります。
複数のデータフローを並列に実行し、各データフローが1か月分のみを処理する
複数のパッケージを並列で実行し、各パッケージに 1 か月だけを処理する 1 つのデータフローと、各 (月) パッケージの実行を制御する 1 つのマスターパッケージがあります。これは、本番環境に移行したらパッケージを 1 か月間だけ実行することになるため、推奨される方法です。
または前と同じですが、Balanced Data DistributorとUnion AllとAggregateを使用します

他の作業を行う前に、簡単なテストを行うことをお勧めします。元のパッケージを取得し、1 か月使用するように変更し、別の月を処理する正確なコピーを作成して、それらのパッケージを並行して実行します。元のパッケージが 2 か月を処理する場合と比較します。同じことを、2 つの個別の 6 か月パッケージと 1 つの 12 か月パッケージに対して同時に実行します。サーバーが CPU を最大使用率で実行するはずです。

宛先への書き込みが複数回行われるため、過度に並列化しないようにしてください。そのため、18 個の並列月次パッケージではなく、3 個または 4 個から開始することをお勧めします。

そして最後に、メモリと宛先 I/O の圧力は排除されるべきものであると強く信じています。

進捗状況をお知らせください。

Question 2

使用プロセスエクスプローラーリソース使用量 (メモリと IO) をさらに明らかにします。ディスク IO グラフは、グラフのピークがハードドライブのキャッシュ機能によるものであることが多いため、少し誤解を招く可能性があることに注意してください。そのため、ディスク IO がボトルネックになっている場合、必ずしもグラフにすぐに現れるわけではありません。

場合によっては、RAMドライブをインストールしてそこに一時ディレクトリを置くことでメリットが得られます。私はこれですビルドマシンが夜間に完全なビルドとテストを実行するのに要する時間を短縮します。ただし、SSIS が恩恵を受けるかどうかはわかりません。

Answer

使用プロセスエクスプローラーリソース使用量 (メモリと IO) をさらに明らかにします。ディスク IO グラフは、グラフのピークがハードドライブのキャッシュ機能によるものであることが多いため、少し誤解を招く可能性があることに注意してください。そのため、ディスク IO がボトルネックになっている場合、必ずしもグラフにすぐに現れるわけではありません。

場合によっては、RAMドライブをインストールしてそこに一時ディレクトリを置くことでメリットが得られます。私はこれですビルドマシンが夜間に完全なビルドとテストを実行するのに要する時間を短縮します。ただし、SSIS が恩恵を受けるかどうかはわかりません。

Question 3

(最初の回答を再投稿します。BDD は考慮していません)

結局のところ、すべての処理は4つの要素のいずれかによって制限される。

メモリ
CPU
ディスク
通信網

最初のステップは、制限要因が何であるかを特定し、それに影響を与えることができるかどうか（使用量を増やすか減らすか）を判断することです。

コンポーネントの選択

18か月以上処理するとサーバーのメモリが不足する理由は、処理に時間がかかることに関係しています。ピボットと集計変換非同期コンポーネントです。ソースコンポーネントから入ってくるすべての行には、N バイトのメモリが割り当てられます。同じデータバケットがすべての変換にアクセスし、操作が適用されて、宛先で空になります。そのメモリバケットは何度も再利用されます。

非同期コンポーネントがアリーナに入ると、パイプラインが分割されます。パイプラインを完了するには、そのデータ行を転送していたバケットを新しいバケットに空にする必要があります。実行ツリー間でデータをコピーすることは、実行時間とメモリの点でコストのかかる操作です (2 倍になる可能性があります)。これにより、エンジンが非同期操作の完了を待機するため、実行機会の一部を並列化する機会も減ります。変換の性質により、操作のさらなる速度低下が発生します。Aggregate は完全にブロックするコンポーネントであるため、全て変換によって下流の変換に 1 行がリリースされる前に、データが到着して処理される必要があります。

可能であれば、ピボットや集計をサーバーにプッシュできますか? これにより、データフローに費やされる時間と消費されるリソースが削減されるはずです。

エンジンが選択できる並列操作の量を増やしてみることもできます。ジェイミーの記事、SQL CATの記事

データフローのどこで時間が費やされているかを本当に知りたい場合は、実行時にOnPipelineRowsSentをログに記録します。その後、これを使用できます。クエリそれを分解する（sysdtslog90をsysssislogに置き換えた後）

ネットワーク転送

グラフを見ると、どちらのボックスでも CPU やメモリに負担がかかっていないようです。ソースサーバーと宛先サーバーは 1 つのボックスにあるとおっしゃっていると思いますが、SSIS パッケージは別のボックスでホストされ、処理されます。そのデータをネットワーク経由で転送して戻すには、少なからずコストがかかります。ソースサーバーでデータを処理することは可能ですか? そのボックスにはより多くのリソースを割り当てる必要がありますが、それが大きな強力な VM で問題にならないことを祈ります。

それができない場合は、設定を試してみてくださいパケットサイズ接続マネージャーのプロパティを 32767 に設定し、ジャンボフレームが適切かどうかをネットワークオペレーターに問い合わせてください。これらのヒントは両方とも、「ネットワークの調整」セクションにあります。

私はディスクカウンターが苦手ですが、待機タイプがディスク関連かどうかは確認できるはずです。

Answer

(最初の回答を再投稿します。BDD は考慮していません)

結局のところ、すべての処理は4つの要素のいずれかによって制限される。

メモリ
CPU
ディスク
通信網

最初のステップは、制限要因が何であるかを特定し、それに影響を与えることができるかどうか（使用量を増やすか減らすか）を判断することです。

コンポーネントの選択

18か月以上処理するとサーバーのメモリが不足する理由は、処理に時間がかかることに関係しています。ピボットと集計変換非同期コンポーネントです。ソースコンポーネントから入ってくるすべての行には、N バイトのメモリが割り当てられます。同じデータバケットがすべての変換にアクセスし、操作が適用されて、宛先で空になります。そのメモリバケットは何度も再利用されます。

非同期コンポーネントがアリーナに入ると、パイプラインが分割されます。パイプラインを完了するには、そのデータ行を転送していたバケットを新しいバケットに空にする必要があります。実行ツリー間でデータをコピーすることは、実行時間とメモリの点でコストのかかる操作です (2 倍になる可能性があります)。これにより、エンジンが非同期操作の完了を待機するため、実行機会の一部を並列化する機会も減ります。変換の性質により、操作のさらなる速度低下が発生します。Aggregate は完全にブロックするコンポーネントであるため、全て変換によって下流の変換に 1 行がリリースされる前に、データが到着して処理される必要があります。

可能であれば、ピボットや集計をサーバーにプッシュできますか? これにより、データフローに費やされる時間と消費されるリソースが削減されるはずです。

エンジンが選択できる並列操作の量を増やしてみることもできます。ジェイミーの記事、SQL CATの記事

データフローのどこで時間が費やされているかを本当に知りたい場合は、実行時にOnPipelineRowsSentをログに記録します。その後、これを使用できます。クエリそれを分解する（sysdtslog90をsysssislogに置き換えた後）

ネットワーク転送

グラフを見ると、どちらのボックスでも CPU やメモリに負担がかかっていないようです。ソースサーバーと宛先サーバーは 1 つのボックスにあるとおっしゃっていると思いますが、SSIS パッケージは別のボックスでホストされ、処理されます。そのデータをネットワーク経由で転送して戻すには、少なからずコストがかかります。ソースサーバーでデータを処理することは可能ですか? そのボックスにはより多くのリソースを割り当てる必要がありますが、それが大きな強力な VM で問題にならないことを祈ります。

それができない場合は、設定を試してみてくださいパケットサイズ接続マネージャーのプロパティを 32767 に設定し、ジャンボフレームが適切かどうかをネットワークオペレーターに問い合わせてください。これらのヒントは両方とも、「ネットワークの調整」セクションにあります。

私はディスクカウンターが苦手ですが、待機タイプがディスク関連かどうかは確認できるはずです。

SSIS プロセスでより多くのリソースを消費し、より高速に実行するにはどうすればよいですか?

答え1

答え2

答え3

コンポーネントの選択

ネットワーク転送

関連情報