none
Delete from ... ist extrem Langsam RRS feed

  • Frage

  • Hallo, ich habe eine SQL server 2008 tabelle mit ca 12 mio Records. diese Tabelle enthält ein Id-Feld welches indiziert ist.
    jetzt sollen (ca 300000) datensätze einer bestimmten Id gelöscht werden. diese Lösch-Abfrage dauert weit über 5 min.

    CREATE TABLE eav_einnahmen2(
        id uniqueidentifier NOT NULL,
        jahr int NULL,
        monat int NULL,
        vu tinyint NULL,
        version tinyint NULL,
        tz_ein smallint NULL,
        gem_ein smallint NULL,
        hs_ein int NULL,
        tz_aus smallint NULL,
        gem_aus smallint NULL,
        hs_aus int NULL,
        weg smallint NULL,
        fs smallint NULL,
        ps smallint NULL,
        preis decimal(18, 2) NULL,
        anzahl smallint NULL,
        datum_kauf date NULL,
        uhrzeit_kauf time(7) NULL,
        datum_fahrt date NULL,
        vw smallint NULL,
        geraete_nr nvarchar(50) NULL,
        ticket_nr nvarchar(50) NULL,
        zahlung smallint NULL,
        kunden_id nvarchar(50) NULL,
        status int NULL,
        original varchar(255) NOT NULL,
        id_datei int NOT NULL,
        PRIMARY KEY CLUSTERED
        (
            id ASC
        )
    );

    CREATE NONCLUSTERED INDEX einnahmen_datei ON eav_einnahmen2
    (
        id_datei ASC
    );

    es existieren keine weiteren Foreign keys, keine trigger oder ähnliches, und trotzdem dauert das döschen so lange.

    wie kann ich dem Problem auf den Grund gehen, oder wo uss ich gucken, warum das so lange dauert ?

    ch

    Freitag, 1. Juni 2012 09:06

Antworten

  • Hi,

    den Wartetyp und die Warteressource sind erstmal am Interessantesten.

    Grüße

    Oliver

    • Als Antwort markiert chuede_nsh Freitag, 1. Juni 2012 11:12
    Freitag, 1. Juni 2012 10:51

Alle Antworten

  • Hi,

    das Problem beim "DELETE FROM" ist, das er jeden Datensatz einzeln löscht. Dies kann je nach Tabellengröße und Abhänigkeiten zwischen den Tabellen sehr lange dauern.

    Du kannst einmal im Aktivitäsmonitor prüfen, welchen Wartevorgang die Löschabfrage hat, damit kann erkannt werden, wo es im SQL Server hängt.

    Grüße

    Oliver

    Freitag, 1. Juni 2012 09:12
  • Hallo,

    wie bestimmst Du die 300TSD Datensätze? Entscheidend wäre hier das WHERE-Kriterium.
    Ist das "ungünstig", so kann es passieren dass die ganze Tabelle durchlaufen werden muss.
    Häufig empfiehlt es sich, zunächst eine SELECT Abfrage mit gleichem Kriterium zu testen.

    Hinzukommt, dass alle Zeilen zunächst protokolliert werden müssen, was einiges an Last auf das Protokoll bringt.
    Um die Menge zu reduzieren kann man ein Löschen über eine Schleife mittels DELETE TOP(n) ausführen.

    Und sollte die Tabelle anderweitig im Zugriff sein, können Verzögerungen durch Blockierungen hinzukommen.

    Gruß Elmar


    Freitag, 1. Juni 2012 09:25
    Beantworter
  • das delete sieht folgendermaßen aus :

    delete from eav_einnahmen where id_datei=xxx;

    nichts wirklich kompliziertes. ((317271 Zeile(n) betroffen)) dauer 6:32 min

    select COUNT(*) from eav_einnahmen where id_datei=xxx hat eine antwortszeit im ms bereich..

    select  * from eav_einnahmen where id_datei=xxx 43 sekunden (mit übertragung der 315xxx Datensätze), reine antwortzeit im MS bereich.

    die DB  hab ich exclusiv, selbst auf dem Server laufen keinen nennenswerten Prozessorlastigen  aufgaben.

    ein lsöchen über

    WHILE 1 = 1
    BEGIN
        DELETE TOP (1000) from eav_einnahmen where id_datei=xxx
        IF @@ROWCOUNT = 0
            BREAK
    END

    ist noch langsamer

    Freitag, 1. Juni 2012 09:46
  • Hi,

    auf was für einem Laufwerk liegt den das Transaktionsprotokol? Ist das Laufwerk ausreichend schnell?

    Und wie gesagt, was steht im Aktivitätsmontior wenn die Löschabfrage läuft.

    Grüße

    Oliver

    Freitag, 1. Juni 2012 09:57
  • schnell genug ist der server auf jeden fall.  ist nagelneu und "up to date".

    was genau brauchst du vom aktivitätsmonitor ? (ist ja recht viel infio da (aber leider nichts mit dem ich derzeit was anfangen kann))

    Freitag, 1. Juni 2012 10:19
  • Hi,

    den Wartetyp und die Warteressource sind erstmal am Interessantesten.

    Grüße

    Oliver

    • Als Antwort markiert chuede_nsh Freitag, 1. Juni 2012 11:12
    Freitag, 1. Juni 2012 10:51
  • Wartetyp: PAGEIOLATCH_SH

    warteresource : 10:1:408484

    • Bearbeitet chuede_nsh Freitag, 1. Juni 2012 11:17
    Freitag, 1. Juni 2012 11:13
  • Das ist wohl ein IO-Problem.
    Wie viel Speicher hat der Server und welche Version setzt Du genau ein?
    Wie sieht denn der Ausführungsplan aus? Bringt es etwas einen weiteren Index anzulegen?

    CREATE UNIQUE NONCLUSTERED INDEX einnahmen_datei1 ON eav_einnahmen2
    (
    id_datei ASC, id ASC
    );

     Versuch mal alternativ die IDs (in einer temporären Tabelle) zu sammeln und danach die Löschung über die IDs durchzuführen.

    Einen schönen Tag noch,
    Christoph
    --
    Microsoft SQL Server MVP
    www.insidesql.org/blogs/cmu

    Freitag, 1. Juni 2012 12:50
  • der server  hat 12g ram, und ist der SBS 2008

    der Index bringt nichts,

    das sammeln in einer anderen tabelle müsste ich mal ausprobieren, aber ich kann mir eigentlich nicht vorstellen, daß das was bringen kann.

    Freitag, 1. Juni 2012 13:09
  • Hallo,

    beim Löschen spielt der Speicher weniger eine Rolle.
    Wenn Du hohe Warteaktivitäten bei der I/O hast, so solltest Du dort ansetzen, wenn möglich.
    Zum einen sollten Datendatei und Protokoll auf unterschiedlichen Medien (Platten) liegen.
    Da der Server Deiner Aussage neu ist:
    Sind die Datenbankdateien schon mal reorganisiert worden - siehe ALTER INDEX REBUILD?

    Insbesondere Uniqueidentifier führen schnell zu ineffizienten Strukturen, die die IO in die Höhe treiben,
    so dass die Wahl eines anderen Clustered Index - vielleicht hier sogar id_datei - besser sein kann.

    Gruß Elmar

    Freitag, 1. Juni 2012 14:11
    Beantworter