SqlBulkCopy가 느리며 전체 네트워크 속도를 사용하지 않습니다.

database networking smo sqlbulkcopy sql-server

문제

지난 몇 주 동안 데이터베이스를 복사 할 수있는 일반 스크립트를 작성했습니다. 목표는 일부 서버에서 데이터베이스를 지정하고 다른 위치로 복사 할 수 있으며 지정된 내용 만 복사해야합니다. 복사 할 정확한 내용은 구성 파일에 지정됩니다. 이 스크립트는 약 10 개의 다른 데이터베이스에서 사용되며 매주 실행됩니다. 그리고 결국 우리는 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 % 감소했습니다. 그리고 우리가 할 일은 각 서버의 복사본을 다른 서버에서 동시에 실행하는 것입니다. 그러나 데이터베이스 중 하나를 복사 할 때 여전히 시간 초과 문제가 발생합니다.

대용량 데이터베이스 중 하나를 복사 할 때 다음과 같은 예외가 계속 발생하는 테이블이 있습니다.

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

내 BulkCopyTimeout 근처에없는 테이블을 복사하기 시작한 후 약 16 분 후에 발생합니다. 테이블이 끝까지 복사된다는 예외는 있지만 또한 해당 테이블을 자르고 해당 테이블에 대한 프로세스 만 다시 시작하면 테이블이 아무런 문제없이 복사됩니다. 그러나 전체 데이터베이스를 복사하는 과정을 거치면 항상 그 하나의 테이블에서 실패합니다.

내가 전체 프로세스를 실행하고 그 결함이있는 테이블을 복사하기 전에 연결을 다시 시도했지만 여전히 오류가 발생했습니다. 각 테이블 다음에 My 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 파일로 내 보낸 다음 대상 데이터베이스로 대량 복사합니다.
프로세스를 두 단계로 나누어 병목 현상이 어디 있는지 파악할 수 있습니다. 하나는 읽기 용이고 다른 하나는 쓰기 용입니다.



아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.