SqlBulkCopy работает медленно, не использует полную скорость сети

database networking smo sqlbulkcopy sql-server

Вопрос

за последние пару недель я создавал общий сценарий, способный копировать базы данных. Цель состоит в том, чтобы иметь возможность указывать любую базу данных на каком-либо сервере и копировать ее в другое место, и она должна копировать только указанный контент. Точный контент, который нужно скопировать, указан в файле конфигурации. Этот скрипт будет использоваться в 10 разных базах данных и запускаться еженедельно. И в итоге мы копируем только около 3% -20% баз данных размером до 500 ГБ. Для этого я использовал сборки SMO. Это мой первый опыт работы с SMO, и потребовалось некоторое время, чтобы создать общий способ копирования объектов схемы, файловых групп ... и т. Д. (На самом деле помогли найти некоторые плохие хранимые процедуры).

В целом у меня есть рабочий скрипт, который не хватает производительности (и время от времени), и надеялся, что вы, ребята, сможете помочь. При выполнении команды WriteToServer для копирования большого объема данных (> 6 ГБ) он достигает моего тайм-аута в 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%. И мы будем делать копию каждой базы данных одновременно на разных серверах. Но при копировании одной из баз данных у меня все еще есть проблема с таймаутом.

При копировании одной из более крупных баз данных существует таблица, для которой я последовательно получаю следующее исключение:

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

Он запускается примерно через 16 минут после того, как он начинает копировать таблицу, которая не находится рядом с моим BulkCopyTimeout. Хотя я получаю исключение, что таблица полностью копируется в конце. Кроме того, если я усекаю эту таблицу и перезагружу свой процесс только для этой таблицы, таблицы будут скопированы без каких-либо проблем. Но процесс копирования всей базы данных не всегда выполняется для одной таблицы.

Я попытался выполнить весь процесс и перезапустить соединение, прежде чем копировать эту ошибочную таблицу, но она по-прежнему ошибочна. Мой 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
Является ли этот КБ законным? Да, узнайте, почему
Лицензировано согласно: CC-BY-SA with attribution
Не связан с Stack Overflow
Является ли этот КБ законным? Да, узнайте, почему