Moving Average Tsql

Nehmen wir an, Sie haben eine Tabelle mit etwa 5 Millionen Datensätzen und eine nvarchar (max) - Spalte mit großen Textdaten belegt. Sie möchten diese Spalte auf NULL setzen, wenn SomeOtherColumn 1 auf die schnellste Weise. Die Brute-Force-UPDATE funktioniert nicht sehr gut hier, weil es eine große implizite Transaktion erstellen und ewig dauern wird. Updates in kleinen Chargen von 50K Datensätze zu einem Zeitpunkt funktioniert, aber seine noch unter 47 Stunden auf beefy 32 core64GB Server abzuschließen. Gibt es eine Möglichkeit, um dieses Update schneller Gibt es irgendwelche magischen Abfragehinweise Tabellenoptionen, die etwas anderes (wie Gleichzeitigkeit) im Austausch für Geschwindigkeit opfert HINWEIS: Das Erstellen von temp-Tabelle oder temp-Spalte ist keine Option, da diese nvarchar (max) - Spalte viel beinhaltet Von Daten und verbraucht so viel Platz PS: Ja, SomeOtherColumn ist bereits indiziert. Ich stimme zu, wir regelmäßig Updates wie diese auf Tabellen mit 50 Millionen oder sogar 500 Millionen Datensätze und es geschieht in Sekunden. Ich würde vermuten, dass die Abfrage Plan ausgewählt ist nicht sehr optimal und nehmen viel Zeit. Ich habe dieses geschehen mir, wenn es eine Fremdschlüsselbeschränkung auf einer anderen Tabelle auf einer unindizierten Spalte gibt. Nach dem Betrachten des Abfrageplans erkannten wir, dass es die andere Tabelle für jeden Löschvorgang scannen musste, der der Täter war. Das hatte 23 Millionen Zeilen, Indizierung der anderen Tabelle gebracht löschen, bis zu weniger als 5 Sekunden. Ndash Cobusve Von allem, was ich sehen kann, sieht es nicht aus, wie Ihre Probleme mit Indizes verknüpft sind. Der Schlüssel scheint in der Tatsache zu sein, dass Ihr nvarchar (max) Feld viele Daten enthält. Überlegen Sie, was SQL tun muss, um dieses Update durchzuführen. Da die Spalte, die Sie aktualisieren, wahrscheinlich mehr als 8000 Zeichen ist, wird sie off-page gespeichert, was einen zusätzlichen Aufwand beim Lesen dieser Spalte bedeutet, wenn sie nicht NULL ist. Wenn Sie einen Stapel von 50000 Updates ausführen, muss SQL dies in einer impliziten Transaktion platzieren, damit es möglich ist, im Falle von Problemen zurückzusetzen. Um sie zurückzusetzen, muss sie den ursprünglichen Wert der Spalte im Transaktionsprotokoll speichern. Angenommen, dass jede Spalte durchschnittlich 10.000 Byte Daten enthält, dh 50.000 Zeilen enthalten etwa 500 MB Daten, die vorübergehend (im einfachen Wiederherstellungsmodus) oder permanent (im Voll-Wiederherstellungsmodus) gespeichert werden müssen. Es gibt keine Möglichkeit, die Protokolle zu deaktivieren, da es die Datenbankintegrität beeinträchtigen wird. Ich lief einen schnellen Test auf meinem langsamen Hund des Hundes, und laufende Losungen von sogar 10.000 wird unzulässig langsam, aber holend die Größe unten zu 1000 Reihen, die eine temporäre Protokollgröße von ungefähr 10MB impliziert, arbeitete gerade nett. Ich lud eine Tabelle mit 350.000 Reihen und markierte 50.000 von ihnen für Update. Dies abgeschlossen in ca. 4 Minuten, und da es linear skaliert sollten Sie in der Lage, Ihre gesamte 5Million Zeilen auf meinem Hund langsam Desktop in ca. 6 Stunden auf meinem 1 Prozessor 2GB Desktop aktualisieren, so würde ich erwarten, etwas viel besser auf Ihrem beefy Server unterstützt Von SAN oder so. Sie können Ihre Update-Anweisung als select ausführen, indem Sie nur den Primärschlüssel und die große nvarchar-Spalte auswählen und dafür sorgen, dass diese so schnell läuft, wie Sie es erwarten. Natürlich kann der Flaschenhals andere Benutzer sperren Dinge oder Konkurrenz auf Ihrem Speicher oder Speicher auf dem Server, aber da Sie nicht erwähnen andere Benutzer Ich nehme an, Sie haben die DB im Single-User-Modus für diese. Als Optimierung sollten Sie sicherstellen, dass sich die Transaktionsprotokolle auf einer anderen physischen Festplattengruppe befinden als die Daten, um Suchzeiten zu minimieren. Das hat mir wirklich geholfen. Ich ging von 2 Stunden bis 20 Minuten mit diesem. Nach meiner Erfahrung, die Arbeit in MSSQL 2005, bewegen täglich (automatisch) 4 Million 46-Byte-Datensätze (keine nvarchar (max) obwohl) von einer Tabelle in einer Datenbank auf eine andere Tabelle in einer anderen Datenbank dauert etwa 20 Minuten in einem QuadCore 8GB , 2Ghz-Server und es tut nicht weh Application Performance. Durch Verschieben bedeute ich INSERT INTO SELECT und dann DELETE. Die CPU-Auslastung geht nie über 30, auch wenn die Tabelle gelöscht werden hat 28M-Datensätze und es macht immer um 4K Insert pro Minute, aber keine Updates. Nun, das ist mein Fall, es kann je nach Server-Last variieren. Gibt an, dass Anweisungen (Ihre Updates) Zeilen lesen können, die von anderen Transaktionen geändert wurden, aber noch nicht verpflichtet sind. In meinem Fall sind die Aufzeichnungen readonly. Ich weiß nicht, was rg-tsql bedeutet, aber hier youll finden Sie Informationen über Transaktionsisolation Ebenen in MSSQL. Seien Sie immer vorsichtig und stellen Sie sicher, dass Sie die Auswirkungen des Lesens von unkommentierten Transaktionen verstehen. Ja, Ihr Prozess muss nicht auf offene Transaktionen zu warten, bevor zu löschen, bevor Elemente, aber natürlich, wenn die Transaktion nicht verpflichtet, nachdem all dies bedeutet, dass Sie die Zeile falsch gelöscht zu warten. Ndash Cobusve Wenn Sie eine Produktionsumgebung mit nicht genügend Speicherplatz für alle Ihre Tabellen zu duplizieren sind, glaube ich, dass Sie auf der Suche nach Mühe früher oder später. Wenn Sie einige Informationen über die Anzahl der Zeilen mit SomeOtherColumn1, vielleicht können wir einen anderen Weg denken, aber ich schlage vor: 0) Sichern Sie Ihre Tabelle 1) Index der Flag-Spalte 2) Legen Sie die Tabelle-Option auf keine Log-Tranktionen. Wenn posible 3) schreiben Sie eine gespeicherte Prozedur, um die Updates laufen geantwortet Jun 2 10 at 3:17 BTW. Sind Sie gehen zu müssen, um diese Prozedur mehr als einmal in einem Leben ndash laufen lassen Dr. belisarius Jun 2 10 at 3:24 Wie setzen Sie Tabelle Option auf quotno log tranctionsquot ndash user356004 Jun 7 10 at 9:56 Ihre Antwort 2017 Stack Exchange , IncShowdown-bcp vs. DTS Welche SQL Server-Datenpumpe Daten schneller ein - und auslagert Jeder SQL Server-Anwendungsmanager muss mindestens eine große Menge an Daten in eine oder aus einer SQL Server-Datenbank verschieben, sodass SQL Server über mehrere Tools verfügt Bulk-Datenübertragungsoperationen. In diesem Artikel vergleiche ich zwei vertraute SQL Server-Dienstprogrammprogrammebulk copy Programm (bcp) und Data Transformation Services (DTS) in einem gemeinsamen Szenario: Verschieben von Daten zu und von einer ASCII-Textdatei. Ich vergleiche die Leistung und die Fähigkeiten dieser Dienstprogramme mit der T-SQL BULK INSERT-Anweisung, die laut Microsoft die schnellste Massenkopie-Methode ist (weitere Informationen finden Sie unter Data ImportExport Architecture in der SQL Server 2000-Onlinedokumentation). Bulk Copy Architecture SQL Server verfügt über mehrere zugrundeliegende APIs. Einige der ursprünglichen DB-Library-API, OLE DB-Provider für SQL Server und der ODBC-Treiber enthalten eine Reihe von Bulk-Copy-Funktionen. In SQL Server 6.5 und früher verwendet das bcp-Dienstprogramm das DB-Library-API. In SQL Server 7.0 führte Microsoft eine neue Version von bcp ein, die stattdessen die ODBC-Massen-Kopie-API verwendet, um neue Datentypen zu unterstützen, die von der DB-Library nicht unterstützt werden. (Microsoft hat die DB-Library auf dem aktuellen Stand der Funktionalität stabilisiert, sodass das Unternehmen keine neuen Features hinzufügen wird.) Letztendlich verarbeitet die SQL Server-Speicher-Engine Anrufe von jedem dieser APIs. Abbildung 1. Seite 38 zeigt eine Übersicht über die Bulk-Copy-Architektur von SQL Server 2000. Innerhalb der Speicher-Engine führen die T-SQL BULK INSERT-Anweisung, bcp und die Task DTS Bulk Insert denselben Code aus. Die T-SQL BULK INSERT-Anweisung kann möglicherweise schneller ausgeführt werden als die anderen, da BULK INSERT vollständig innerhalb des SQL Server-Prozesses ausgeführt wird. Bcp und DTS, Dienstprogrammprogramme, die in ihren eigenen Prozessen ausführen, müssen den Overhead der Interprozess-Kommunikation tragen, um Daten aus der Quelltextdatei an SQL Server weiterzugeben. Wenn Sie bcp oder DTS auf einem Clientcomputer anstatt auf dem SQL Server-System ausführen, fügt die Notwendigkeit, die Daten über eine Netzwerkverbindung zu kopieren, erheblichen Overhead hinzu und verlangsamt den Massen-Kopiervorgang. In meinen Testergebnissen wurde der potentielle Vorteil der T-SQL BULK INSERT-Anweisung nicht immer schneller übersetzt. Nach Microsoft Product Support Services (PSS), ist der Vorteil bei sehr großen Tabellen (Microsoft routinemäßig Tests mit 50 Millionen Zeilen Tabellen), wenn Sie mehrere Tabellen in eine Datenbank parallel laden, und wenn Sie Dateigruppen zu trennen Verschiedene Datenträgervolumes die Tabellen, die Sie planen, parallel zu laden. Andere Faktoren beeinflussen auch die Gesamtleistung eines Bulk-Copy-Vorgangs: Gleitkommafelder sind schneller als Zeichenfelder und Dateien, die erstellt werden, um Daten in SQL Servern nativen, internen, binären Datenformat-Import schneller als Dateien im externen ASCII-Zeichenformat zu speichern. Erstellen von Testdateien Ich habe die Datenbank erstellt und alle Objekte für meinen Test definiert, indem ich ein T-SQL-Skript in SQL Server Query Analyzer verwende. Dann habe ich diese leere Datenbank gesichert, um einen reproduzierbaren Ausgangspunkt für meine Tests zu schaffen. Mit einem Testdatengenerator für eine Tabelle mit 608-Byte-Zeilen (die aus einer Kombination aus Varchar - und Integer-Datentypen bestehen) generierte ich 5000 Zeilen-, 100.000 Zeilen - und 1 Million Zeilen-Tabellen. Ich habe den DTS ImportExport Wizard verwendet, um jede Tabelle in Textdateien im CSV-Format (Comma Separated Values) und im Festfeldformat zu exportieren. Diese Textdateien wurden zum Datenstandard für DTS-Tests. Da bcp nicht direkt Textdateien im selben CSV-Format erzeugt, die DTS erzeugen kann (dh bcp bietet keine einfache Möglichkeit, Textdatendateien mit durch Kommas getrennten Feldern und zitierten Textfeldern zu erstellen), habe ich einen zweiten Satz von Textdateien erstellt Bcp und BULK INSERT Prüfung. Ich benutzte DTS, um die CSV-Format-Datendateien und bcp zu laden, um sie in beide bcps-Format mit variabler Breite und ein Festfeldformat zu exportieren. Ich habe die Dateien mit variabler Breite im Format bcp und BULK INSERT getestet. Mein Testsystem bestand aus SQL Server 2000 Enterprise Edition unter Windows 2000 Advanced Server mit Service Pack 1 (SP1). Meine Testplattform war ein Compaq ProLiant 7000 mit zwei 500 MHz Intel Pentium II Prozessoren und 512 MB DRAM. Mein System umfasste einen Compaq Smart Array 3100ES Controller. Ich legte die SQL-Server-Daten auf einem 12-Laufwerk RAID 0-Array, und ich habe Log-Dateien auf einem separaten 3-Laufwerk RAID 0-Array. DTS und seine GUI machte Massen-Kopieren zwischen SQL Server und eine Textdatei einfach. Der SQL Server-DTS-Assistent verwendet die Transform Data-Task, um verschiebende Daten zu vereinfachen. Der DTS Package Designer, der DTS-Features bereitstellt, die der Assistent nicht enthält, ermöglicht es Ihnen, komplexe Prozeduren zu erstellen. Mit der DTS-Transformationsdaten-Task können Sie das Datenfeldformat während des Import - oder Exportvorgangs ändern. DTS stellt außerdem die Funktionalität der effizienteren T-SQL BULK INSERT-Anweisung in der Task DTS Bulk Insert bereit. Ich benutzte den DTS-Assistenten für die meisten meiner DTS-Tests und beschäftigte die Bulk Insert-Aufgabe nur zu überprüfen, ob seine Leistung der Verwendung der T-SQL BULK INSERT-Anweisung in Query Analyzer entspricht. Der DTS-Assistent macht das Exportieren einer Tabelle einfach, indem Sie einfach die Quelldatenbank und - tabelle auswählen, auf Textdateiausgabe klicken, einen Dateinamen angeben und festlegen, ob Sie eine Ausgabedatei mit festem oder Komma-Trennzeichen wünschen. Das Importieren der Daten aus der durch Komma getrennten Datei war genauso einfach wie das Exportieren, da das Textformat dem Tabellenformat entsprach. Wenn die erste Zeile in der Textdatei die Spaltennamen der Zieltabelle (eine Checkbox-Auswahl) enthält, wird die Spaltenreihenfolge in der Textdatei unwichtig. Unter diesen Bedingungen toleriert der Import fehlende Spalten, die zulassen, dass NULLs und Spalten mit einer Standardeinschränkung definiert werden. Das Importieren von Daten aus einer Fixed-Field-Formatdatei, die Sie mithilfe von DTS erstellen, um die Tabelle in einem festen Feldformat zu exportieren, war nur etwas komplexer. DTS erkennt Spaltenpositionen, die Daten enthalten, denen ein Leer vorangestellt ist, und legt standardmäßige Spaltenumbrüche in diesen Positionen fest. Meine Daten enthielten einige Spaltenpositionen, die den Beginn der neuen Datenspalten und damit standardmäßig mehr Datenspalten in der Eingabedatei als die Ausgabetabelle definiert hatten. Ich doppelklickte die fremden Spaltenumbrüche, um sie zu entfernen, und der Import ging ohne Fehler. Der DTS ImportExport Wizard aktiviert standardmäßig die Option Schnell laden. Diese Option, die nur verfügbar ist, wenn das System den SQL Server OLE DB-Provider (SQLOLEDB) verwendet, ruft diese Provider IRowsetFastLoad API auf, um einfachere Massenkopieoperationen effizienter zu handhaben. DTS hat viele Funktionen, die bcp nicht zur Verfügung stellt, einschließlich Einrichtungen für das Verschieben von Daten zwischen SQL Server-Datenbanken, zwischen SQL Server und Nicht-SQL Server-Datenbanken und zwischen zwei ODBC-Datenquellen. Zusätzlich enthält DTS Funktionen zum Modifizieren und Transformieren von Datenfeldern während des Massen-Kopiervorgangs. Sie können zwischen zwei Modi des bcp-Vorgangs wählen: interaktiv und nicht interaktiv. Im interaktiven Modus fordert Sie eine Reihe von Eingabeaufforderungen auf, das Textdateiformat für bcp zu beschreiben. Der bcp-Vorgang kann diese Informationen für eine spätere Verwendung in einer textbasierten Formatdatei speichern. Darüber hinaus unterstützt bcp mehrere Standard-Dateiformate: Standard-ASCII-Zeichen-Datendateien, Unicode-Zeichendatendateien, SQL Server-native Datenformate und eine Kombination der beiden letzten. Wenn Sie einen dieser Standarddatentypen verwenden, fordert bcp keine Informationen zur Verwendung eines Satzes von Standardwerten auf und erstellt optional eine entsprechende Formatdatei. Wenn Sie eine Formatdatei angeben, die das Layout der Textdatendateien beschreibt, können Sie bcp auch im nicht interaktiven Modus ausführen. Die Formatdatei beschreibt das Layout der zu importierenden oder zu exportierenden Datendatei und definiert die Korrespondenz zwischen Datendateien und SQL Server-Tabellenspalten. Die Format-Datei bietet Ihnen viel Kraft und Flexibilität in der Datendateien physikalischen Format und Sortierung. (Weitere Informationen zum Layout der Datendateien finden Sie in der Seitenleiste Dateiformatübersicht auf Seite 44.) Nachdem Sie eine Arbeitsdatei haben, ist bcp einfach zu bedienen. Ich habe eine Reihe von einfachen. BAT-Dateien, eine für jeden meiner Tests, weil bcp läuft als Kommandozeilen-Dienstprogramm. In der Befehlszeile habe ich die Datenbank - und Tabellennamen sowie IN oder OUT angegeben, um die Kopierrichtung zu bestimmen. Ich habe auch den Format-Dateinamen, den Datendateinamen und eine Benutzer-ID und ein Kennwort, um den Zugriff auf die SQL Server-Tabelle zu authentifizieren. Die bcp-Operation verfügt über Optionen, die ich nicht verwenden, einschließlich der Möglichkeit, SQL Server Bulk-Copy-Hinweise angeben. Verwendung von T-SQL BULK INSERT Die BULK INSERT-Anweisung macht die bcps-Funktionalität in einer T-SQL-Anweisung verfügbar. BULK INSERT verschiebt Daten aus einer Datei in eine SQL Server-Tabelle. BULK INSERT kann jedoch keine Daten aus einer SQL Server-Tabelle in eine Datei exportieren. Die T-SQL-Anweisung basiert auf einer bcp-style-Formatdatei, um die Datendateistruktur zu ermitteln. In meinem BULK INSERT testen, habe ich die gleichen Daten-und Format-Dateien, die ich verwendet, um bcp testen. Mit diesen Eingaben, fand ich, dass mit der BULK INSERT-Anweisung von Query Analyzer war nicht mehr schwierig als mit bcp. Dann habe ich ein T-SQL-Skript erstellt, um die BULK INSERT-Anweisung auszuführen und zu berechnen, wie lange es ausgeführt hat. T-SQL BULK INSERT unterstützt die meisten der gleichen Optionen und die Format-Datei, die bcp. BULK INSERT fehlt die Fähigkeit bcps, eine Fehlerdatei zu erstellen, die die Zeilen enthält, die nicht in die Tabelle eingefügt werden konnten. Die T-SQL-Anweisung ist nicht kompatibel mit Datendateien, die frühere Versionen von bcp erstellt diese Datendateien enthalten möglicherweise Feldformate, die SQL Server 2000 nicht unterstützt. BULK INSERT fehlt auch bcps Fähigkeit, regionale Formate verwenden, wenn Bulk-Kopieren Währung, Datum und Uhrzeit-Daten. Schließlich fehlt BULK INSERT die Fähigkeit bcps, die Netzwerkpaketgröße für Massenkopieoperationen auf entfernte SQL Server-Systeme festzulegen. Testergebnisse Weil die Zeitdauer, die ein bestimmter Import - oder Exportvorgang dauert, sehr stark von der Hardwarekonfiguration des SQL Servers abhängt, wurde Ive gewählt, um die Zeiten in den Testergebnisgraphen als Prozentsatz der längsten Import - oder Exportzeit darzustellen Die Ergebnisse beziehen sich auf die Zeitspanne, in der DTS 1 Million ANSI-gepufferte Datensätze importieren musste, und die Ergebnisse der Testergebnisse beziehen sich auf die Dauer, die DTS für die Ausfuhr von 1 Million ANSI-gepolsterten Datensätzen benötigte. Ich notierte die relative Zeit, die die verschiedenen Massenexportoperationen erforderten, um 5000, 100.000 und 1 Million Aufzeichnungen zu exportieren Diagramm 1. Seite 42, zeigt den Exportvergleich für 1 Million Aufzeichnungen. Ich habe auch die relative Zeit der verschiedenen Massen-Kopiervorgänge aufgezeichnet, die für den Import von 5000, 100.000 und 1 Million Datensätzen erforderlich sind. Grafik 2. Seite 42, zeigt den Importvergleich für 1 Million Datensätze. (Die dargestellten Graphen sind für alle Ergebnisse repräsentativ.) Weil BOL anzeigt, dass T-SQL BULK INSERT die schnellste der Bulk-Copy-Methoden ist, erwartete ich BULK INSERT, um das Rennen in meinen Tests zu gewinnen. Aber bcp und BULK INSERT beendeten in einer virtuellen toten Hitze und DTS war nicht weit hinter. Einige Ergebnistrends sind offensichtlich (z. B. je mehr Bytes SQL Server verarbeiten muss, desto länger dauert es). Was überrascht mich war, wie viel länger SQL-Server nahm, um die feste Länge Datendatei zu importieren, in der jedes Varcharfeld mit Leerzeichen auf die volle 50-Byte-Spaltenlänge aufgefüllt wird, die es benötigte, um die Datendatei mit variabler Länge zu importieren. Obwohl die Anzahl der Datensätze, Spalten und Schlüssel, die benötigt werden, um einzufügen, konstant bleibt, korreliert die Anzahl der verarbeiteten Bytes stark mit der Zeitdauer, die SQL Server benötigt, um die Aufgabe abzuschließen. Beispielsweise war die DTS-Fixed-Format-Datendatei mit der 1-Million-Zeilen-Datenbank das 2,7-fache der Größe der CSV-Datei und dauerte 2,5 Mal so lange, bis sie importiert wurde. Durch die Mittelung der Bcp - und T-SQL-BULK INSERT-Importzeiten zusammen, fand ich, dass die Datendatei mit festem Format das 3-fache der Größe der Datei mit variabler Länge war und das 2,7-fach so lange für den Import benötigte. Performance Monitoring Nach der Erfassung von Timing-Daten für die 1 Million Zeilen-Tests, lief ich den Import wieder mit dem Win2K Performance Monitor aktiv, Protokollierung CPU und Festplattenverwendung Statistiken. Abbildung 3 zeigt die CPU-Auslastung, die ich beobachtet habe, und Grafik 4 zeigt die IO-Statistik an. Das CPU-Auslastungsprofil war interessant. Für bcp und DTS trat die CPU-Auslastung während des Importes in drei verschiedenen Phasen von etwa gleicher Dauer auf. Während Phase 1, die grob mit der Zeit korreliert, in der der Bulk-Kopiervorgang die Eingabedatei gelesen und in tempdb geschrieben hat, beträgt die CPU-Auslastung 44 Prozent. Während der Phase 2, die die Haushaltungsaktivitäten abdeckt, sank die CPU-Auslastung auf nur 6 Prozent. Während Phase 3, die den abgeschlossenen Batch, der von tempdb an die Zieldatenbanktabelle gebunden ist, widerspiegelt, betrug die CPU-Auslastung 46 Prozent, wobei eine CPU 74 Prozent und die andere 18 Prozent betrug. Bei der Verwendung von T-SQL BULK INSERT tritt Phase 1 innerhalb des SQL Server-Prozesses auf, und nach meinen Beobachtungen dauert nur 82 Prozent der CPU-Zyklen, die bcp oder DTS benötigt. Beachten Sie den starken Einsatz von einer CPU in Phase 3. Ich vermute, dass der robuster Fall (d. H. Wenn Sie mehrere Tabellen gleichzeitig auf verschiedene Dateigruppen auf verschiedenen Plattengeräten laden) mehrere Prozessoren stärker nutzen würden. Beachten Sie außerdem die Verringerung der CPU 3-Auslastung während eines minimal protokollierten BULK INSERT-Betriebs von 44 Prozent auf 27 Prozent der CPU. Ich beobachtete auch die Festplattenauslastung, indem ich Festplatten-Lese - und Schreib-IOs pro Sekunde und die durchschnittliche Festplattenwarteschlangenlänge für das Systemvolume, ein RAID 0-Array mit drei Festplatten und die beiden RAID-Arrays, die die SQL Server-Transaktionsprotokolldatei und die Datenbanken verwalteten, betrachtete Datendatei. Die IOs-pro-Sekunde-Metrik zeigte eine ziemlich schwere IO-Aktivität gegenüber dem Systemvolumen (etwa 90 IOs pro Sekunde) während des gesamten Kopiervorgangs, mit Ausnahme einer kurzen Flaute bei etwa der Dreiviertelmarkierung. Dieses RAID 0-Array verwendete auch die Datenbank tempdb und die ASCII-Datendateien. Alle IO auf den Datenbankdaten und Transaktionsprotokollarrays traten in Phase 3 auf. Im Allgemeinen signalisiert eine durchschnittliche Platten-IO-Warteschlangenlänge von mehr als der doppelten Anzahl von Plattenlaufwerken in einem Array einen potentiellen Engpass (dh eine Ressource, die den Vorgang vom Abschließen begrenzt schneller). Das Systemvolumen (mit der tempdb-Datenbank und den eingegebenen ASCII-Dateien) hatte eine durchschnittliche Disk-Warteschlangenlänge von 1,4 und trotz des stetigen IO-Datenverkehrs hat meine Prüfung die Warteschlange nicht stark belastet. Meine Tests auch nicht die Festplatte mit der Datenbank-Transaktionsprotokoll-Datei die durchschnittliche Festplatte Warteschlange Länge nie getroffen 1. Meine Tests am stärksten verwendet das Volumen der Datenbanken Datendatei mit durchschnittlichen IO-Warteschlange Längen während DTS, bcp und BULK INSERT-Operationen von 39.6 , 67,7 und 31,9 Ereignisse, wie Grafik 4 zeigt. Diese IO-Warteschlangenlängen deuten auf einen Performance-Engpass in diesem Array hin, da es 12 Laufwerke hatte. Die meisten Computeroperationen haben einen Engpass im IO-Subsystem. Durch die Identifizierung der Quelle der Performance-Probleme, youll wissen, welche Ressourcen zu adressieren, wenn Sie die Geschwindigkeit beschleunigen müssen. Ein paar Überraschungen Ich war einige Male während der Tests überrascht (um Überraschungen zu vermeiden, siehe die Sidebar Some Gotchas to Remember, Seite 39). Zuerst dauerte das Importieren der Daten aus der Formatdatei mit dem festen Feld erheblich länger als das Importieren der gleichen Daten aus der CSV-Formatdatei, und nach dem Import belegte die Datenbank viel mehr Speicherplatz fast dreimal so viel Speicherplatz. Schlechte Leerzeichen in den Varchar-Feldern in der Festfeld-Formatdatei verursachten diesen unnötigen Raumverbrauch. Sie legen nachfolgende Leerzeichen fest, indem Sie ANSIPADDING auf ON setzen. ON ist nicht die Voreinstellung für SQL Server 2000. ANSIPADDING ist eine von sieben SET-Optionen, die SQL Server erfordert, wenn Sie mit indizierten Ansichten arbeiten, sodass Administratoren sie häufig einschalten. Der Wert von ANSIPADDING, der beim Festlegen des Felds wirksam ist, legt fest, ob hintere Leerzeichen aus VARAR-Feldern abgeschnitten werden sollen. Der Wert, der beim Hinzufügen oder Aktualisieren des Feldes wirkt, ist nicht relevant. Ich lief ein Skript in Query Analyzer, um die Datenbank zu definieren. Die Datenbanken Verbindungseigenschaften standardmäßig auf die Einstellung ANSIPADDING auf ON, so dass die Option aktiviert war, wenn ich lief das Datenbankdefinitionsskript. Wenn Sie mit indizierten Sichten arent arbeiten und Speicherplatz sparen und die Bearbeitungszeit verkürzen möchten, setzen Sie ANSIPADDING auf OFF, bevor Sie der Tabelle Felder hinzufügen oder hinzufügen. Protokollierung von Bulk-Kopiervorgängen Meine zweite Überraschung kam, als ich erkannte, wie kompliziert minimal loggende Massenkopieoperationen waren. Wenn Sie eine große Anzahl von Datensätzen in eine Datenbank importieren, kann die vollständige Aktualisierung der Protokollierung das Transaktionslog schnell füllen. Wenn Sie jedoch Massen-Kopiervorgänge für die minimale Protokollierung festlegen, protokolliert SQL Server nur Umfangszuweisungen (SQL Server 64 KB, 8-seitige Einheiten von Festplattenspeicher) und nicht die eingefügten Daten. Die Umfangszuweisungsinformation lässt SQL Server die Transaktion zurück, aber die Menge an Informationen ist nicht ausreichend, um die Transaktion während der Datenbankwiederherstellungsvorgänge zu rollen. SQL Server 7.0 lieferte die Option Datenbankoption auswählen, um minimal protokollierte T-SQL BULK INSERT-Vorgänge zu unterstützen. SQL Server 2000 verfügt über zwei Wiederherstellungsmodelle, einfache und Bulk-protokollierte, die zu minimal protokollierten BULK INSERT-Operationen führen können. Diese Einstellungen allein reichen jedoch aus, um SQL Server von der vollständigen Protokollierung von BULK INSERT-Operationen zu befreien. Um eine minimale Protokollierung zu erhalten, müssen Sie sicherstellen, dass nichts die Zieltabelle repliziert, die Zieltabelle keine Auslöser enthält, die Zieltabelle entweder leer oder keine Indexe definiert ist und Sie den TABLOCK-Hinweis verwenden, sodass die BULK INSERT - Ebene, anstatt Zeile-Ebene, Sperren. Bei großen Massenkopieroperationen müssen Sie auch die BATCHSIZE-Einstellung in Betracht ziehen. Standardmäßig handelt es sich bei der gesamten Eingabedatei um einen Stapel, der von SQL Server als eine Transaktion betrachtet wird. Bis Sie diese Transaktion beenden, gibt SQL Server den Transaktionsprotokoll-Speicherplatz frei, der die Rollback-Informationen enthält. (Weitere Informationen zu Wiederherstellungsmodellen finden Sie unter Kalen Delaney, Inside SQL Server, Datenbankwiederherstellungsmodelle, Juni 2000.) Verwenden des TABLOCK-Befehls "Bulk-Kopie" Der TABLOCK-Hinweis ist für SQL Server erforderlich, um T-SQL BULK INSERT-Vorgänge minimal zu protokollieren. Der TABLOCK-Hinweis ist auch eine der Bedingungen, die SQL Server erfordert, wenn Sie Daten in einer Tabelle von mehreren Clients gleichzeitig bei einem parallelen Datenladevorgang laden. Wenn ich die TABLOCK-Option getestet habe, habe ich festgestellt, dass SQL Server minimal die Transaktion logonly 13MB verwendet wurden, nachdem ich die 1 Million-Record-Tabelle mit der TABLOCK-Option, im Vergleich zu mehr als 370 MB nach dem Laden der Tabelle ohne die Option geladen. Allerdings, nachdem ich die Tabelle mit der Option TABLOCK geladen, belegte die Tabelle fast 2 GB Speicherplatz. Laden der Tabelle aus der gleichen Eingabedatei ohne die TABLOCK-Option verwendet nur 228 MB Festplattenspeicher. Der Befehl DBCC SHOWCONTIG, der angibt, ob Ihre Indizes fragmentiert sind, zeigte beide Versionen mit einer Scan-Dichte von 99 Prozent plus, was darauf hinweist, dass beide Tabellen sehr wenig externe Fragmentierung hatten. (Informationen zu mehreren Fragmentierungsarten finden Sie unter Kalen Delaney, Halten Sie SQL Server auf und Ausführen, Dezember 2000.) Der Unterschied zwischen den Tabellen lag in ihrer Seitendichte, dem durchschnittlichen Speicherplatz, der für jede 8KB-Seite verwendet wurde, die der Tabelle zugewiesen wurde. Die durchschnittliche Seitendichte der Tabelle mit TABLOCK war nur 11 Prozent die Tabelle ohne die Option TABLOCK hatte eine Seitendichte von 99 Prozent. Ich habe die Tabelle mit einer clustered Primärschlüssel-Einschränkung auf der IDENTITY-Spalte und einem FILLFACTOR von 10 Prozent erstellt. SQL Server verwendet den FILLFACTOR nur, wenn er einen Index erstellt SQL Server ignoriert den FILLFACTOR, wenn er den Index ändert. (Weitere Informationen zu FILLFACTOR finden Sie unter Kalen Delaney, Inside SQL Server, Fill-Factor Truth, Seite 29.) Wenn Sie den TABLOCK-Hinweis weglassen, scheint SQL Server daher die T-SQL BULK INSERT-Operation als Updates für die Datenbank. Wenn Sie den TABLOCK-Hinweis angeben, scheint SQL Server die BULK INSERT-Operation als eine erste Tabellenlast zu behandeln. Beachten Sie, dass die IO-Warteschlangenlänge bei minimal protokollierten T-SQL BULK INSERT-Vorgängen auf 3,7 Ereignisse fiel, die die Anzahl der sequentiellen Schreibvorgänge warten und impliziert keinen Disk-IO-Engpass. Zusätzliche Tests mit TABLOCK zeigten, dass SQL Server den FILLFACTOR beim Laden der Tabelle nur beim Laden einer neuen Tabelle verwendet hat. SQL Server ignoriert FILLFACTOR, wenn ich lief die BULK INSERT mit TABLOCK auf einer leeren Tabelle, die zuvor Datensätze enthalten hatte. Quellen an Microsoft erklärten dieses Verhalten wie folgt: Im Gegenzug für das Verbieten von Updates gleichzeitig mit dem BULK INSERT, TABLOCK ermöglicht einige Optimierungen. Ich glaube, Sie haben einen gruppierten Index. Wenn TABLOCK gesetzt ist, verwenden wir denselben Algorithmus intern, den wir verwenden, um den Index während der Indexerstellung zu füllen, weshalb er auf den Füllfaktor achtet. Dies führt zu schnelleren Ladezeiten, führt jedoch zu einigen Inkonsistenzen im Verhalten. Welche zu verwenden Für einfache Tabelle laden, wenn entweder bcp oder T-SQL BULK INSERT wird den Job zu tun, wählen Sie diejenige, die am besten passt die Art und Weise Sie arbeiten. Sie können nicht schlagen DTSs Fähigkeiten und Benutzerfreundlichkeit, aber Sie zahlen eine Leistungseinbuße, wenn Sie SQL Server ImportExport Wizard, der die DTS Transform Data-Tasks aufruft speziell für größere Tabellen (z. B. die Kosten etwa 15 Prozent auf meinem 1-Million-Zeile-Test). Um die Leistung von T-SQL BULK INSERT mit der Bequemlichkeit eines DTS-Pakets zu kombinieren, verwenden Sie DTSs Bulk Insert-Task. Wenn Sie sehr große Zahlen von Zeilen in eine Datenbank importieren, beeinflusst die Art und Weise, wie Sie die Operation entwerfen, nicht nur die Geschwindigkeit, sondern auch die Operationen entscheidenden Erfolg. Die Batchgröße legt fest, wie häufig SQL Server den Protokollbereich während der Massenkopieoperation befreit und hilft Ihnen sicherzustellen, dass Sie nicht aus dem Protokollbereich auslaufen. Das Erfüllen der Anforderungen für die minimale Protokollierung der Bulk-Copy-Operation hat eine enorme Auswirkung auf die Speicherplatzspeicheranforderungen und einige Auswirkungen auf die Leistung, die eine 3-prozentig schnellere Punktzahl in meinem Datensatztest mit einer Million Zeilen mit variabler Länge erzielt.