Skip to content

Tipps & Tricks: Große Datenmengen in einer Produktionsumgebung aktualisieren

5. September 2013

Gelegentlich lässt es sich nicht vermeiden, dass große Datenmengen in einer Produktionsumgebung aktualisiert werden müssen. Beispielsweise dann, wenn ein Feld einen neuen Standardwert bekommen soll (ohne ein Default-Constraint zu nutzen). Das ließe sich im Prinzip mit der folgenden SQL-Anweisung erledigen:

  UPDATE dbo.Protokoll
SET DeletedFlag = 0
WHERE DeletedFlag IS NULL

Problematisch ist dabei, dass aufgrund der Lock-Escalation dann die gesamte Tabelle solange exklusiv gesperrt wird, bis die Änderung komplett durchgelaufen ist. Gerade bei produktiven Umgebungen, kann dies dazu führen, dass andere Transaktionen in Timeouts laufen, was möglichst zu vermeiden ist. Natürlich kann man die Aktualisierung in die Nacht verlagern, aber auch da könnten eventuell irgendwelche Batches laufen, die versuchen, auf die genannte Tabelle zuzugreifen und dann nach einem Timeout abbrechen.

Wie also kann man den UPDATE trotzdem ausführen, und das Produktivsystem dabei möglichst wenig belasten?

Die Lösung liegt darin, dass UPDATE in mehreren kleinen Schritten auszuführen. Damit werden die Transaktionen kleiner, so dass auch nur kurzzeitig Sperren erzeugt und möglichst schnell wieder freigegeben werden. Mit der folgenden Anweisung werden beispielsweise nur die ersten 10.000 Zeilen aktualisiert, bei denen das DeletedFlag noch auf NULL steht:

  UPDATE TOP (10000) p
SET DeletedFlag = 0
FROM dbo.Protokoll p (NOLOCK)
WHERE DeletedFlag IS NULL

Natürlich kann der Hinweis „NOLOCK“ nicht dafür sorgen, dass auf der Tabelle gar keine Sperren stattfinde. Er sorgt aber dafür, dass die Zeilen bei der Ermittlung der zu ändernden Zeilen (also der Auswertung der WHERE-Klausel) noch nicht, sondern erst bei der eigentlischen Schreibaktion gesperrt werden. Wenn Sie die Anweisung erneut ausführen, werden die nächsten 10.000 Zeilen aktualisiert. Die Wahl der optimalen Schrittweite hängt von verschiedenen Faktoren ab und ist am besten herauszufinden, indem man verschiedene Werte ausprobiert und dabei im Aktivitätsmonitor die Suspended Processes beobachtet (unter „Prozesse“ den Taskstatus auf „SUSPENDED“ filtern). Es ist in der Regel unproblematisch, wenn hier ein paar auftreten, solange diese einerseits irgendwann wieder abgebaut werden können und andererseits die Menge nicht zu sehr anwächst, weil die blockierende Transaktion zu lange dauert. Zwischen den einzelnen Ausführungen der UPDATE-Anweisung sollte der Server die Gelegenheit bekommen, die bis dahin angestauten SUSPENDED-Prozesse abzubauen. Wenn man den UPDATE also in eine Schleife einbaut, sollte nach jeder Ausführung eine kleine Pause eingelegt werden. Davor wird über die Systemvariable @@ROWCOUNT ermittelt, wie viele Zeilen vom letzten UPDATE geändert wurden. Die Schleife wird nun solange ausgeführt, wie die Zahl größer 0 ist. Sobald der Wert 0 erreicht ist, sind alle Zeilen aktualisiert.

  DECLARE @Schrittweite INT  
  DECLARE @Zeilen INT  
  DECLARE @Pause DATETIME

  SET @Zeilen = 1 —  nur Startwert
  SET @Schrittweite = 5000
  SET @Pause = ’00:00:10′

  WHILE @Zeilen > 0

  BEGIN

    UPDATE TOP (@Schrittweite) p
    SET DeletedFlag = 0
    FROM dbo.Protokoll p (NOLOCK)
    WHERE DeletedFlag IS NULL

    SET @Zeilen=@@ROWCOUNT

    WAITFOR DELAY @Pause

  END

