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 Wertes 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
  , Verwenden Sie den Hinweis bzw. die Option KILOBYTES_PER_BATCH, um die Größe der Datendatei in KB zu schätzen. SQL Server optimiert den Massenimport gemäß dem angegebenen Wert.

Hinweis:
Wenn Sie einen Massenimport für eine große Datendatei ausführen, ohne die Batchgröße oder andere Optimierungseinstellungen für die Massenprotokollierung 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.

Siehe auch

Konzepte

Optimieren der Leistung des Massenimportierens

Andere Ressourcen

BACKUP (Transact-SQL)
bcp (Dienstprogramm)
BULK INSERT (Transact-SQL)
ImportRowsPerBatch Property
OPENROWSET (Transact-SQL)
sp_dboption (Transact-SQL)

Hilfe und Informationen

Informationsquellen für SQL Server 2005