none
Select Count(*) und Delete RRS feed

  • Frage

  • Servus Experten

    also es handelt sich um einen 64 Bit SQL Server mit 4 CPUs + 4 . Blade Center IBM

    1) Wie befinden uns direkt auf dem Server und arbeiten im Managment Studio. Die folgende Abfrage gibt mir ein Timeout zurück

    Select count(*) from TableA      (Die Tabelle umfast ca. 5 bis 10 Millionen Datensätze)

    Wieso?

    2) Die Struktur der Tabellen ist mit entsprechenden Beziehung versehen (Member/Owner), wenn nun z.B. der Auftrag gelöscht wird geht das über 7 weitere Tabellen. Nun, klingt gut ist es aber nicht . Es werden ca. 700 -3500 Datensätze gelöscht und das kann nun bin zu 3-5 sec dauern. Bei 3000 Aufträge kann man das vergessen, da der Zugriff für User in der Zeit so gut wie unmöglich ist.

    Mein Lösungsansatz ist nun folgender gewesen. Es werden alle Aufträge ermittelt, die gelöscht werden sollen, und in eine Tabelle geschrieben. Über einen Cursor gehe ich nun über jeden einzelnen Auftrag und lösche von unten (member) nach oben (owner) was bedeuten schneller geht. Jeder Auftrag-Entfernungsvorgang ist (über alle Tabellen) ist in eine Transaction eingebettet. Das funzt alles soweit prima, nur ... das geht so schnell dass kein User mehr irgendwie an die Daten kommt, da der Server zu macht.

    Nun, würde ich gerne sowas wie eine PAUSE einbauen, damit zwischen den Löschvorgänge etwas "Luft" entsteht.

    In diesem Zusammenhang ist mir noch etwas aufgefallen. Nach ca 150-250 gelöschten Aufträgen entspricht ca 500000 Datensätze, fahren alle CPUs plötzlich gegen 95% Last. Beim Löschen selbst liegen wir zwischen 5-15 % Last. Aus das zuschalten 4 weiterer CPUs brachte keine Verbesserung der Server blieb für ca 45 min unter Volllast und war nicht mehr ansprechbar.

    micki sagt danke ...

     

    Montag, 5. Juli 2010 15:31

Antworten

  • Hallo,

    zu 1.) auch bei einem trivialen SELECT COUNT(*) wird effektiv gezählt (und nicht etwa geschätzt ;-)
    Wozu i. a. der Index mit dem schnellsten Zugriffsweg verwendet wird.
    Aber sobald eine Blockierung auftritt, nützt das alles nichts mehr.

    Brauchst Du nur eine Schätzung kannst Du entweder den NOLOCK Hinweis verwenden
    oder auf die alte sysindexes Sicht zurückgreifen. Wobei beide bei den Aktivitäten in 2.)
    durchaus fehlerhafte Werte liefern könn(t)en.

    zu 2.)

    Grundsätzlich kann bei großen Tabellen ein Cursor an der richtigen Stelle etwas bringen.
    Und eine Pause kannst Du mit WAITFOR (Transact-SQL) einbauen - was tunlichst ausserhalb
    jeglicher Transaktion stattfinden sollte.

    Was das Löschen von "unten nach oben" angeht:
    Achte darauf, dass Du dadurch keine Deadlocks provozierst, wenn die Restlichen Anwendungen
    von oben nach unten arbeiten (wie man das üblicherweise tut). Zumindest sollte eine Sperre
    auf dem Auftragskopf liegen, so dass Risiko minimiert wird.

    Das Löschen von Massendaten solltest Du aber eher in eine Zeit geringerer Aktivität verlagern.
    Denn nach größeren Operationen nimmt zum einen die Fragmentierung bei den Indizes zu,
    und eine Reorganisation ist empfehlenswert, siehe Neuorganisieren und Neuerstellen von Indizes
    Auch kann dadurch das Protokoll kräftig anschwellen und je nachdem wie gut die Indizierung ist,
    auch tempdb heftig strapaziert werden.

    Was die zusätzliches CPUs angeht: Im allgemeinen sind an der Stelle weniger
    viele CPUs als eine ausreichende I/O-Leistung entscheidend.
    Du solltest überprüfen, wo die wirkliche Schwachstelle liegt, siehe Leistung (Datenbankmodul)
    Wobei ein kürzerer Zeitraum zwischen solchen Löschaktionen, die benötigten Ressourcen
    reduzieren kann.

    Weitere Maßnahme abseits des Löschens wäre eine Partitionierung , so dass die Aktiven
    und zu löschenden Daten nicht (so häufig) kollidieren.

    Gruß Elmar

     

    Montag, 5. Juli 2010 16:24

