SQL – Fragen und AntwortenZeilenüberlauf, differenzielle Sicherungen und mehr

Paul S. Randal

F: Ich habe kürzlich eine Anwendung aktualisiert, die unter SQL Server 2005 ausgeführt werden soll. Eines der Dinge, die ich dabei genutzt habe, ist die Möglichkeit, dass die Zeilen größer als 8.060 Byte sein können, d. h., ich kann Benutzern ermöglichen, längere Datenfelder zu erstellen, ohne von SQL Server eine Fehlermeldung zu erhalten. Nachdem diese Anwendung jetzt serienmäßig hergestellt wird, treten Leistungsprobleme bei einigen Scanabfragen auf, die vor der Schemaänderung problemlos ausgeführt wurden. Ich habe die Fragmentierung der verschiedenen Indizes geprüft, und alles ist in Ordnung. Weshalb ist die Ausführung der Abfragen unter SQL Server 2005 so langsam?

A: Das von Ihnen verwendete Feature, der Zeilenüberlauf, eignet sich hervorragend, um die eine oder andere Zeile länger als 8.060 Byte ausfallen zu lassen, aber es ist nicht dafür vorgesehen, dass die Mehrzahl der Zeilen zu groß ausfällt, und kann wie in Ihrem Fall zu einer Verminderung der Abfrageleistung führen.

Der Grund dafür ist Folgendes: Wenn eine Zeile kurz davor ist, zu groß zu werden, wird in der Zeile eine der Spalten mit variabler Länge „aus der Zeile abgestoßen“. Dies bedeutet, dass die Spalte aus der Zeile auf der Daten- oder Indexseite genommen und auf eine Textseite verschoben wird. Statt des alten Spaltenwerts wird ein Zeiger eingefügt, der auf den neuen Standort des Spaltenwerts in der Datendatei verweist.

Das ist genau die Methode, die für das Speichern regulärer LOB-Spalten (Large Object), wie z. B. XML, text, image oder varchar(max), verwendet wird. Beachten Sie, dass es bei einem Tabellenschema, das mehrere Spalten mit variabler Länge enthält, keine Garantie gibt, dass die gleiche Spalte aus der Zeile abgestoßen wird, wenn mehrere Zeilen zu groß werden.

Diese Methode kann ein Leistungsproblem verursachen. Plötzlich kann es sein, dass die Abfrage einer Spalte mit variabler Länge aus einer einzigen Zeile in einer Tabelle einen zusätzlichen E/A-Vorgang benötigt (um von der Textseite zu lesen, die den neuen Standort des Werts enthält), falls die Spalte aus der Zeile abgestoßen wurde. Wenn mehrere Zeilen zu groß sind, könnte eine Abfrage, mit der die gleiche Spalte variabler Länge von mehreren Zeilen abgerufen werden soll, eine unvorhersehbare Leistung aufweisen, je nachdem, wie viele Werte aus der Zeile abgestoßen wurden.

In Ihrem Fall zeigt eine Abfrage, die einen Bereichs- oder Tabellenscan für eine Auswahlliste durchführt, die eine Spalte variabler Länge enthält, eine schlechte Leistung aufgrund eines Zeilenüberlaufs und seiner Auswirkungen. Dabei ist es egal, ob die Indizes vollständig oder fragmentiert sind, denn wenn eine Spalte variabler Länge aus der Zeile abgestoßen wurde, wird der zuvor effiziente Scan im Grunde unterbrochen, da ein zufälliger E/A-Vorgang notwendig ist, um die Textseite zu lesen, die den abgestoßenen Wert enthält.

Der Zeilenüberlauf ist nach wie vor sehr nützlich für die eine oder andere zu große Zeile. Wenn es jedoch auf die Abfrageleistung ankommt, sollte dieses Feature keine stark genutzte Komponente in Ihrem Entwurf darstellen.

F: Wir haben kürzlich eine Datenbankspiegelung zwischen zwei Failoverclustern eingeführt. Sie soll Georedundanz ermöglichen, die kostengünstiger ist als die SAN-Replikation (Storage Area Network). Da die Datencenter sich in derselben Stadt befinden, können wir synchrones Spiegeln verwenden. Das Problem ist Folgendes: Wenn auf dem lokalen Cluster ein Failover stattfindet, führt die gespiegelte Datenbank ein Failover zum Remotecluster durch, was nicht das von uns gewünschte Verhalten darstellt. Wie können wir dies verhindern? Wir wollen, dass das Failover nur dann stattfindet, wenn der lokale Cluster nicht verfügbar ist.

A: Um die Verfügbarkeit zu verbessern, wird das Spiegeln mit einem Zeugenserver eingerichtet, damit Failover automatisch stattfinden, wenn der Prinzipal nicht verfügbar ist. Der Gedanke dahinter: Falls der gesamte lokale Cluster ausfällt, führt die Datenbankspiegelung ein Failover zum zweiten Cluster durch, und die Anwendung kann fortfahren.

Das Problem tritt auf, wenn ein Clusterfailover stattfindet. Das Failover dauert länger als die Standardtimeouteinstellung für die Datenbankspiegelung. Der Zeugenserver und der Spiegelserver (d. h. die aktive SQL Server-Instanz auf dem zweiten Cluster) stimmen dahingehend überein, dass sie den Prinzipal nicht erkennen können, und daraufhin initiiert der Spiegelserver ein Spiegelungsfailover zum zweiten Cluster.

Die einfachste Methode, mit der dies verhindert werden kann, ist das Entfernen des Zeugenservers, damit die Datenbankspiegelung nicht automatisch ein Failover auslöst, wenn der lokale Cluster ausfällt. Selbstverständlich wird dadurch die Verfügbarkeit verringert, da dann ein Mensch ein Failover initiieren muss.

Die zweite Option besteht darin, die Standardtimeouteinstellung der Datenbankspiegelung zu verändern. Dies ist die Anzahl der einmal pro Sekunde stattfindenden Pingvorgänge, wobei der Prinzipal als nicht verfügbar deklariert wird, wenn er auf diese Anzahl von Pingvorgängen nicht reagiert hat. Diese Einstellung wird als das Partnertimeout bezeichnet und hat einen Standardwert von 10. Der aktuelle Timeoutwert für die Datenbank kann mit dem folgenden Code ermittelt werden:

SELECT [mirroring_connection_timeout]
  FROM master.sys.database_mirroring 
  WHERE [database_id] = DB_ID ('mydbname');
GO

Der Timeoutwert kann mit dem folgenden Code geändert werden:

ALTER DATABASE mydbname 
  SET PARTNER TIMEOUT <timeoutvalue>;
GO

Für dieses Szenario muss das Partnertimeout so festgelegt werden, dass es höher ist als die in der Regel für das Stattfinden eines Clusterfailovers auf dem lokalen Cluster erforderliche Zeit. Aufgrund der Variabilität in der Zeit, die das Ausführen einer Wiederherstellung auf der gespiegelten Datenbank erfordert, wenn das Clusterfailover stattfindet, kann es etwas schwierig sein, dies zu ermitteln, aber Sie sollten die obere Grenze bestimmen können. Das Problem bei dieser Methode ist, dass der Timeoutwert möglicherweise in Minuten angegeben werden muss, was in einem realen Notfall eventuell nicht in Frage kommt.

F: Meine Sicherungsstrategie umfasst vollständige Sicherungen und Protokollsicherungen, aber ich habe gehört, dass ich differenzielle Sicherungen hinzufügen sollte, um die Wiederherstellungszeit zu verringern. Ich führe einmal die Woche eine vollständige Sicherung und außerdem stündliche Protokollsicherungen durch. Ich habe versucht, tägliche differenzielle Sicherungen hinzuzufügen, aber etwas Seltsames, das ich dabei bemerkt habe, ist, dass die differenziellen Sicherungen am Ende der Woche nahezu die gleiche Größe haben wie die wöchentliche vollständige Sicherung. Ich war der Meinung, sie seien inkrementell, so wie Protokollsicherungen. Übersehe ich hier etwas?

A: Das Missverständnis hier dreht sich um die Natur differenzieller Sicherungen. Im Unterschied zu Protokollsicherungen sind differenzielle Sicherungen nicht inkrementell. Eine differenzielle Sicherung enthält die gesamten geänderten Daten seit der letzten vollständigen Sicherung (und dies trifft auf die Datenbank, die Dateigruppe und Sicherungen auf Dateiebene zu).

Wenn ein Block (eine logische Gruppe von acht Seiten zusammenhängender Datendateien) in irgendeiner Weise geändert wird, wird er auf einer bestimmten Bitmusterseite gekennzeichnet. Diese Seite wird differenzielle Zuordnung genannt (oder auch). Es gibt eine differenzielle Zuordnung für jeden 4 GB-Abschnitt einer Datendatei. Wenn eine differenzielle Sicherung durchgeführt wird, scannt das Sicherungssubsystem alle differenziellen Zuordnungen und kopiert alle geänderten Blöcke, aber die differenziellen Zuordnungen werden nicht zurückgesetzt. Das heißt, wenn zwischen aufeinanderfolgenden differenziellen Sicherungen weitere Blöcke geändert werden, fallen die späteren Sicherungen größer aus. Die differenziellen Zuordnungen werden nur zurückgesetzt, wenn eine vollständige Sicherung durchgeführt wird.

Wenn die Anwendungsarbeitsauslastung so aussieht, dass die Datenbankinhalte innerhalb eines kurzen Zeitraums (z. B. innerhalb einer Woche) umfassend geändert werden, hat eine wöchentliche vollständige Sicherung fast die gleiche Größe wie eine differenzielle Sicherung, die erst kurz vor der nächsten vollständigen Sicherung durchgeführt wurde. Dies erklärt das Verhalten, das Sie beobachten.

Es ist korrekt, dass differenzielle Sicherungen eine Möglichkeit bieten, die Wiederherstellungszeit im Fall einer Notfallwiederherstellung zu verringern. Wenn die Sicherungsstrategie aus wöchentlichen vollständigen Sicherungen und stündlichen Protokollsicherungen besteht, würde eine minutengenaue Wiederherstellung Folgendes erfordern:

• Sichern Sie das Protokollfragment (alle Protokolle, die seit der letzten Protokollsicherung generiert wurden).
• Stellen Sie die letzte vollständige Datenbanksicherung wieder her.
• Stellen Sie alle Protokollsicherungen seit der letzten vollständigen Datenbanksicherung eine nach der anderen wieder her.
• Stellen Sie das Protokollfragment wieder her.

Es kann sein, dass dabei viele Protokollsicherungen wiederhergestellt werden müssen, vor allem wenn sich der Notfall erst kurz vor der nächsten bevorstehenden vollständigen Sicherung ereignet. (Im schlimmsten Fall müssten 24 + 24 + 24 + 24 + 24 + 24 + 23 Protokollsicherungen wiederhergestellt werden.) Wenn dieser Strategie tägliche differenzielle Sicherungen hinzugefügt werden, ändert sich die Wiederherstellungsabfolge wie folgt:

• Sichern Sie das Protokollfragment (alle Protokolle, die seit der letzten Protokollsicherung generiert wurden).
• Stellen Sie die letzte vollständige Datenbanksicherung wieder her.
• Stellen Sie die letzte differenzielle Sicherung wieder her.
• Stellen Sie alle Protokollsicherungen seit der letzten differenziellen Sicherung eine nach der anderen wieder her.
• Stellen Sie das Protokollfragment wieder her.

Es kann sein, dass dadurch nicht mehr ganz so viele Protokollsicherungen wiederhergestellt werden müssen, da das Wiederherstellen einer differenziellen Sicherung im Grunde identisch ist mit dem Wiederherstellen aller Protokollsicherungen in dem Zeitraum, der von der differenziellen Sicherung abgedeckt wird.

Der schlimmste Fall in einem Szenario, in dem eine tägliche differenzielle Sicherung durchgeführt wird, wären 23 Protokollsicherungen, auch am letzten Tag der Woche. Der Nachteil dessen, dass differenzielle Sicherungen nicht inkrementell sind, ist der, dass sie mehr Speicherplatz erfordern, aber das ist fast immer ein lohnenswerter Kompromiss, um die Wiederherstellungszeit zu verringern.

F: Ich habe einen Failovercluster mit zwei Knoten. Jeder Knoten führt eine Einzelinstanz von SQL Server 2005 aus. Ich folge dem allgemeinen Rat, jede Instanz so festzulegen, dass sie nur 50 % des verfügbaren Speichers verwendet. Jetzt habe ich Probleme, weil die Arbeitsauslastung auf beiden Instanzen mehr Speicher erfordert, um das gleiche Leistungsniveau beizubehalten. Ich denke, wenn ich die Speichereinschränkung entferne oder erhöhe, werde ich auf Probleme stoßen, wenn eine der Instanzen ein Failover durchführt und beide auf nur einem Knoten ausgeführt werden. Was empfehlen Sie?

