SqlBulkCopy est lent et n'utilise pas la pleine vitesse du réseau

database networking smo sqlbulkcopy sql-server

Question

Au cours des dernières semaines, j'ai créé un script générique capable de copier des bases de données. L'objectif est de pouvoir spécifier n'importe quelle base de données sur un serveur et de la copier dans un autre emplacement. Le contenu spécifié ne doit alors être copié. Le contenu exact à copier est spécifié dans un fichier de configuration. Ce script sera utilisé sur une dizaine de bases de données différentes et exécuté toutes les semaines. Enfin, nous ne copions que 3% à 20% des bases de données d’une capacité allant jusqu’à 500 Go. J'ai utilisé les assemblages SMO pour y parvenir. C'est la première fois que je travaille avec SMO et il m'a fallu un certain temps pour créer un moyen générique de copier les objets de schéma, les groupes de fichiers, etc. (En fait, aidé à trouver quelques mauvaises procédures stockées).

Globalement, j’ai un script de travail qui manque de performances (et parfois de temps mort) et espérais que vous pourrez nous aider. Lors de l'exécution de la commande WriteToServer pour copier une grande quantité de données (> 6 Go), le délai d'expiration de mon délai d'expiration est d'une heure. Voici le code principal pour la copie des données de la table. Le script est écrit dans 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

Les bases de données source et cible étant situées sur différents serveurs, j'ai donc également suivi la vitesse du réseau. L’utilisation du réseau n’a jamais dépassé 1%, ce qui m’a beaucoup étonné. Mais lorsque je ne fais que transférer des fichiers volumineux entre les serveurs, l'utilisation du réseau atteint 10%. J'ai essayé de définir le $ bulkData.BatchSize à 5000 mais rien n'a vraiment changé. Augmenter BulkCopyTimeout à un niveau encore plus élevé ne résoudrait que le délai d'attente. J'aimerais vraiment savoir pourquoi le réseau n'est pas pleinement utilisé.

quelqu'un d'autre a ce problème? Toute suggestion sur la mise en réseau ou une copie groupée sera appréciée. Et s'il vous plaît laissez-moi savoir si vous avez besoin de plus d'informations.

Merci.

METTRE À JOUR

J'ai modifié plusieurs options qui améliorent les performances de SqlBulkCopy, telles que la configuration de la journalisation des transactions sur simple et la fourniture d'un verrou de table à SqlBulkCopy au lieu du verrou de ligne par défaut. De plus, certaines tables sont mieux optimisées pour certaines tailles de lot. Dans l’ensemble, la durée de la copie a été réduite d’environ 15%. Et ce que nous allons faire est d’exécuter la copie de chaque base de données simultanément sur différents serveurs. Mais je rencontre toujours un problème de délai d’attente lors de la copie d’une des bases de données.

Lors de la copie d'une des plus grandes bases de données, il existe une table pour laquelle je reçois systématiquement l'exception suivante:

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

Il est lancé environ 16 minutes après le début de la copie de la table, qui n’est pas proche de mon BulkCopyTimeout. Même si j'ai l'exception, cette table est entièrement copiée à la fin. De plus, si je tronque cette table et ne redémarre mon processus que pour cette table, celle-ci est copiée sans aucun problème. Toutefois, le processus de copie de la base de données entière échoue toujours pour cette table.

J'ai essayé d'exécuter tout le processus et de réinitialiser la connexion avant de copier cette table défectueuse, mais celle-ci est toujours erronée. Mon SqlBulkCopy et Reader sont fermés après chaque table. Avez-vous des suggestions quant à ce qui pourrait causer l’échec du script à chaque fois?

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]

Aucun index n'existe pour cette table sur le DB cible.

Réponse acceptée

Avez-vous envisagé de supprimer des index, d'effectuer l'insertion, puis de réindexer?


Réponse populaire

SqlBulk Copy est de loin le moyen le plus rapide de copier des données dans des tables SQL.
Vous devriez obtenir une vitesse supérieure à 10 000 lignes par seconde.
Afin de tester la fonctionnalité de copie en bloc, essayez DBSourceTools. ( http://dbsourcetools.codeplex.com )
Cet utilitaire est conçu pour écrire des bases de données sur disque, puis les recréer sur un serveur cible.
Lors de la copie de données, DBSourceTools exporte d'abord toutes les données dans un fichier .xml local, puis effectue une copie en bloc dans la base de données cible.
Cela vous aidera à identifier plus précisément votre goulot d'étranglement en divisant le processus en deux étapes: une pour la lecture et une pour l'écriture.



Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi
Sous licence: CC-BY-SA with attribution
Non affilié à Stack Overflow
Est-ce KB légal? Oui, apprenez pourquoi