Alle Antworten

  • Hallo,

    zu 1.) auch bei einem trivialen SELECT COUNT(*) wird effektiv gezählt (und nicht etwa geschätzt ;-)
    Wozu i. a. der Index mit dem schnellsten Zugriffsweg verwendet wird.
    Aber sobald eine Blockierung auftritt, nützt das alles nichts mehr.

    Brauchst Du nur eine Schätzung kannst Du entweder den NOLOCK Hinweis verwenden
    oder auf die alte sysindexes Sicht zurückgreifen. Wobei beide bei den Aktivitäten in 2.)
    durchaus fehlerhafte Werte liefern könn(t)en.

    zu 2.)

    Grundsätzlich kann bei großen Tabellen ein Cursor an der richtigen Stelle etwas bringen.
    Und eine Pause kannst Du mit WAITFOR (Transact-SQL) einbauen - was tunlichst ausserhalb
    jeglicher Transaktion stattfinden sollte.

    Was das Löschen von "unten nach oben" angeht:
    Achte darauf, dass Du dadurch keine Deadlocks provozierst, wenn die Restlichen Anwendungen
    von oben nach unten arbeiten (wie man das üblicherweise tut). Zumindest sollte eine Sperre
    auf dem Auftragskopf liegen, so dass Risiko minimiert wird.

    Das Löschen von Massendaten solltest Du aber eher in eine Zeit geringerer Aktivität verlagern.
    Denn nach größeren Operationen nimmt zum einen die Fragmentierung bei den Indizes zu,
    und eine Reorganisation ist empfehlenswert, siehe Neuorganisieren und Neuerstellen von Indizes
    Auch kann dadurch das Protokoll kräftig anschwellen und je nachdem wie gut die Indizierung ist,
    auch tempdb heftig strapaziert werden.

    Was die zusätzliches CPUs angeht: Im allgemeinen sind an der Stelle weniger
    viele CPUs als eine ausreichende I/O-Leistung entscheidend.
    Du solltest überprüfen, wo die wirkliche Schwachstelle liegt, siehe Leistung (Datenbankmodul)
    Wobei ein kürzerer Zeitraum zwischen solchen Löschaktionen, die benötigten Ressourcen
    reduzieren kann.

    Weitere Maßnahme abseits des Löschens wäre eine Partitionierung , so dass die Aktiven
    und zu löschenden Daten nicht (so häufig) kollidieren.

    Gruß Elmar

     

    Montag, 5. Juli 2010 16:24
  • Danke Elmar,

    das Waitfor funzt, dass muss jetzt mal paar Wochen laufen ;-)

    Die Indexe sind soweit OK.

    Was mir noch aufgefallen ist, auf einer Testumgebung, auf die, die gleichen Prozesse angewendet werden, wie Sicherung, Reorganisation etc, wurde die Datenbank nach Löschung von ca. 60% des Datenbestandes, das Volumen  auf der Platte nicht kleiner. Auch die Funktion DB verkleiner brachte nicht den gewünschten Erfolg.

    nochmals danke für deine Antwort und gute Zeit ... micki

    Montag, 5. Juli 2010 22:12
  • Was mir noch aufgefallen ist, auf einer Testumgebung, auf die, die gleichen Prozesse angewendet werden, wie Sicherung, Reorganisation etc, wurde die Datenbank nach Löschung von ca. 60% des Datenbestandes, das Volumen  auf der Platte nicht kleiner. Auch die Funktion DB verkleiner brachte nicht den gewünschten Erfolg.


    Hallo Micki,

    auch nicht mit der Option "Seiten vor dem Verschieben neu organisieren"?
    Rechne aber mit langen Laufzeiten, je nach Größe der DB.

    Manchmal macht das Verkleinern nicht unbedingt Sinn.

    Dienstag, 6. Juli 2010 13:01
  • Hallo Micki,
    eine Alternative um Sperren zu verhindern, wäre auch noch die Zeilenversionsverwaltung.
    http://www.insidesql.org/beitraege/administration/zeilenversionsverwaltung-row-level-versioning

    Das unregelmässige Verhalten des Servers finde ich allerdings durchaus beachtenswert. Elmar hat ja schon Links dazu gepostet.

    Einen schönen Tag noch,
    Christoph


    Microsoft SQL Server MVP
    http://www.insidesql.org

    Dienstag, 6. Juli 2010 13:59
    Beantworter