Artikel
03/30/2012

Richtlinien zur Optimierung von Massenimport

In diesem Thema werden Richtlinien erläutert, wie Sie die Leistung für verschiedene Massenimportszenarien optimieren.

Importieren von Daten aus einem einzelnen Client (oder Stream) in eine leere Tabelle.

Importieren von Daten aus einem einzelnen Client (oder Stream) in eine teilweise gefüllte, nicht leere Tabelle.

Hinweis
Das Importieren von Daten in eine nicht leere Tabelle wird auch als inkrementeller Massenimport bezeichnet. Bei jedem inkrementellen Massenimport stellt sich die entscheidende Frage, ob Indizes zuvor verworfen werden sollen.

Importieren von Daten mit Sperren auf Tabellenebene aus mehreren Clients gleichzeitig (oder Streams).
Kopieren von Daten zwischen Microsoft SQL Server-Instanzen.

Dieses Thema enthält eine Zusammenfassung des Sperr- und Protokollierungsverhaltens von Tabellen während Massenimportvorgängen.

Importieren von Daten aus einem einzelnen Client (oder Stream) in eine leere Tabelle

Wenn Sie Daten aus einem einzelnen Client (oder Stream) in eine leere Tabelle importieren möchten, sollten Sie die folgenden von Microsoft empfohlenen Schritte ausführen:

Geben Sie den TABLOCK-Qualifizierer an (dieser ist als Hinweis oder Option für alle drei Massenimportverfahren verfügbar). Mithilfe von TABLOCK wird für die Dauer des Massenvorgangs eine Sperre auf Tabellenebene errichtet und damit der zum Sperren einzelner Zeilen erforderliche Verarbeitungsaufwand beseitigt. Weitere Informationen finden Sie unter Steuern des Sperrverhaltens für den Massenimport.
Minimieren der Protokollierung. Weitere Informationen finden Sie unter Voraussetzungen für die minimale Protokollierung beim Massenimport.

Behandeln von Indizes in der folgenden Weise.

Wenn Sie bcp, BULK INSERT oder INSERT ... SELECT * FROM OPENROWSET(BULK...) verwenden und die Tabelle leer ist, einen gruppierten Index enthält und die Daten in der Datendatei so angeordnet sind, dass sie mit den Schlüsselspalten des gruppierten Index übereinstimmen, führen Sie zusätzlich folgende Schritte aus:

Massenimportieren Sie die Daten, die den gruppierten Index bereits enthalten.
Geben Sie den ORDER- sowie den TABLOCK-Hinweis an. Weitere Informationen finden Sie unter Steuern der Sortierreihenfolge beim Massenimport von Daten.

Bei leeren Tabellen wird für diese Vorgehensweise wesentlich weniger Zeit benötigt, als wenn der gruppierte Index erst nach dem Importieren der Daten erstellt wird, weil in diesem Fall die Sortierung wegfällt.

Hinweis
Bei nicht leeren Tabellen mit Indizes werden Massenimporte vollständig protokolliert. Das gilt auch für das Modell der massenprotokollierten Wiederherstellung. Die Entscheidung, ob Indizes entfernt werden sollen, ist davon abhängig, ob durch den Massenimport in eine Tabelle ohne Indizes ein größerer Vorteil erzielt wird, als beim Verwerfen und erneuten Erstellen der betreffenden Indizes.

Wenn Sie Daten in eine leere Tabelle mit Indizes massenimportieren und die Batchgröße angeben, ist die Tabelle nach dem ersten Batch nicht mehr leer. Beim Starten des zweiten Batches werden die Daten vollständig protokolliert. Bei leeren indizierten Tabellen sollte der Massenimport in einem einzelnen Batch ausgeführt werden.

Hinweis
Wenn eine Batchgröße standardmäßig nicht angegeben ist, nimmt der SQL Server-Abfrageoptimierer einen Standardwert für die Größe der Datendateien an. Um die Leistung zu verbessern, können Sie mithilfe des ROWS_PER_BATCH-Qualifizierers oder des KILOBYTES_PER_BATCH-Qualifizierers einen Hinweis für den Optimierer angeben, der die ungefähre Anzahl der Zeilen in der Datendatei enthält. Weitere Informationen finden Sie unter Verwalten von Batchgrößen für das Massenkopieren.

Wenn eine Batchgröße standardmäßig nicht angegeben ist, nimmt der SQL Server-Abfrageoptimierer einen Standardwert für die Größe der Datendateien an. Um die Leistung zu verbessern, können Sie mithilfe des ROWS_PER_BATCH-Qualifizierers oder des KILOBYTES_PER_BATCH-Qualifizierers einen Hinweis für den Optimierer angeben, der die ungefähre Anzahl der Zeilen in der Datendatei enthält. Weitere Informationen finden Sie unter Verwalten von Batchgrößen für das Massenkopieren.

Im Allgemeinen wird beim Massenimport von Tabellen ohne Indizes weniger Zeit benötigt als bei Tabellen mit Indizes. Bevor Sie Daten in eine leere Tabelle importieren, sollten Sie daher die vorhandenen Indizes verwerfen und im Anschluss erneut erstellen. Wenn die Daten nicht nach einer gruppierten Schlüsselspalte sortiert sind, und die Tabelle leer ist, verwerfen Sie alle Indizes, importieren Sie die Daten, und erstellen Sie die Indizes anschließend neu.

Importieren von Daten aus einem einzelnen Client (oder Stream) in eine nicht leere Tabelle

Das Importieren von Daten in eine nicht leere Tabelle (eine Tabelle, die bereits Daten enthält) wird auch als inkrementeller Massenimport bezeichnet. Bei jedem inkrementellen Massenimport stellt sich die entscheidende Frage, ob Indizes zuvor verworfen werden sollen. Hierfür stehen zwei Möglichkeiten zur Verfügung: Sie können die Indizes entweder beibehalten oder sie löschen und später wieder neu erstellen.

Wenn Sie Daten aus einem einzelnen Client in eine nicht leere Tabelle importieren, stellt sich die Frage, ob Sie die Indizes beibehalten sollen. Das ist davon abhängig, in welchem Verhältnis die neu zu importierende Datenmenge und die bereits vorhandene Datenmenge der Tabelle stehen.

Wenn Sie relativ zur vorhandenen Datenmenge nur eine kleine Menge an neuen Daten importieren, kann sich das Verwerfen und erneute Erstellen der Indizes nachteilig auswirken. Für das erneute Erstellen der Indizes wird wahrscheinlich mehr Zeit benötigt als durch den Massenvorgang gespart werden kann.
Beim Importieren einer großen Menge neuer Daten kann die Leistung dagegen verbessert werden, wenn die Indizes der Tabelle vor dem Massenvorgang verworfen werden. Die für die Indizierung benötigte Zeit wird dadurch nicht wesentlich erhöht.

Die folgende Tabelle enthält die Menge an neuen Daten, die zum Verwerfen von Indizes mindestens in einer Tabelle vorhanden sein sollten. Diese Mindestmenge ist proportional zur Gesamtmenge der Daten in der Tabelle. Die Menge variiert je nach den für die Indizes verwendeten Typen und Kombinationen. Wenn die neuen Daten den für einen bestimmten Indextyp oder eine bestimmte Indexgruppierung vorgeschlagenen Prozentsatz überschreiten, sollten Sie die Indizes vor dem Massenvorgang verwerfen und im Anschluss neu erstellen. Diese Zahlen sind vom Datenmuster der vorhandenen Daten und der zu ladenden Daten abhängig. Deshalb dienen Zahlen nur als allgemeine Richtlinie.

Indizes	Relative Menge neuer Daten
Nur gruppierter Index	30 Prozent
Gruppierte Indizes, mit einem nicht gruppierten Index	25 Prozent
Gruppierte Indizes, mit zwei nicht gruppierten Indizes	25 Prozent
Ein nicht gruppierter Index	100 Prozent
Zwei nicht gruppierte Indizes	60 Prozent

Importieren von Daten mit Sperren auf Tabellenebene aus mehreren Clients parallel (oder Streams)

Wenn SQL Server auf einem Computer mit mehreren Prozessoren ausgeführt wird, und die in eine Tabelle massenzuimportierenden Daten in verschiedene Datendateien aufgeteilt werden können, können Sie die Leistung verbessern, indem Sie die Daten aus mehreren Clients parallel in die Tabelle importieren. Wenn Sie Daten aus mehreren Clients in eine Tabelle massenimportieren, muss jeder Client über eine eigene Eingabedatendatei verfügen.

Wenn Sie Daten aus mehreren Clients in eine Tabelle importieren, sollten Sie Folgendes berücksichtigen:

Die einzelnen Massenimportstreams können sich gegenseitig blockieren.

Um dies zu verhindern, stellt SQL Server eine spezielle interne Sperre, die so genannte Massenaktualisierungssperre (BU, bulk update), bereit. Zum Errichten einer Massenaktualisierungssperre geben Sie für jeden Massenimportstream die Option TABLOCK an, ohne dass dadurch die anderen Massenimportstreams blockiert werden. Auf diese Weise werden mögliche Konflikte zwischen Clients beim Tabellenzugriff vermieden. Massenaktualisierungssperren sind jedoch nur für Tabellen (sowohl leere als auch nicht leere) ohne Indizes verfügbar. Wenn Sie TABLOCK für eine Tabelle mit Indizes angeben, ist kein paralleler Massenimport möglich. Weitere Informationen finden Sie unter Steuern des Sperrverhaltens für den Massenimport.

Wenn die Tabelle über Indizes verfügt, können Sie die Vorteile der Massenaktualisierungssperre nutzen, indem Sie vor dem Massenimport der Daten alle Indizes verwerfen. Anschließend können Sie die Daten mithilfe von TABLOCK parallel massenimportieren und dann den Index oder die Indizes neu erstellen. Beachten Sie, dass Massenimporte bei nicht leeren Tabellen mit Indizes vollständig protokolliert werden. Das gilt auch für das Modell der massenprotokollierten Wiederherstellung. Die Entscheidung, ob die Indizes entfernt werden sollen, ist davon abhängig, ob der durch den Massenimport in eine Tabelle ohne Indizes erzielte Vorteil größer ist, als beim Verwerfen und erneuten Erstellen der betreffenden Indizes.

Hinweis

Wenn Sie sekundäre Indizes verwerfen, sollten Sie diese parallel neu erstellen, indem Sie jeden sekundären Index aus einem anderen Client erstellen.

Wenn Sie die Indizes nicht verwerfen und neu erstellen möchten, sollten Sie die Daten parallel ohne Angabe des TABLOCK-Hinweises importieren. In diesem Fall können sich die einzelnen Massenimportstreams jedoch gegenseitig blockieren. Darüber hinaus sind auch die mit der Massenprotokollierung verbundenen Optimierungen nicht mehr verfügbar. Zum Minimieren der Protokollierung können Sie eine kleinere Batchgröße angeben und mithilfe des ORDER-Hinweises die beim Massenimport ausgeführte Sortierung verhindern.
Die Daten müssen in mehrere Eingabedateien, und zwar eine Datei pro Client, aufgeteilt werden. Um die effizienteste Verwendung der CPU sicherzustellen, sollten die Datendateien in etwa dieselbe Größe besitzen.

Hinweis
Wenn Sie sekundäre Indizes verwerfen, sollten Sie diese parallel neu erstellen, indem Sie jeden sekundären Index aus einem anderen Client erstellen.

Weitere Informationen finden Sie unter Paralleles Importieren von Daten mit Sperren auf Tabellenebene.

Protokollierung und Sperrung von Tabellen während des Massenimports

In der folgenden Tabelle wird zusammengefasst, wie die Sperrtypen vom Tabellenschema während eines Massenimportvorgangs bestimmt werden. Darüber hinaus gibt die folgende Tabelle auch an, ob die Tabelle leer ist, ob TABLOCK für den Vorgang festgelegt wurde und welcher Sperrtyp aktiviert wird, wenn die Datenbank das massenprotokollierte Wiederherstellungsmodell verwendet.

Hinweis
Nach dem ersten erfolgreichen Batch ist die Tabelle nicht länger leer.

Zieltabelle für den Massenimport	Leere Tabelle?	TABLOCK festgelegt?	Sperren	Protokollierung unter massenprotokolliertem und einfachem Wiederherstellungsmodell
Heap	Ja	Ja	BU-Tab	Massenprotokollierung
Heap	Ja	Nein	IX-Tab	Vollständige Protokollierung
Heap	Nein	Ja	BU-Tab	Massenprotokollierung
Heap	Nein	Nein	IX-Tab	Vollständige Protokollierung
Heap mit einem nicht gruppierten Index	Ja	Ja	SCH-M	Massenprotokollierung
Heap mit einem nicht gruppierten Index	Ja	Nein	IX-Tab	Vollständige Protokollierung
Heap mit einem nicht gruppierten Index	Nein	Ja	SCH-M	Daten - Massenprotokollierung Index - vollständige Protokollierung
Heap mit einem nicht gruppierten Index	Nein	Nein	IX-Tab	Vollständige Protokollierung
Gruppierter Index	Ja	Ja	SCH-M	Massenprotokollierung
Gruppierter Index	Ja	Nein	IX-Tab	Vollständige Protokollierung
Gruppierter Index	Nein	Ja	X-TAB	Vollständige Protokollierung
Gruppierter Index	Nein	Nein	IX-Tab	Vollständige Protokollierung

Kopieren von Daten zwischen SQL Server-Instanzen

Wenn Sie Daten von einer SQL Server-Instanz in eine andere Instanz massenkopieren möchten, exportieren Sie die Tabellendaten mithilfe von bcp in eine Datendatei. Mithilfe eines der Massenimportverfahren können Sie die Daten anschließend aus der Datei in eine Tabelle importieren. Führen Sie beide Massenvorgänge, Export und Import, entweder mithilfe des systemeigenen Formats oder des Unicode-Eigenformats aus.

Hinweis
Weitere Informationen zu diesen Formaten finden Sie unter Verwenden des systemeigenen Formats zum Importieren oder Exportieren von Daten und Verwenden des systemeigenen Unicode-Formats zum Importieren oder Exportieren von Daten.

Wenn die Quelltabelle einen gruppierten Index besitzt oder wenn Sie die Daten in eine Tabelle mit einem gruppierten Index massenimportieren möchten, führen Sie folgende Schritte aus:

Massenexportieren Sie die Daten aus der Quelltabelle, indem Sie mithilfe von bcp eine SELECT-Anweisung mit der Option query sowie die passende ORDER BY-Klausel verwenden, um eine sortierte Datendatei zu erstellen. Weitere Informationen finden Sie unter bcp (Hilfsprogramm).
Beim Massenimportieren der Daten in SQL Server. Verwenden Sie den ORDER-Qualifizierer, der nur von bcp und BULK INSERT unterstützt wird. Weitere Informationen finden Sie unter Steuern der Sortierreihenfolge beim Massenimport von Daten.

Weitere Informationen finden Sie unter Kopieren von Daten zwischen Servern.