none
doppelter Schlüssel ... nein, doch nicht?

    Frage

  • Einer meiner Kunden hat eine sehr große Datenbank und lädt täglich viele Daten aus einem Textfile dazu (per SQL-Merge). In den letzten Tagen mehren sich Fehlermeldungen der Art "Eine Zeile mit doppeltem Schlüssel kann in das Objekt (xxx) mit dem eindeutigen Index (yyy) nicht eingefügt werden". Wenn der Import dann nochmal durchgeführt wird, klappt es aber, obwohl sich an den Datenbankdaten und an den zu importierenden Daten nichts geändert hat !!

    Das kann ja eigentlich gar nicht sein.

    Die Datenbank müßte mal dringend reindiziert werden, das konnte bisher nicht gemacht werden, weil es zu lange dauert und dann morgens den laufenden Betrieb behindert.

    Kann so ein Fehler auftreten, wenn in sehr großen Tabellen der Index reindiziert werden müßte? eigentlich doch nicht - das könnte den Datenzugriff doch höchstens verlangsamen. Aber welche Erklärung kann es sonst geben?


    -Michael Peters
    • Bearbeitet mpeters.de Montag, 4. Dezember 2017 09:09
    Montag, 4. Dezember 2017 09:09

Alle Antworten

  • Hallo Michael,

    dein Posting hinterlässt leider einige Fragen, die eine Einschätzung schwierig machen.

    Was ist "sehr groß" oder "sehr viel"?

    Was genau meinst Du mit SQL-Merge?

    Welche Version und Edition haben wir hier?

    Wie ist die Tabellen- und Indexstruktur?

    Was läuft noch auf der Datenbank? Vielleicht irgendwelche Deletes?

    Kennst Du den Unterschied zwischen INDEX REORG und REBUILD?


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Montag, 4. Dezember 2017 11:12
  • danke Christoph,

    Zu "Merge":
    https://docs.microsoft.com/de-de/sql/t-sql/statements/merge-transact-sql

    Ist es für die Beantwortung meiner Frage wirklich wichtig, ob "sehr groß" 100 oder 1000 GB heißt? oder welche Version es genau ist? oder wie die Tabellenstruktur ist? Oder welche Daten drinstehen?

    Nochmal meine Frage, unabhängig von Größe, Version oder Tabellenstruktur:
    kann es theoretisch sein, daß es zu solchen Fehlern kommen kann, wenn der Index stark fragmentiert ist?

    -Michael

    Dienstag, 5. Dezember 2017 08:13
  • Die Indexfragmentierung hat ausschließlich Performancenachteile.
    Ein Index wird bei jedem Insert/Update/Delete entsprechend mitgepflegt.

    Ein Problem könnte eine Snapshot-Transaktion bei Unique-Keys (Primary) sein, wenn durch offene Transaktionen anderer Sitzungen ggf. Satzsperren gegen Veränderungen vorliegen und konkurierende Updates auftreten könnten.
    Gerade die Satzversionierung bietet da einiges an Fallstricken mit parallelen Updates.

    Die Frage ist also, wie du den unique Key erfindest.
    Hier bieten sich ausschließlich Identity-Spalten an, da diese unabhängig von Transaktionen weitergezählt werden.

    Wenn du z.B. per Max-Funktion die letzte Nummer ermittelst, könnte diese auf Grund von Satzversionen kleiner sein als real vorhanden, da deine Transaktion nicht auf die aktuellen sondern auf alte Werte zugreift.
    Dies liegt an der Satzversionierung, die durch Snapshot-Transaktionen oder auch Trigger-Updates entstehen und alte Transaktionen noch so rumhängen.

    Dienstag, 5. Dezember 2017 09:02
  • danke für die Hinweise.

    Die Primary Keys sind alle Int-Spalten mit Identity=Yes.

    Das Seltsame ist, daß es nur einen einzigen User (Admin) gibt, der überhaupt Inserts vornimmt - alle anderen User lesen nur. Konkurrierende Updates können also nicht vorkommen.

    Bei den fraglichen Inserts werden alle Datensätze (einige Tausend, sagen wir, bis zu ca. 20000) von einer Zwischentabelle, wo sie vorher auf Korrektheit überprüft worden sind, in die identisch aufgebaute endgültige Tabelle geschrieben, in der es schon einige Millionen Datensätze gibt. Weil es sein kann, daß manche der eindeutigen Schlüssel (aus mehreren Spalten zusammengesetzt) schon existieren, benutze ich den erwähnten Merge-Befehl - der fügt einfach alle noch nicht vorhandenen Datensätze ein.

    Das hat jahrelang problemlos funktioniert - in der letzten Zeit kommt es gelegentlich an dieser Stelle zu den erwähnten Fehlern.

    Gruss -Michael


    • Bearbeitet mpeters.de Mittwoch, 6. Dezember 2017 16:56
    Mittwoch, 6. Dezember 2017 16:55
  • Dann noch mal etwas genauer.

    Die Zieltabelle hat einen PK (PK1) mit Identity und einen eindeutigen Index (AK)?
    Die Zwischentabelle hat den gleichen eindeutigen Index aber als PK (PK2)?

    Beim MERGE joinst Du alle Spalten des PK2 mit dem AK?

    Daher meine Frage nach den Strukturen. 


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Donnerstag, 7. Dezember 2017 07:29
  • Zieltabelle hat einen PK mit Identity ("ID" as Int) und einen mehrere andere Indizes, einer davon eindeutig/nonclustered, zusammengesetzt aus einem datetime, einem nvarchar und einem varchar-Feld. (Es gibt noch mehrere andere Tabellen mit ähnlichen Strukturen.)

    Die Zwischentabelle hat, da sie nur temporär gefüllt wird, nur dieselbe ID-Spalte als PK, keine anderen Indizes. Bei der Merge-Anweisung wird die PK-Spalte natürlich weggelassen, alle anderen werden in die Zieltabelle geschrieben.

    Gruss -Michael


    • Bearbeitet mpeters.de Donnerstag, 7. Dezember 2017 11:00
    Donnerstag, 7. Dezember 2017 10:59
  • Wie wäre es erst einmal auf der Zwischentabelle den UNIQUE-Key über die Merge-Spalten anzulegen?

    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Donnerstag, 7. Dezember 2017 12:04
  • Wenn die anderen Zieltabellen ebenso noch Unique-Keys haben, musst du natürlich zusätzlich dafür sorgen, dass diese Regeln nicht verletzt werden können.
    D.h., in der Importtabelle diese Unique-Keys ebenso anlegen und beim Import zusätzlich prüfen, ob in der späteren Zieltabelle der Unique-Key nicht schon vorhanden ist.
    Wobei durchaus Verzögerungen bis zum Merge zu berücksichtigen sind.
    Will heißen, dass zwischen Prüfung und Merge genug Zeit vergehen kann, so dass der Unique-Key inzwischen erstellt wurde, der zum Prüfzeitpunkt noch nicht da war.

    Durch Transaktionsschutz wäre der Umweg über die temporäre Tabelle eigentlich gar nicht nötig, da andere deine neuen Daten ja noch gar nicht sehen können.
    Insgesamt würde dies sogar den Ablauf beschleunigen.

    • Bearbeitet bfuerchau Donnerstag, 7. Dezember 2017 12:15
    Donnerstag, 7. Dezember 2017 12:13
  • > Wie wäre es erst einmal auf der Zwischentabelle den UNIQUE-Key über die Merge-Spalten anzulegen?

    ja, könnte man alles machen, wär vielleicht sinnvoll, aber daß wirklich doppelte Werte importiert werden, ist eigentlich ausgeschlossen, da das wohl schon von der Software verhindert wird, die die Importdaten erstellt (in den letzten Jahre hat es da bei täglichen Imports ein einziges Mal einen Fehler gegeben).

    Aber das ist ja auch gar nicht das Problem. Das Problem ist, daß ein "doppelter Schlüssel"-Fehler angezeigt wurde, wo gar keiner war. Die Daten waren in Ordnung, und beim zweiten Versuch ging es. Das ist in den letzten Wochen zweimal passiert, in verschiedenen Tabellen (es gibt insgesamt 6). Ich verstehe nicht, wie das passieren konnte.

    Donnerstag, 7. Dezember 2017 13:48
  • danke bfuerchau,

    wie ich gerade schon Christoph geantwortet haben:  das Problem sind nicht doppelte Schlüssel - das Problem ist, daß eine Schlüsselverletzung angezeigt wurde, wo gar keine war. Es gab keine doppelten Schlüssel in den Importdaten, und beim zweiten Versuch lief der Import durch, ohne daß sich irgendwas in den Daten geändert hätte.

    -Michael

    Donnerstag, 7. Dezember 2017 13:52
  • Eine Glaskugel habe ich leider nicht und die Informationen über das System sind ja nach wie vor eher dürftig.

    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Donnerstag, 7. Dezember 2017 14:22
  • Es muss sich was geändert haben, da mit Sicherheit irgendwelche Transaktionen offen waren, die diese Regelverletzung auslösen.
    Passiert dies auch, wenn du ganz alleine mit der Datenbank arbeitest?
    Donnerstag, 7. Dezember 2017 14:23
  • Was für Transaktionen hätten das denn sein können? es ging ja um das Einfügen von neuen Datensätzen durch den Admin. Ein Schlüsselverletzungs-Fehler ist dabei doch eigentlich unmöglich.

    Im Einzelbetrieb ist es noch nicht passiert - es ist leider kein reproduzierbares Phänomen, fast immer klappt es ohne Fehler. Aber es macht einen natürlich mißtrauisch :)

    Freitag, 8. Dezember 2017 11:07
  • Bevor du weiter auf Spekulationen angewiesen bist, hilft dir nur, an Stelle eines Bulk-Inserts ein Skript zu erstellen, die Sätze einzeln zu laden und bei Duplicate Key, diesen Satz in einer separaten Tabelle abzulegen.
    Nun hast du 2 Möglichkeiten.
    Entweder du brichst den Vorgang mit Rollback ab und protokollierst den Satz dann in der benannten Tabelle oder du ignorierst den Satz und machst mit dem Rest weiter.
    Anschließend kannst du dich um die fehlerhaften Daten kümmern.

    Bei Bulk-Inserts ist das leider halt immer mühsam, Constraint-Errors zu lokalisieren wenn es keine Log's darüber gibt.

    Freitag, 8. Dezember 2017 12:24
  • Wie wäre es mal mit konkreten Zahlen?

    Wie stark ist denn der Index fragmentiert?

    SELECT s.object_id AS objectid,
    		SCHEMA_NAME(o.schema_id) as schemaname,
    		QUOTENAME(OBJECT_NAME(s.object_id, DB_ID())) as objectname,
    		s.index_id AS indexid,
    		QUOTENAME(i.name) as indexname,
    		partition_number AS partitionnum,
    		avg_fragmentation_in_percent AS frag,
    		i.type_desc 
    		FROM sys.dm_db_index_physical_stats (db_id(), null, null, null, null) s
    		inner join sys.objects o on s.[object_id] = o.[object_id]
    		INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] 
    			AND s.index_id = i.index_id 
    		where	s.index_id > 0
    		AND		i.name IS NOT NULL		-- I.e. Ignore HEAP indexes.
    		and		s.page_count > 100
    order by schemaname, objectname, indexname, partitionnum		;


    Einen schönen Tag noch, Christoph -- Data Platform MVP - http://www.insidesql.org/blogs/cmu

    Montag, 11. Dezember 2017 07:22
  • viele um die 99% - eine Reindex-Aktion ist vom Provider geplant, aber er muß erst die Datenbank kopieren und das dann woanders machen und hinterher wieder draufspielen, weil zu groß und weil das Reindizieren den normalen Betrieb stört. Vielleicht ist der Anteil auch durch mehrere vergangene Reindex-Aktionen so hoch geworden, weil es immer abgebrochen werden mußte - weil ja immer nur neue Daten draufkommen, aber normalerweise keine Deletes/Updates stattfinden, hätte ich eigentlich nicht eine so hohe Fragmentierung erwartet.

    Mal sehen, ob das Reindizieren klappt und wie es sich danach verhält.


    • Bearbeitet mpeters.de Dienstag, 12. Dezember 2017 09:23
    Dienstag, 12. Dezember 2017 09:16
  • Damit werden nur die Zugriffe über Index wieder etwas beschleunigt.
    Mit dem Problem des Unique-Keys hat das nichts zu tun, da ein neuer Schlüssel auf jeden Fall in den Index muss, damit er eben überhaupt als doppelt geprüft werden kann.
    Das Einzige, was nicht immer aktualisiert wird ist die sog. "Indexstatistik" über die Anzahl verschiedener Schlüssel um dem Optimizer den besten Zugriffsweg zu weisen. Dies kann man je nach nachlassender Performance durchaus zwischen durch mal machen. Dies ist aber kein Reindexing.
    Dienstag, 12. Dezember 2017 11:16
  • danke!

    vor 6 Stunden 7 Minuten