SqlBulkCopy很慢,不利用全网速

database networking smo sqlbulkcopy sql-server

在过去的几周里,我一直在创建能够复制数据库的通用脚本。目标是能够在某个服务器上指定任何数据库并将其复制到其他位置,并且它应该只复制指定的内容。要复制的确切内容在配置文件中指定。该脚本将用于大约10个不同的数据库并每周运行一次。最后,我们只复制了大约3%-20%的数据库,这些数据库大到500GB。我一直在使用SMO组件来实现这一目标。这是我第一次使用SMO,需要一段时间来创建复制模式对象,文件组等的通用方法。 (实际上帮助找到一些不好的存储过程)。

总的来说,我有一个工作脚本缺乏性能(有时超时),并希望你们能够提供帮助。执行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增加到更大的数量只会解决超时问题。我真的很想知道为什么网络没有被完全使用。

还有其他人有这个问题吗?有关网络或批量复制的任何建议将不胜感激。如果您需要更多信息,请告诉我。

谢谢。

UPDATE

我已经调整了几个提高SqlBulkCopy性能的选项,例如将事务日志记录设置为simple,并为SqlBulkCopy提供表锁,而不是默认的行锁。某些表格也针对某些批量大小进行了更好的优化。总体而言,副本的持续时间减少了约15%。我们要做的是在不同的服务器上同时执行每个数据库的副本。但是在复制其中一个数据库时,我仍然遇到超时问题。

复制其中一个较大的数据库时,有一个表,我一直得到以下异常:

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

在它开始复制没有靠近我的BulkCopyTimeout的表之后大约16分钟抛出它。即使我得到了表格最终被完全复制的例外情况。此外,如果我截断该表并仅为该表重新启动我的进程,则会复制这些表而不会出现任何问题。但是,通过复制整个数据库的过程总是会失败。

我已经尝试执行整个过程并在复制该故障表之前重置连接,但它仍然存在错误。我的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]

目标数据库上没有此表的索引。

一般承认的答案

您是否考虑过删除索引,执行插入操作,然后重新编制索引?


热门答案

到目前为止,SqlBulk Copy是将数据复制到SQL表中的最快方法。
您的速度应该超过每秒10,000行。
要测试批量复制功能,请尝试DBSourceTools。 ( http://dbsourcetools.codeplex.com
此实用程序旨在将数据库脚本编写到磁盘,然后在目标服务器上重新创建它们。
复制数据时,DBSourceTools将首先将所有数据导出到本地.xml文件,然后批量复制到目标数据库。
这有助于进一步确定瓶颈的位置,将过程分为两个过程:一个用于阅读,一个用于写入。




许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因
许可下: CC-BY-SA with attribution
不隶属于 Stack Overflow
这个KB合法吗? 是的,了解原因