SqlBulkCopy ist langsam, nutzt nicht die volle Netzwerkgeschwindigkeit

database networking smo sqlbulkcopy sql-server

Frage

In den letzten paar Wochen habe ich ein generisches Skript erstellt, das Datenbanken kopieren kann. Das Ziel besteht darin, jede Datenbank auf einem Server angeben und sie an einen anderen Speicherort kopieren zu können, und nur den angegebenen Inhalt zu kopieren. Der genaue Inhalt, der kopiert werden soll, wird in einer Konfigurationsdatei angegeben. Dieses Skript wird in 10 verschiedenen Datenbanken verwendet und wöchentlich ausgeführt. Und am Ende kopieren wir nur etwa 3% -20% der Datenbanken, die so groß wie 500 GB sind. Ich habe die SMO-Baugruppen verwendet, um dies zu erreichen. Dies ist das erste Mal, dass ich mit SMO arbeite und es dauerte eine Weile, um eine generische Methode zu erstellen, um die Schemaobjekte, Dateigruppen usw. zu kopieren. (Tatsächlich geholfen, einige schlechte gespeicherte Procs zu finden).

Insgesamt habe ich ein funktionierendes Skript, dem es an Performance (und manchmal auch an Zeit) mangelt und ich hatte gehofft, ihr könnt mir helfen. Wenn ich den WriteToServer-Befehl ausführe, um große Datenmengen (> 6 GB) zu kopieren, erreicht er meine Zeitüberschreitung von 1 Stunde. Hier ist der Kerncode zum Kopieren von Tabellendaten. Das Skript ist in PowerShell geschrieben.

$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

Die Quell- und Zieldatenbanken befinden sich auf verschiedenen Servern, so dass ich auch die Netzwerkgeschwindigkeit verfolgt habe. Die Netzwerkauslastung ist nie über 1% gestiegen, was für mich ziemlich überraschend war. Aber wenn ich nur einige große Dateien zwischen den Servern übertrage, steigt die Netzwerkauslastung um bis zu 10%. Ich habe versucht, die $ bulkData.BatchSize auf 5000 zu setzen, aber nichts hat sich wirklich geändert. Wenn Sie BulkCopyTimeout auf einen noch größeren Wert erhöhen, wird nur das Zeitlimit behoben. Ich würde gerne wissen, warum das Netzwerk nicht vollständig genutzt wird.

Hat noch jemand dieses Problem? Irgendwelche Vorschläge zum Netzwerken oder Massenkopieren werden geschätzt. Und bitte lassen Sie mich wissen, wenn Sie weitere Informationen benötigen.

Vielen Dank.

AKTUALISIEREN

Ich habe mehrere Optionen optimiert, die die Leistung von SqlBulkCopy erhöhen, wie z. B. das Festlegen der Transaktionsprotokollierung auf "Einfach" und das Bereitstellen einer Tabellensperre für SqlBulkCopy anstelle der Standardzeilensperre. Einige Tabellen sind auch besser für bestimmte Batchgrößen optimiert. Insgesamt wurde die Dauer der Kopie um etwa 15% verringert. Und wir werden die Kopie jeder Datenbank gleichzeitig auf verschiedenen Servern ausführen. Aber ich habe immer noch ein Timeout-Problem beim Kopieren einer der Datenbanken.

Beim Kopieren einer der größeren Datenbanken gibt es eine Tabelle, für die ich immer die folgende Ausnahme bekomme:

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

Es wird ca. 16 Minuten nach dem Kopieren der Tabelle geworfen, die sich nicht in der Nähe von BulkCopyTimeout befindet. Obwohl ich die Ausnahme bekomme, dass die Tabelle am Ende vollständig kopiert wird. Wenn ich diese Tabelle abschneide und meinen Prozess nur für diese Tabelle neu starte, werden die Tabellen ohne Probleme kopiert. Das Durchlaufen der gesamten Datenbank schlägt jedoch immer für diese eine Tabelle fehl.

Ich habe versucht, den gesamten Prozess auszuführen und die Verbindung vor dem Kopieren dieser fehlerhaften Tabelle wiederherzustellen, aber es ist immer noch fehlerhaft. Mein SqlBulkCopy und Reader sind nach jeder Tabelle geschlossen. Irgendwelche Vorschläge, was sonst noch dazu führen könnte, dass das Skript jedes Mal zum Scheitern verurteilt wäre?

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]

Für diese Tabelle im Ziel-DB existieren keine Indizes.

Akzeptierte Antwort

Haben Sie darüber nachgedacht, Indizes zu entfernen, die Einfügung durchzuführen und dann neu zu indizieren?


Beliebte Antwort

SqlBulk Copy ist bei weitem die schnellste Möglichkeit, Daten in SQL-Tabellen zu kopieren.
Sie sollten Geschwindigkeiten von mehr als 10.000 Zeilen pro Sekunde erhalten.
Versuchen Sie DBSourceTools, um die Massenkopierfunktionalität zu testen. ( http://dbsourcetools.codeplex.com )
Dieses Dienstprogramm wurde entwickelt, um Datenbanken auf Datenträger zu schreiben und sie dann auf einem Zielserver neu zu erstellen.
Beim Kopieren von Daten exportiert DBSourceTools zuerst alle Daten in eine lokale XML-Datei und führt dann eine Massenkopie zur Zieldatenbank durch.
Dies wird helfen, den Engpass genauer zu identifizieren, indem Sie den Prozess in zwei Durchgänge aufteilen: einen zum Lesen und einen zum Schreiben.



Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow
Ist diese KB legal? Ja, lerne warum
Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow
Ist diese KB legal? Ja, lerne warum