SqlBulkCopy is sluggish and does not use the whole network bandwidth.

database networking smo sqlbulkcopy sql-server

Question

I have been developing a general script that can replicate databases for the last several weeks. The objective is to be able to specify any database on any server and transfer it to another place, just copying the content that has been provided. In a configuration file, the precise material to be transferred over is provided. This script will be used on around ten different databases and executed once a week. And in the end, we are only replicating 3–20 percent of datasets that are 500GB or more. To do this, I have been use the SMO assembly. Since this is my first experience with SMO, it took some effort to develop a general method of copying schema objects, filegroups, etc (Actually helped find some bad stored procs).

I have a functional script overall, but it sometimes times out and lacks performance. I was hoping you guys could assist. The WriteToServer command approaches my timeout limit of 1 hour while copying huge amounts of data (>6GB). The main code for copying table data may be found here. PowerShell is used to write the script.

$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

Because the source and destination databases are on separate servers, I also monitored the network speed. The fact that the network usage never exceeded 1% surprised me much. But the network use increases by up to 10% when I just transfer some huge files across the servers. The $bulkData.BatchSize was set to 5000, but it didn't seem to make a difference. The timeout would only be resolved by further increasing the BulkCopyTimeout. I truly want to know why the network isn't being used to its maximum potential.

Anyone else experience this issue? I'd be grateful for any advice on networking or mass copy. And if you need any other details, do let me know.

Thanks.

UPDATE

In order to improve SqlBulkCopy's efficiency, I changed a number of settings, such as switching the transaction logging to basic and giving it a table lock in place of the standard row lock. Additionally, certain tables are better suited for particular batch sizes. The length of the material was trimmed by around 15% overall. And what we'll do is run a copy of every database concurrently on many servers. However, I continue to get a timeout problem while transferring one of the databases.

There is a table for which I constantly receive the following error while replicating one of the bigger databases:

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

About 16 minutes after it begins copying the table, it throws an error, which is much longer than my BulkCopyTimeout. Even when I get an exception, the table is ultimately completely duplicated. Additionally, the tables are successfully transferred over if I truncate that table and restart my process for that table exclusively. However, duplicating that database's complete contents always fails for that one table.

Even after doing the full procedure and restarting the connection before transferring the problematic table, there was still an issue. I close my SqlBulkCopy and Reader after each table. Any other ideas as to why the script keeps failing at the same location repeatedly?

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]

On the target DB, this table has no indexes.

1
1
12/22/2010 8:41:08 PM

Accepted Answer

Have you given the idea of doing the insert, deleting the indexes, and then reindexing?

0
12/17/2010 10:28:11 PM

Popular Answer

The quickest method for transferring data into SQL tables is SqlBulk Copy.
You ought to experience speeds of more than 10,000 rows per second.
Try DBSourceTools to test the bulk copy feature. (http://dbsourcetools.codeplex.com)
With the help of this tool, databases may be written to disk and then recreated on a destination server.
In order to bulk transfer data to the destination database, DBSourceTools first exports all data to a local.xml file.
By dividing the process into two passes—one for reading and one for writing—you can more clearly see where your bottleneck is.



Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow