none
Performance SQL 2008: Langsame INSERTS RRS feed

  • Allgemeine Diskussion

  • Hallo zusammen,

    ich habe folgendes Problem hinsichtlich der INSERT Performance:

    Einmal im Monat werden ca. 1.000.000 Datensätze einer Tabelle hinzugefügt... Aktuell hat diese Tabelle 68.000.000 Rows. Die Abfrageperformance ist gut! Weniger als eine halbe Sekunde und der Datensatz ist da. Probleme machen die INSERTS. Die letzten Messungen haben ergeben, dass er eine Row in 100ms hinzufügt.

    Das wäre eine Laufzeit von 27 Stunden!! Unterirdisch schlecht wie ich finde. Aktuell vermute ich, dass es an den beiden Indizes liegt:

    1. Index ==> Eine Spalte enthalten (32 byte) non-clustered, non-unique

    2. Index ==> Eine Spalte enthalten (64 byte) clustered, unique

    Ich habe mal beide Indizes gelöscht. Danach hatte ich eine Performance von 4,5ms pro Datensatz. Also eine Verbesserung von rund Faktor 20... Nun ist logischerweise die Abfrageleistung katastrophal...

    Vielleicht hat ja sonst jemand noch eine Idee? 68 Mio Rows sind ja auch nicht so wahnsinnig viel, zumal die Tabelle völlig flach ist und keine Joins vorhanden ist.

    Viele Grüße

    Mittwoch, 1. August 2012 16:53

Alle Antworten

  • 2. Index ==> Eine Spalte enthalten (64 byte) clustered, unique

    Hallo,

    Wie sieht den der Clustered Index / die Daten dazu aus, sind die kontinuierlich aufsteigend, auch bei den neu eingefügten Daten? Sind sie es nicht, müssen regelmäßig die Pages umorganisiert werden, da dieses physikalische abhängend vom Clustered Index angeordnet werden.

    Eine Option wäre, vor den Einfügen der Daten den Index zu deaktivieren und nach dem Einfügen wieder zu aktivieren (=rebuild), nur mit der Zeit = steigenden Datenmenge wird das auch immer länger dauern.

    Wenn Du die Enterprise Edition hast, könntest Du für jeden Import eine eigene Partion anlegen.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Mittwoch, 1. August 2012 17:01
  • Hallo,

    Indizes mit 32 bzw. 64 Bytes sind schon ziemlich "fett".
    Ihr wäre es interessant zu wissen aus was für Spalten sie sich zusammensetzen.
    Und eine genauere Analyse gegen die Abfragen zu machen, ob das wirklich alle sein müssen.
    Wenn die Daten chronologisch (aufteilbar) sind, mögen auch Partitionen hilfreich sein.

    Was den Import selbst angeht:
    Wenn es nur einmal pro Monat passiert, so könnte man die Indizes temporär löschen.
    Und wenn nur neue Daten eingefügt werden, dies über Bulk Insert erledigen:
    Importieren von Massendaten mithilfe von BULK INSERT oder OPENROWSET(BULK...) (SQL Server)
    Danach könnte man die Indizes neu anlegen bzw. gleich eine Reorganisation durchführen.

    Könnten Duplikate vorhanden sein, sollte man die vorher (mit Index) entfernen.

    Gruß Elmar

    Mittwoch, 1. August 2012 17:08
  • Die Datensätze werden durch SAP erzeugt... Und auch die IDs die im UNIQUE Index stehen werden durch SAP vergeben. Deswegen ist auch etwas unklar ob die IDs wirklich nacheinander vergeben werden oder nicht... Aber ich vermute mal es ist "wild durcheinander" :-)

    Glaubt ihr denn, dass es auf jeden Fall am CLUSTERED Index liegt? Oder könnte es auch der NON-CLUSTERED sein?

    Mittwoch, 1. August 2012 17:56
  • Es wird, wie bereits beschrieben, eher am Clustered Index liegen. Wenn die Indexwerte nicht kontinuierlich aufsteigend sind, müssen eben häufig die Pages umorganisiert werden, zudem defragmentiert der Index stärker. Wenn sie kontinuierlich aufsteigend sind, müssen nur neue Pages hinzugefügt werden.

    Wenn Du Einfluß auf das Tabellendesign hast, könntest Du (für einen ersten Test) den Clustered in einen Non-Clustered Index ändern. Als Kandidat für einen CI käme eher eine Spalte mit IDENTITY in Frage, das ist per Design es schon vorgegeben, das sie aufsteigende Werte enthält.
    Aber als Außenstehender ist es schwer zu bewerten, wie das ganze Design sein sollte und was wie genutzt wird bzw. welche weiteren Designänderungen nötig wären.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Mittwoch, 1. August 2012 18:04
  • Hmmm, da musst du mir kurz auf die Sprünge helfen... IDENTITY?

    Meinst du den UNIQUEIDENTIFIER Datentyp der automatisch IDs vergibt?

    Mittwoch, 1. August 2012 18:25
  • Ich meinte die IDENTITY-Eigenschaft (Transact-SQL) für eine Spalte mit einem ganzzahligen Datentypen wie smallint, int und bigint, wo die Werte für neue Datensätze automatisch fortlaufend vergeben werden.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Mittwoch, 1. August 2012 18:35
  • Und noch ne Frage: Kann man das Organisieren der Indizes nicht abschalten? Also so, dass sie beim INSERT nicht neu organisiert werden oder ähnliches?

    Das könnte man dann ja vielleicht auf die Nacht auslagern... Als Wartungsplan etc...

    Mittwoch, 1. August 2012 18:38
  • Kann man das Organisieren der Indizes nicht abschalten?

    Eigentlich gar nicht, es sein denn, Du deaktivierst den Index oder änderst in in einen Non-Clustered Index um.

    Aber "theoretisch" kann man da nichts empfehlen, das hängt wirklich vom ganzen Konstrukt ab und muss natürlich getestet werden; Du willst es ja nicht verschlimm-bessern.

     

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Mittwoch, 1. August 2012 18:53
  • Hallo,

    Indizes werden automatisch gepflegt, ausgenommen Du deaktivierst sie, siehe Deaktivieren von Indizes

    Das kann aber letztendlich einem Neuerstellen gleichkommen..
    Deaktivierst Du eindeutige Indizes (wie Primärschlüssel), so kann es bei Reaktivieren zu Fehlern kommen,
    da die Eindeutigkeit nicht mehr überprüft werden kann.

    Wie Olaf bin ich der Meinung, dass man da nicht blind herumprobieren sollte.

    Wenn die Daten von extern kommen, so könntest Du bereits für eine passende Sortierung beim Einfügen sorgen,
    in dem Du den neuen Daten eine äquivalente Reihenfolge (clustered index) verpasst.
    Dann kann ein effizienterer Abfrageplan erzeugt werden.

    Gruß Elmar

    Mittwoch, 1. August 2012 19:16
  • Hmmm, okay. Ich habe, bei den aktuell 68 Mio Rows mal den UNIQUE Index gelöscht... Neu erstellen ging... Offensichtlich sorgt SAP dafür, dass sowieso keine doppelten IDs vergeben werden... Also ändere ich mal den CLUSTERED-UNIQUE Index zu NON-CLUSTERED UNIQUE...

    Könnte das Problem auch in der UNIQUE Eigenschaft liegen? Also dass der SQL Server erstmal checken muss, ob der neue Wert schon vorhanden ist und deshalb ein Performance Lag entsteht?

    Als nächstes habe ich eine neue Storage HDD angefordert, so dass ich den Index in andere Filegroups auslagern kann... Und auch auf andere physikalische HDDs.

    Was würdet ihr als RAID empfehlen? Im Einsatz ist RAID10 mit 4 HDDs...

    Mittwoch, 1. August 2012 19:25
  • Empfehlen würde ich ein ordentliches SAN oder SSD's im RAID, aber das ist vornehmlich eine Kosten-Nutzen-Frage; die Kosten sind hier höher als beim einem RAID mit "normalen" HDD's.
    Beim Einsatz vom SQL Server kann man ganze Aufsätze darüber schreiben, welches Storage für welche Datenbank (wie SysDB TempDB) und welche Datenbank-Dateiart (MDF/NDF und LDF) die beste ist; und auch hier ist die Kostenfrage ein nicht gerade geringer Faktor.
    Ja, Raid10 ist eine gute Kombination aus Sicherheit und Performanz.

    Ja, bei einen UNIQUE Index muss natürlich immer geprüft werden, ob es den Index Wert bereits gibt.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Mittwoch, 1. August 2012 19:40
  • Sagen wir mal so: Geld spielt keine Rolle :-)

    Das aktuelle Storage liegt preislich bei 25K mit 24 HDDs zu je 300GB per Fibre Channel angebunden.

    Datenbank liegt auf einem RAID10 mit 4HDDs

    Transaction Log auch so...

    Die Prüfung, ob der Index Wert gibt, sollte doch - gerade weil es ein Index ist - kein Performance Lag darstellen, oder?

    Gibt es vllt. eine "Faustformel" für die DB Storage Empfehlung?

    Mittwoch, 1. August 2012 19:53
  • Die Prüfung, ob der Index Wert gibt, sollte doch - gerade weil es ein Index ist - kein Performance Lag darstellen, oder?

    Es ist eine zusätzlich Abfrage: Gibt es den Wert oder nicht. Und bei jedem Insert/Delete muss der Index gepflegt werden, auch das kostet und 64 Bytes ist nicht gerade wenig.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Donnerstag, 2. August 2012 02:45
  • Okay, Ich habe das auch gerade mal gecheckt... Der Index ist ein nvarchar(32) was aus Design-Sicht so gar keinen Sinn ergibt. Aber ich fürchte da muss ich mit leben... Werde mal den Index als NON-CLUSTERED anlegen und testen.

    Werden denn nur bei einem CLUSTERED Index die Pages neu organisiert?

    Donnerstag, 2. August 2012 07:11
  • Wenn eine Tabelle keinen Clustered Index hat, dann nennt man sie auch Heap Tabelle; siehe MSDN Heapstrukturen

    Da werden dann die Pages eher unorganisiert angelegt. Im SQL Server 2000 (mit SP4) hatte so was mal sehr große "unused spaces" verursacht und die Datenbanken sind von der Größe her regelrecht "explodiert" (etwas übertrieben gesprochen). In SQL Azure kann man solche Heap Tables erst gar nicht anlegen, das ist dort eine der Restriktionen.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Donnerstag, 2. August 2012 07:24
  • Ja, die Heapstrukturen kenne ich...

    Aber sollte man nicht auch dafür sorgen, wenn man einen CLUSTERED Index verwendet, dass der Datentyp "sinnvoll" gewählt ist... Ein nvarchar(32) der auch noch wild durcheinander gefüllt wird ist doch eher suboptimal...

    Donnerstag, 2. August 2012 08:48
  • Natürlich sollte ein sinnvoller Kandidat für die Wahl des Clustered Index gefunden werden, was den Datentypen betrifft wie auch die Daten selbst (kontinuierlich steigend...). Ich hatte ja schon geschrieben, das Du ein Feld vom Typ int mit Identity Eigenschaft hinzufügen kannst, sowas ist immer sehr geeignet.

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Donnerstag, 2. August 2012 09:29
  • Am 02.08.2012 09:11, schrieb Dkem:

    Okay, Ich habe das auch gerade mal gecheckt... Der Index ist ein nvarchar(32) was aus Design-Sicht so gar keinen Sinn ergibt.

    Blöde Frage ... was steht denn in dem Schlüssel drin???? Wie sieht der denn aus, wenn er aus dem SAP kommt?

    Donnerstag, 2. August 2012 09:51
  • Hmmm, der sieht ungefähr so aus:

    0000AE347EGXG475947HJGDUR30012KS

    Donnerstag, 2. August 2012 17:21
  • hallo Dkem,

    in einem Post schreibst Du, dass Du die Daten aus SAP bekommst und in einer anderen DB speichern musst.

    kannst Du uns kurz beschreiben, wofuer Du die Daten in einer separaten DB speichern musst und nicht direkt auf die SAP SQL DB zugreifen kannst. Und wie bekommst Du die Daten und wer schreibt Dir die genaue Definition dieser Tabellen in dieser DB vor ?

    abhaengig von Deinen Antworten koennen wir Dir dann auch passende Tips geben.

    68Mio Rows ist noch nichts aussergewoehnliches und daher sollte die performance eigentlich bei korrektem tuning eigentlich kein Problem darstellen zumal Du ja schreibst, dass die Daten nur 1x pro Monat geladen werden und damit weitere Optimierungen moeglich sind.


    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Donnerstag, 2. August 2012 17:57
  • Am 02.08.2012 19:21, schrieb Dkem:

    Hmmm, der sieht ungefähr so aus:

    0000AE347EGXG475947HJGDUR30012KS

    Wäre es eine Option deine Spalte nicht als Unicode zu definieren, sondern als CHAR(32) ? Dann müssten aus den 64 Byte ja 32 Byte werden.

    Meine eigentliche Idee, war ja den Schlüssel in etwas einfacheres umzuwandeln, aber da seh ich grad keinen Weg. Auch mit HASHBYTE besteht halt immer die Gefahr eben doch eine Kollision zu bekommen.

    Ich weiß nicht inwieweit eine Kombination weiterhilft.
    SELECT
     CHECKSUM(N'0000AE347EGXG475947HJGDUR30012KS'),
     DATALENGTH(CHECKSUM(N'0000AE347EGXG475947HJGDUR30012KS')),
     HASHBYTES(N'SHA1', N'0000AE347EGXG475947HJGDUR30012KS'),
     DATALENGTH(HASHBYTES(N'SHA1', N'0000AE347EGXG475947HJGDUR30012KS'))
    bringt
    -881084317 4 0xB1D572D44B4C228F4496A1D01923CA62FB5B9740 20

    Wären dann 24 Byte ...
    Allerdings kann ich nicht sagen, ob eine Eindeutigkeit damit zu erreichen ist, bzw inwieweit die Nutzung von CHECKSUM UND HASHBYTE den Geschwindigkeitsvorteil wieder eindampft!

    Freitag, 3. August 2012 07:28
  • Leider nein, SAP bedient eine Anwendung, so dass auch Anwender die keinen Zugriff auf SAP haben Einsicht in die Rechnungen haben. Die SAPGUI ist ja nicht gerade preiswert :-)

    Die Tabelle schreibt der Hersteller der Drittanwendung vor... Zumindest wird diese Tabelle in der Form automatisch angelegt.

    Freitag, 3. August 2012 07:36
  • Sooo, ich habe jetzt nochmal intensiv getestet...

    Wenn ich BEIDE Indizes deaktiviere habe ich eine Transaktionsdauer von 40.000ms für 5000 Datensätze.

    Sobald ein Index aktiviert ist, habe ich zwischen 100.000 und 15.0000ms Transaktionsdauer für 5000 Datensätze.

    Für mich sieht das so aus, als besteht ein I/O Problem... Was meint ihr?

    Freitag, 3. August 2012 11:29
  • Es muss nicht zwingend ein I/O Problem sein.

    Wenn ein/mehrer Indizes aktiv sind, müssen die bei den Inserts hat aktualisiert werden und das bedeutet natürlich immer zusätzlichen Aufwand + I/O Last.

    Das kannst Du mit Windows PerfMon überwachen, wie schnell / wieviele IOs bei der Aktion verarbeitet werden oder ob das Storage komplett ausgelastet ist.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Freitag, 3. August 2012 11:48
  • Welche Leistungsindikatoren würdest du dafür denn empfehlen?
    Freitag, 3. August 2012 11:57
  • Dazu gibt es im Net diverse Empfehlungen, einfach mal nach suchen.

    Auch bei TechNet gibt es dazu Empfehlungen: SQL Server Best Practices Article


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Freitag, 3. August 2012 12:03
  • ... und hier gibts die Per Counter sogar als Poster: http://www.quest.com/backstage/images/promotions/SQLServer-Perfmonance-Poster.pdf

    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Freitag, 3. August 2012 12:08
  • Ich habe gerade mal den Counter "Disk % idle" des Datenbank-Storage überwacht. Wenn die INSERTS laufen, ist die HDD auf 3% Leerlauf. Wenn ich dann zu diesem Zeitpunkt versuche eine größere Datei (2GB ZIP-File) auf die HDD zu kopieren, habe ich eine Bandbreite von 8,8MB/s.

    Läuft der INSERT nicht, liegt die Bandbreite bei 300MB/sec... Also vielleicht doch die HDD?

    Freitag, 3. August 2012 14:44
  • Ich nehme an, dann ist "Durchschnittlich Warteschlangenlänge des Datenträgers" auf 97%, weil das System mit dem Schreiben nicht nachkommt.

    Liegen Datenbank- und Log-Dateien auf getrennten Storages? Bei größeren Schreiboperationen (Insert/Update) ist das von Vorteil.

    Hier findest Du im TechNet ScriptCenter ein PowerShell Script, mit dem Du die IO Performanz ermitteln und dann mit anderen Rechner/Disks vergleichen: Storage I/O Performance Tester


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    Freitag, 3. August 2012 15:17
  • Hallo und sorry wegen der späten Rückmeldung. Aktuell noch in anderen Projekten unterwegs...

    Ja, das Thema ist noch aktuell...

    Datenbank und Log sind getrennt und liegen je auf einem dedizierten RAID10 System.

    Mittwoch, 15. August 2012 13:05
  • Danke übrigens für das PowerShell Skript. Leider ist die SQLIO.EXE nicht mehr herunterladbar...
    Mittwoch, 15. August 2012 13:29