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合法嗎? 是的,了解原因