SqlBulkCopyは遅く、完全なネットワーク速度を利用しません

database networking smo sqlbulkcopy sql-server

質問

過去数週間、私はデータベースをコピーできる汎用スクリプトを作成していました。目標は、一部のサーバー上のデータベースを指定して他の場所にコピーできるようにすることです。指定されたコンテンツのみをコピーする必要があります。コピーされる正確なコンテンツは、設定ファイルで指定されます。このスクリプトは、いくつかの異なるデータベースで使用され、毎週実行されます。そして最終的には500GBほどのデータベースの約3%-20%のみをコピーしています。私はこれを達成するためにSMOアセンブリを使用してきました。これはSMOで初めての作業であり、スキーマオブジェクト、ファイルグループなどをコピーする一般的な方法を作成するまでには時間がかかりました。 (実際に悪い格納procsを見つけるのを助けた)。

全体的に私はパフォーマンスに欠けている(そして時にはタイムアウトする)作業スクリプトを用意しており、皆さんが助けてくれることを願っていました。 WriteToServerコマンドを実行して大量のデータ(> 6GB)をコピーすると、タイムアウト時間が1時間に達します。テーブルデータをコピーするためのコアコードです。このスクリプトはPowerShellで書かれています。

$query = ("SELECT * FROM $selectedTable " + $global:selectiveTables.Get_Item($selectedTable)).Trim()
Write-LogOutput "Copying $selectedTable : '$query'"            
$cmd = New-Object Data.SqlClient.SqlCommand -argumentList $query, $source
$cmd.CommandTimeout = 120;
$bulkData = ([Data.SqlClient.SqlBulkCopy]$destination)
$bulkData.DestinationTableName = $selectedTable;
$bulkData.BulkCopyTimeout = $global:tableCopyDataTimeout # = 3600
$reader = $cmd.ExecuteReader();
$bulkData.WriteToServer($reader); # Takes forever here on large tables

ソースとターゲットのデータベースは別々のサーバーに配置されているため、ネットワークの速度も把握していました。ネットワークの利用率は1%を超えず、これは私には非常に驚きでした。しかし、サーバー間で大きなファイルを転送すると、ネットワークの使用率が最大10%上昇します。私は$ bulkData.BatchSizeを5000に設定しようとしましたが、実際には何も変わりません。 BulkCopyTimeoutをさらに大きくすると、タイムアウトのみが解決されます。私は本当にネットワークが完全に使われていない理由を知りたいです。

誰もがこの問題を抱えていた?ネットワーキングまたは一括コピーに関するご意見をお待ちしております。さらに詳しい情報が必要な場合はお知らせください。

ありがとう。

更新

トランザクションログを単純に設定し、既定の行ロックの代わりにSqlBulkCopyにテーブルロックを提供するなど、SqlBulkCopyのパフォーマンスを向上させるいくつかのオプションを調整しました。また、一部の表は、特定のバッチサイズに対して最適化されています。全体的に、コピーの所要時間は約15%減少しました。また、各データベースのコピーを異なるサーバー上で同時に実行することもできます。しかし、私はまだデータベースの1つをコピーするときにタイムアウトの問題が発生しています。

大きなデータベースの1つをコピーすると、次の例外を一貫して取得するテーブルがあります。

System.Data.SqlClient.SqlException: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. 

それは私のBulkCopyTimeoutの近くのどこにもないテーブルのコピーを開始してから約16分後にスローされます。テーブルが最後に完全にコピーされているという例外がありますが。また、そのテーブルを切り捨てて、そのテーブルのプロセスを再起動しても、テーブルは問題なくコピーされます。しかし、そのデータベース全体をコピーするプロセスは、常にその1つのテーブルで失敗します。

私は、プロセス全体を実行し、その障害のあるテーブルをコピーする前に接続をリセットしようとしましたが、それでもエラーが発生しました。私のSqlBulkCopyとReaderは各テーブルの後に閉じます。その都度、スクリプトがその時点で失敗する原因となるものは何ですか?

CREATE TABLE [dbo].[badTable](
[someGUID] [uniqueidentifier] NOT NULL,
[xxx] [uniqueidentifier] NULL,
[xxx] [int] NULL,
[xxx] [tinyint] NOT NULL,
[xxx] [datetime] NOT NULL,
[xxx] [datetime] NOT NULL,
[xxx] [datetime] NOT NULL,
[xxx] [datetime] NULL,
[xxx] [uniqueidentifier] NOT NULL,
[xxx] [uniqueidentifier] NULL,
CONSTRAINT [PK_badTable] PRIMARY KEY NONCLUSTERED 
(
[someGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ターゲットDBのこのテーブルのインデックスは存在しません。

受け入れられた回答

インデックスの削除、挿入の実行、再インデックス付けを検討しましたか?


人気のある回答

SqlBulk Copyは、データをSQLテーブルにコピーするのに最も速い方法です。
毎秒10,000行を超える速度が得られるはずです。
一括コピー機能をテストするには、DBSourceToolsを試してみてください。 ( http://dbsourcetools.codeplex.com
このユーティリティは、データベースをディスクにスクリプト化し、ターゲットサーバーで再作成するように設計されています。
データをコピーするとき、DBSourceToolsはまずすべてのデータをローカルの.xmlファイルにエクスポートし、次にターゲット・データベースにバルク・コピーを行います。
プロセスを2つのパスに分けることで、ボトルネックがどこにあるかをさらに特定することができます.1つは読み取り用、もう1つは書き込み用です。



ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ
ライセンスを受けた: CC-BY-SA with attribution
所属していない Stack Overflow
このKBは合法ですか? はい、理由を学ぶ