SqlBulkCopy è lento, non utilizza la piena velocità di rete

database networking smo sqlbulkcopy sql-server

Domanda

da un paio di settimane ho creato uno script generico in grado di copiare database. L'obiettivo è essere in grado di specificare qualsiasi database su un server e copiarlo in un'altra posizione, e dovrebbe solo copiare il contenuto specificato. Il contenuto esatto da copiare è specificato in un file di configurazione. Questo script verrà utilizzato su 10 diversi database ed eseguito settimanalmente. E alla fine stiamo copiando solo il 3% -20% dei database di dimensioni pari a 500 GB. Ho usato gli assembly SMO per raggiungere questo obiettivo. Questa è la prima volta che lavoro con SMO e ci è voluto un po 'di tempo per creare un modo generico per copiare gli oggetti dello schema, i filegroup ... ecc. (In realtà ha aiutato a trovare alcuni proc memorizzati male).

Nel complesso, ho una sceneggiatura di lavoro che manca di prestazioni (ea volte va fuori) e speravo che voi ragazzi poteste aiutare. Quando si esegue il comando WriteToServer per copiare grandi quantità di dati (> 6 GB), raggiunge il mio periodo di timeout di 1 ora. Ecco il codice principale per copiare i dati della tabella. Lo script è scritto in 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

I database di origine e di destinazione si trovano su server diversi, quindi ho tenuto traccia della velocità della rete. L'utilizzo della rete non è mai andato oltre l'1%, il che è stato abbastanza sorprendente per me. Ma quando trasferisco solo file di grandi dimensioni tra i server, l'utilizzo della rete raggiunge il 10%. Ho provato a impostare $ bulkData.BatchSize su 5000 ma non è cambiato nulla. Aumentare il BulkCopyTimeout a un livello ancora maggiore risolverebbe solo il timeout. Mi piacerebbe davvero sapere perché la rete non viene utilizzata completamente.

Qualcun altro ha avuto questo problema? Saranno apprezzati eventuali suggerimenti sul networking o sulla copia di massa. E per favore fatemi sapere se avete bisogno di ulteriori informazioni.

Grazie.

AGGIORNARE

Ho modificato diverse opzioni per aumentare le prestazioni di SqlBulkCopy, ad esempio impostando la registrazione delle transazioni in modo semplice e fornendo un blocco di tabella a SqlBulkCopy invece del blocco di riga predefinito. Inoltre, alcune tabelle sono meglio ottimizzate per determinate dimensioni di batch. Complessivamente, la durata della copia è stata ridotta di circa il 15%. E quello che faremo è eseguire la copia di ogni database contemporaneamente su server diversi. Ma sto ancora avendo un problema di timeout durante la copia di uno dei database.

Quando si copia uno dei database più grandi, esiste una tabella per la quale ottengo costantemente la seguente eccezione:

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

Viene lanciato circa 16 minuti dopo che inizia a copiare il tavolo, che non è vicino al mio BulkCopyTimeout. Anche se ottengo l'eccezione che la tabella viene completamente copiata alla fine. Inoltre, se ho troncato quella tabella e riavviato il mio processo solo per quella tabella, le tabelle vengono copiate senza problemi. Ma passare attraverso il processo di copia dell'intero database fallisce sempre per quella tabella.

Ho provato a eseguire l'intero processo e resettare la connessione prima di copiare quella tabella difettosa, ma ancora errata. My SqlBulkCopy e Reader sono chiusi dopo ogni tabella. Qualche suggerimento su quale altro potrebbe essere il motivo per cui lo script fallisce al punto ogni volta?

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]

Nessun indice esiste per questa tabella sul DB di destinazione.

Risposta accettata

Hai preso in considerazione la rimozione degli indici, l'inserimento e la reindicizzazione?


Risposta popolare

SqlBulk Copy è di gran lunga il modo più veloce di copiare i dati in tabelle SQL.
Dovresti ottenere velocità superiori a 10.000 file al secondo.
Per testare la funzionalità di copia bulk, prova DBSourceTools. ( http://dbsourcetools.codeplex.com )
Questa utilità è progettata per eseguire script di database su disco e quindi ricrearli su un server di destinazione.
Durante la copia dei dati, DBSourceTools prima esporta tutti i dati in un file .xml locale, quindi esegue una copia bulk nel database di destinazione.
Ciò consentirà di identificare ulteriormente dove si trova il collo di bottiglia, suddividendo il processo in due passaggi: uno per la lettura e uno per la scrittura.



Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché
Autorizzato sotto: CC-BY-SA with attribution
Non affiliato con Stack Overflow
È legale questo KB? Sì, impara il perché