A: Ich werde diese Frage für den Fall mit zwei Knoten und zwei Instanzen beantworten, aber die folgenden Erläuterungen treffen auch auf ein Setup mit mehreren Instanzen zu (N-1 Failovercluster, bei denen N Knoten und N-1 SQL Server-Instanzen vorhanden sind).

Viele haben es mit einer hohen Arbeitsauslastung (wenn mehr als 50 % des Serverspeichers genutzt wird) auf beiden Instanzen zu tun und berücksichtigen dabei nicht die Wirkung auf die Arbeitsauslastungen, wenn beide Instanzen letzten Endes auf einem einzigen Knoten ausgeführt werden, nachdem ein Failover stattgefunden hat. Ohne eine besondere Konfiguration ist es möglich, dass eine unverhältnismäßige Speicherverteilung zwischen den Instanzen auftritt, sodass die eine Arbeitsauslastung ordnungsgemäß ausgeführt wird und die andere sich auf ein Schneckentempo verlangsamt.

Unter SQL Server 2000 empfiehlt es sich, jede Instanz auf höchstens 50 % des Clusterknotenspeichers zu begrenzen. Das kommt daher, dass der Speichermanager in SQL Server 2000 auf Speicherbelastungen nicht reagiert. Wenn SQL Server zum Beispiel 80 % des Knotenspeichers in Anspruch nimmt, gibt es ihn nicht wieder zurück. In einer Failoversituation heißt das, dass eine andere Instanz, die gerade startet, nur 20 % des Speichers zur Verfügung hätte. Durch das Begrenzen beider Instanzen auf höchstens 50 % des Knotenspeichers wird einer Failoverinstanz 50 % des Speichers garantiert. Das Problem dabei ist, dass die Arbeitsauslastung auf jeder Instanz ebenfalls auf 50 % des Speichers begrenzt wird.

Bei SQL Server 2005 (und SQL Server 2008) kann der Speichermanager auf die Speicherbelastung reagieren, sodass die Höchstgrenze von 50 % nicht mehr angemessen ist. Falls aber keine Einschränkung vorhanden ist und zwei Instanzen auf einem Clusterknoten ausgeführt werden, kann es sein, dass sie gegenseitig Druck ausüben, bis eine unverhältnismäßige Speicherverteilung entsteht.

Die Antwort lautet, dass jede Instanz so festgelegt werden muss, dass sie eine Mindestspeichermenge hat, damit sie nicht dazu gedrängt werden kann, zu viel Speicher abzugeben. Bei einer allgemeinen Einstellung für ein Setup mit zwei Knoten und zwei Instanzen wird jede Instanz auf mindestens 40 % des Speichers konfiguriert. Das heißt, wenn jede Instanz auf einem separaten Knoten ausgeführt wird, können die Instanzen beliebig viel Speicher nutzen. Wenn ein Failover stattfindet, steht jeder Instanz eine garantierte Menge an Speicherplatz zur Verfügung, um ein bestimmtes Leistungsniveau bei der Arbeitsauslastung beizubehalten, wobei etwas Speicherplatz zur gemeinsamen Nutzung übriggelassen wird. Auch wenn das bedeutet, dass die Leistung der beiden Arbeitsauslastungen in einer Failoversituation (erwartungsgemäß) abnehmen kann, werden sie in der Zeit, in der jede Instanz auf einem separaten Clusterknoten ausgeführt wird, meistens gar nicht begrenzt sein.

Paul S. Randal ist der leitende Direktor von SQLskills.com und ein SQL Server-MVP. Paul Randal war von 1999 bis 2007 im SQL Server-Speichermodulteam von Microsoft tätig. Er schrieb DBCC CHECKDB/repair für SQL Server 2005 und war bei der Entwicklung von SQL Server 2008 für das Kernspeichermodul zuständig. Als Experte für Notfallwiederherstellung, hohe Verfügbarkeit und Datenbankwartung ist Paul Randal ein regelmäßiger Referent bei Konferenzen weltweit. Sein Blog befindet sich unter SQLskills.com/blogs/paul.