Artikel
03/30/2012

Verwalten von Batches für den Massenimport

In diesem Abschnitt wird die Vorgehensweise beim Bestimmen der Batchgröße für einen Massenimportvorgang erläutert. Standardmäßig werden alle Zeilen in einer Datendatei als einzelner Batch unbekannter Größe in einer einzelnen Transaktion importiert. Wenn in diesem Fall der Importvorgang vor Beendigung fehlschlägt, wird ein Rollback für die gesamte Transaktion ausgeführt, und der Zieltabelle werden keine Daten hinzugefügt. Der fehlgeschlagene Vorgang muss dann ab dem Anfang der Datendatei neu gestartet werden.

Das Importieren einer großen Datendatei als einzelner Batch kann problematisch sein. Deshalb können Sie mit bcp und BULK INSERT, Daten in einer Folge von Batches importieren, die jeweils kleiner als die Datendatei sind. Jeder Batch wird in einer gesonderten Transaktion importiert und protokolliert. Nach dem Commit einer bestimmten Transaktion wird ein Commit der bei dieser Transaktion importierten Zeilen durchgeführt. Wenn der Vorgang einen Fehler generiert, wird ein Rollback ausschließlich der aus dem aktuellen Batch importierten Zeilen durchgeführt. So können Sie den Importvorgang am Anfang des fehlgeschlagenen Batches fortsetzen, statt am Anfang der Datendatei.

Hinweis
Weitere Informationen zur Funktionsweise von Batches finden Sie unter Batches.

Wenn Sie die Batchgröße nicht begrenzen möchten, können Sie die Leistung auch verbessern, indem Sie die Größe der Datendatei im Befehl schätzen. Ihre Schätzung wird vom Abfrageprozessor verwendet, wenn der Abfrageplan für den Vorgang erstellt wird.

Hinweis
Genauigkeit ist beim Angeben der Batch- bzw. Datendateigröße unerheblich.

In der folgenden Tabelle sind die Qualifizierer zusammengefasst, die diese Alternativen unterstützen.

Befehl	Batchgröße	Pro Batch gesendete Zeilen	Pro Batch gesendete KB
bcp1	-bbatch_size	-h "ROWS_PER_BATCH = bb"	-h "KILOBYTES_PER_BATCH = cc"
BULK INSERT2	BATCHSIZE = batch_size	ROWS_PER_BATCH = rows_per_batch	KILOBYTES_PER_BATCH = kilobytes_per_batch Hinweis Das Verwenden dieser Option zusammen mit BATCHSIZE führt zu einem Fehler.
INSERT ... SELECT * FROM OPENROWSET (BULK...)	—3	ROWS_PER_BATCH = rows_per_batch	—3

1 Verwenden Sie bei bcp-Befehlen den Schalter -bbatch_size nicht zusammen mit dem Hinweis ROWS_PER_BATCH bzw. KILOBYTES_PER_BATCH. Eine Kombination dieser Elemente verursacht Fehler.

2 Wenn Sie bei einem BULK INSERT-Befehl BATCHSIZE zusammen mit ROWS_PER_BATCH bzw. KILOBYTES_PER_BATCH verwenden, hat BATCHSIZE den Vorrang.

3 Für OPENROWSET gibt es weder die Option BATCHSIZE noch die Option KILOBYTES_PER_BATCH.

In den folgenden Abschnitten wird beschrieben, wie diese Qualifizierer verwendet werden.

Angeben der ungefähren Batchgröße

Wenn Sie eine besonders große Anzahl von Zeilen importieren, ist es u. U. vorteilhaft, die Daten in Batches zu unterteilen. Die Transaktion wird jeweils nach Abschluss der einzelnen Batches protokolliert. Wenn der Massenimportvorgang aus irgendeinem Grund verfrüht beendet wird, wird nur für die aktuelle Transaktion (Batch) ein Rollback ausgeführt.

Hinweis
Bei der Massenprotokollierung wird eine Kopie der importierten Daten im Transaktionsprotokoll abgelegt. Dadurch kann das Protokoll schnell anwachsen, aber Sie können das Protokoll nach jedem Batch sichern, um wieder Protokollspeicherplatz freizugeben.

Um die Datendatei in einer Folge von Batches mit einer ungefähr angegebenen Größe zu importieren, verwenden Sie den folgenden Qualifizierer:

For bcp: -b
Für BULK INSERT: BATCHSIZE

Jeder Zeilenbatch wird als eine gesonderte Transaktion eingefügt. Wenn der Massenimportvorgang aus irgendeinem Grund verfrüht beendet wird, wird nur für die aktuelle Transaktion ein Rollback ausgeführt. Wenn beispielsweise in einer Datendatei 1000 Zeilen enthalten sind und die Batchgröße 100 verwendet wird, protokolliert Microsoft SQL Server den Vorgang als 10 getrennte Transaktionen, wobei jede Transaktion 100 Zeilen in die Zieltabelle einfügt. Wird der Massenimportvorgang während des Ladens von Zeile 750 beendet, werden nur die vorhergehenden 49 Zeilen entfernt, da SQL Server ein Rollback für die aktuelle Transaktion ausführt. Die ersten 700 Zeilen sind noch immer in der Zieltabelle enthalten.

SQL Server optimiert die Ladung hinsichtlich des Werts der Batchgröße automatisch, was zu einer verbesserten Leistung führen kann. Im Allgemeinen sollte die angegebene Batchgröße möglichst groß sein. Je umfangreicher die Batchgröße, desto besser ist meist die Leistung des Massenimportvorgangs. Es gibt jedoch einige Ausnahmen. Wenn die Zieltabelle mindestens einen Index enthält, steht bei einer umfangreicheren Batchgröße möglicherweise nicht genügend Arbeitsspeicher für das Sortieren zur Verfügung. Wenn ein paralleler Ladevorgang ohne die Option TABLOCK ausgeführt wird, kann eine umfangreichere Batchgröße darüber hinaus zu weiteren Sperren führen.

Hinweis
Bei einem Massenexport von Daten aus einer Instanz von SQL Server in eine Datendatei sind Batchgrößen nicht anwendbar.

Angeben der ungefähren Größe einer Datendatei

Wenn Sie für den Massenimport keine Batchgröße angeben, können Sie die ungefähre Größe der Datendatei angeben, damit der Abfrageprozessor Ressourcen im Abfrageplan effektiv zuordnen kann. Um die ungefähre Größe der Datendatei anzugeben, schätzen Sie entweder die Anzahl der Zeilen oder die Anzahl der KB für die Daten folgendermaßen:

Schätzen der Zeilen pro Batch

Um die Anzahl der Zeilen zu schätzen, verwenden Sie den Hinweis bzw. die Option ROWS_PER_BATCH. Wenn Sie einen Wert > 0 angeben, verwendet der Abfrageprozessor den Wert unter ROWS_PER_BATCH als Hinweis für die Zuordnung der Ressourcen im Abfrageplan. Der Wert sollte von der gleichen Größenordnung sein wie die tatsächliche Zeilenanzahl.

Obwohl alle Zeilen aus der Datendatei in einem Batch in eine Instanz von SQL Server kopiert werden, zeigt bcp die Meldung "1000 Zeilen an SQL Server gesendet" nach jeweils 1.000 Zeilen an. Diese Meldung dient nur zur Information und wird unabhängig von der Batchgröße angezeigt.
Schätzen der Kilobytes pro Batch

Um die Größe der Datendatei in Kilobyte zu schätzen, verwenden Sie den KILOBYTES_PER_BATCH-Hinweis oder die KILOBYTES_PER_BATCH-Option. SQL Server optimiert den Massenimportvorgang gemäß des angegebenen Werts.

Hinweis
Wenn Sie einen Massenimport für eine große Datendatei ausführen, ohne die Batchgröße oder andere Optimierungseinstellungen für die minimale Protokollierung anzugeben, ist das Transaktionsprotokoll möglicherweise bereits vor dem Abschluss des Massenimportvorgangs voll. Um dies zu vermeiden, können Sie das Transaktionsprotokoll vergrößern oder festlegen, dass es bei Bedarf automatisch vergrößert wird.

Wenn Sie einen Massenimport für eine große Datendatei ausführen, ohne die Batchgröße oder andere Optimierungseinstellungen für die minimale Protokollierung anzugeben, ist das Transaktionsprotokoll möglicherweise bereits vor dem Abschluss des Massenimportvorgangs voll. Um dies zu vermeiden, können Sie das Transaktionsprotokoll vergrößern oder festlegen, dass es bei Bedarf automatisch vergrößert wird.