Wenn Sie nun noch ein Feedback über den Fortschritt der Ausführung haben möchten, ist eine Ausgabe sowohl per SELECT als auch per PRINT nicht sinnvoll, da beide nicht synchron ausgeführt werden. Sie würden die Meldung also erst verzögert oder vielleicht sogar erst dann sehen, wenn das Skript ohnehin fertig ist. Wenn man stattdessen die RAISERROR-Anweisung verwendet und damit eine reine Info-Meldung (Severity = 0) erzeugt, kann man das aber auch umgehen:

  DECLARE @Schrittweite INT
  DECLARE @Zeilen INT
  DECLARE @Pause DATETIME
  DECLARE @Meldung varchar(255)

  SET @Zeilen = 1 —  nur Startwert
  SET @Schrittweite = 5000
  SET @Pause = ’00:00:10′

  WHILE @Zeilen > 0

  BEGIN

    UPDATE TOP (@Schrittweite) p
    SET DeletedFlag = 0
    FROM dbo.Protokoll p (NOLOCK)
    WHERE DeletedFlag IS NULL

    SET @Zeilen=@@ROWCOUNT

    SET @Meldung=convert(varchar, getdate(), 114) + ‚ : ‚ + convert(varchar, @Zeilen) + ‚ Zeilen verarbeitet.‘
    RAISERROR (@Meldung, 0, 0) WITH NOWAIT

    WAITFOR DELAY @Pause

  END

Nun kann das Skript problemlos laufen. Es empfiehlt sich aber trotzdem hin und wieder im Activity Monitor einen Blick auf die Suspended Processes zu werfen. Hier kann man gut beobachten, wie sich diese mit der Zeit anstauen und während der WAITFOR DELAY-Anweisung wieder abbauen. Sollte es hier Probleme geben, können Sie das Skript jederzeit unterbrechen (es wird dann nur der letzte begonnene UPDATE zurückgerollt) und die Werte für Schrittweite (wenn zu viele Suspended Processes auftreten) und Pause (wenn die Zeit nicht reicht, um die Suspended Processes abzubauen) anpassen. Nach einem Neustart des Skripts geht es dann wieder an derselben Stelle weiter.

3 Kommentare
  1. Maksymov permalink

    Also Robert, deine Bemühung die Lock escalation zu umgehen erfolgt zwar in der richtigen Richtung, aber kurz verfehlt. Laut technet, „A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index“, d.h. Dein Skript macht nichts anderes als sperrt die Tabelle für Update-dauer, lässt sie für 5 sek frei und weiter von vorne….

    • Hi Andriy, das sehe ich etwas anders: Ja, ab 5000 Locks kann eine Lock Escalation erfolgen, die dafür sorgt, dass die ganze Tabelle gesperrt wird. Wenn man das verhindern will, kann man entweder die Lock Escalation per Hint unterbinden (was eher nicht zu empfehlen ist), oder aber eine geringere Schrittweite wählen. Auf jeden Fall sorgt das beschriebene Vorgehen dafür – und darauf kam es mir eigentlich an – dass die Tabelle nicht für die Laufzeit des kompletten Updates gesperrt wird, sondern für mehrere aber jeweils kurze Zeiträume, zwischen denen Transaktionen von anderen Usern auch Zugriff auf die Tabelle bekommen können. Sofern diese Zeiträume kurz genug sind, dass sie nicht zu einem Timeout einer anderen Transaktion führen, wäre im Extremfall also auch ein exklusiver Lock der gesamten Tabelle vertretbar.
      Trotzdem spricht natürlich auch einiges dafür, die Lock Escalation zu vermeiden, indem man entsprechend kleinere Schrittweiten von beispielsweise 2.000 Zeilen wählt, allerdings dauert dann die gesamte Update-Aktion auch einiges länger. Hier ist dann das entsprechende Fingerspitzengefühl des ausführenden Admins oder DB-Entwicklers gefragt. Aber wie beschrieben lässt sich das Skript auch problemlos mittendrin abbrechen, die Schrittweite anpassen und erneut starten, so dass man sich an die optimale Schrittweite langsam herantasten kann (im Zweifelsfall besser von unten – also mit kleinen Werten – als von oben).

Trackbacks & Pingbacks

  1. SQL Server: Interessanter Post über die Aktualisierung großer Datenmengen | WebJagger's Blog

Schreibe einen Kommentar

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

%d Bloggern gefällt das: