AG replica falling behind - Ursachen und Folgen

Überblick

Der Einsatz von AlwaysOn Verfügbarkeitsgruppen ist schon seit Langem Standard zur Erreichung von Hochverfügbarkeit in produktiven MSSQL Umgebungen. Hier werden mindestens zwei Instanzen so konfiguriert, dass beim Ausfall der Primärinstanz eine Standby-Instanz den Betrieb übernimmt. Diese Funktionalität wird dadurch erreicht, dass die Transaktionsinformationen (TLogs) permanent von der Primär- zur Standby-Seite übertragen und dort in einem fortlaufenden Recovery-Prozess angewandt werden, so dass Primär- und Standby-Seite bezüglich der Tabelleninhalte immer den - fast - gleichen Stand haben.
Da bei entsprechender Lizenzierung die Standby-Seite für Lesezugriffe verwendet werden kann, werden AlwaysOn Verfügbarkeitsgruppen außer zur Erreichung von Hochverfügbarkeit zusätzlich auch gerne für Funktionalitäten verwendet, die lediglich einen Lesezugriff erfordern, wie z.B. das Reporting. Hier kann der Primärknoten also beispielsweise dadurch entlastet werden, dass Reports auf dem Standbyknoten erstellt werden. Hierbei kann es in seltenen Fällen zu Latenzen kommen, die bewirken, dass der Report nicht die neuesten Daten enthält. Ein Beispiel dafür wäre, dass das die TLogs wegen der hohen Transaktionstätigkeit auf der Primärseite nicht in der erforderlichen Geschwindigkeit über das Netzwerk übertragen werden können.
Ein anderes Szenario ist, dass der Zielserver durch die Erstellung komplexer Reports so stark ausgelastet ist, dass die Verarbeitung der TLogs hinter der Transaktionsgeschwindigkeit auf der Primärseite hinterherhinkt oder einfach, dass die Reporterstellung Sperren benötigt, die inkompatibel zu der Verarbeitung weiterer TLog-Informationen sind.

In diesem Beitrag wird ein Szenario aufgebaut, das letzteren Fall wiedergibt und die Latenz bei der TLog-Verarbeitung zeigt.

Versuchsaufbau

Als gegeben wird vorausgesetzt, dass ein Cluster mit mindestens zwei Knoten bereitsteht. Der Cluster enthält mindestens eine Verfügbarkeitsgruppe, in der sich mindestens eine Datenbank befindet.

Zutaten

Ein Cluster “win19sql17cluster” mit zwei Knoten, “win19sql1701” und “win19sql1702”, jeweils Windows Server 2019
Auf jedem der beiden Knoten eine MSSQL Standardinstanz (= “MSSQLSERVER”), SQL Server 2022
Die aus dem Internet bekannte Testdatenbank StackOverflow2010, die sich in der AlwaysOn Verfügbarkeitsgruppe TLogXfer befindet

Vorbereitung

In einem ersten Schritt wird mit den folgenden Statements eine einspaltige Tabelle angelegt, die 2^15 (32768) Zeilen mit dem gleichen konstanten Wert (1) enthält.

--Aufruf in der Primary DB einer AOAG USE Stackoverflow2010 GO --Tabelle anlegen create table latenztest (konstante int) go -- Eine Zeile mit dem Wert 1 anlegen insert into latenztest select 1 GO -- 15 mal den Inhalt der Tabelle in diese einfügen insert into latenztest select * from latenztest go 15 select count(*) from latenztest GO

Prüfen von Zugriffssperren

Ein zentraler Punkt zum Aufbau einer erkennbaren Latenz ist eine Kombination aus einem DML- (Data Manipulation Language) und einem DDL- (Data Description Language) Statement und dadurch der Aufbau einer entsprechenden Sperre (Lock). Hierzu wird ein sehr lange anhaltendes select (zählt tatsächlich zur DML Gruppe, obwohl keine Daten geändert werden) eingesetzt. Durch Verwendung des select-Statements wird eine Lesesperre auf die betroffene(n) Tabelle(n) gelegt, die mit der für das DDL-Statement benötigten Sperre inkompatibel ist und deshalb zu einer Blockade dieses Statements führt, bis das select abgeschlossen ist.

Im folgenden Codeblock wird zunächst das DDL-Statement und sein Timing vorgestellt. Es besteht lediglich in einer Änderung des Datentyps der Spalte der Tabelle latenztest und wird hier auf der Primärinstanz ausgeführt, um zu zeigen, dass die zu beobachtende Sperre nicht durch Spezifika der AlwaysOn Verfügbarkeitsgruppe auftritt.

--Ändern des Datentyps der Spalte "konstante"
alter table latenztest alter column konstante bigint
go
-- und Änderung zurück in den Datentyp int
alter table latenztest alter column konstante int
go

-- Der gesamte Vorgang hat ca. 3 Sekunden benötigt

Nun kommt das DML-Statement: Es besteht aus einem zweifachen Cross Join über diese Tabelle, das heißt einer theoretischen Ergebnismenge von 32768^3 Zeilen. An dieser Stelle geht es lediglich darum, eine ausreichend lange Abfrage zu verwenden, damit man Zeit hat, zu sehen, was in der Datenbank passiert.

-- Bildung eines Cross-Join über alle Zeilen der latenztest-Tabelle
-- Die hohe Anzahl der Ergebnismenge bietet ausreichend Zeit, um sich die -- resultierenden Sperren anzusehen
select a.konstante from latenztest a
    cross join latenztest b
    cross join latenztest c

Zur Überprüfung der dabei auftretenden Sperren wird das folgende Statement verwendet, das entsprechend der u.a. Abbildung drei Intent Shared (IS) Sperren verwendet.

USE StackOverflow2010
GO

--Prüfen der aktuell gesetzten Sperren
select 	resource_type, request_mode, request_status
	from sys.dm_tran_locks 
	where resource_type = 'OBJECT'

Versucht man nun, während der Laufzeit des o.a. Cross-Joins den Datentyp der Spalte “konstante” zu ändern, so erhält man hierbei einen Wartezustand, der durch die dazu benötigte und mit der Lesesperre inkompatiblen Schreibsperre verursacht wird. Bei der benötigten Sperre handelt es sich um eine Schema-Modifikation (Sch-M) und der Request-Status ist WAIT.

alter table latenztest alter column konstante bigint

Die gleiche Situation wird nun im Cluster hergestellt:

Auf der Standby-Seite wird die Leseoperation mit dem doppelten Cross-Join ausgeführt
Während dieses Statement bearbeitet wird, wird der Datentyp auf der Primärseite geändert. Dies hat zur Folge, dass das entsprechende Statement über die Transaktionslogs zu der Standby-Seite gelangt und dort wegen der noch laufenden Abfrage blockiert wird.
Auf der Primärseite werden mehrere Updates durchgeführt, die zur Folge haben, dass wegen der Blockade auf der Standby-Seite die Warteschlange für neu auflaufende Transaktioslogs permanent anwächst.

Der folgende Codeblock zeigt das Statement, mit dem die Ausgaben in den darauf folgenden Abbildungen erzeugt wurden. Es wurde jeweils auf der Primärseite ausgeführt, während der Cross Join lief, nachdem die Datentyp-Änderung auf bigint abgesetzt wurde und nachdem jeweils die Spalte konstante der Tabelle latenztest auf 2, 3 bzw. 4 aktualisiert wurde. Man sieht das Anwachsen der Redo-Warteschlange auf 12444, 17668 bzw. 22944. Die Zeit des letzten Redo bleibt konstant bei 15:05:13 und die Differenz zwischen der letzten empfangenen und zur Anwendung gespeicherten Änderungsinformation (last_hardened_time) und last_redone_time wird immer größer.

SELECT ar.replica_server_name as InstanceName, hars.role_desc, drs.redo_queue_size, 
    CASE drs.is_local WHEN 1 THEN db_name(drs.database_id) 
        ELSE NULL END as DBName, drs.synchronization_state_desc as SyncState,
    drs.last_hardened_lsn, drs.end_of_log_lsn, drs.last_redone_lsn,
    drs.last_hardened_time, drs.last_redone_time, drs.log_send_queue_size
    FROM sys.dm_hadr_database_replica_states drs
    LEFT JOIN sys.availability_replicas ar 
        ON drs.replica_id = ar.replica_id
    LEFT JOIN sys.availability_groups ags 
        ON ar.group_id = ags.group_id
    LEFT JOIN sys.dm_hadr_availability_replica_states hars
        ON ar.group_id = hars.group_id and ar.replica_id = hars.replica_id
    WHERE ags.name = 'TLogXfer'
	ORDER BY ags.name, group_database_id, hars.role_desc, ar.replica_server_name

Die folgenden drei Abbildungen zeigen einige Werte zum Fortschritt bzw. Stillstand der TLog-Übertragung von der primären zur Standby-Seite nach Änderung der Werte der Spalte konstante von 1 auf 2, 2 auf 3 bzw. 3 auf 4.

Anwachsen der Redo-Warteschlange auf 12444 nach erstem Update aller Tabellenzeilen auf 2

Anwachsen der Redo-Warteschlange auf 12444 nach erstem Update aller Tabellenzeilen auf 3

Anwachsen der Redo-Warteschlange auf 12444 nach erstem Update aller Tabellenzeilen auf 4

Troubleshooting

Im hier vorgestellten Beispiel wurde das Problem durch die Kombination inkompatibler Sperren verursacht. Es gibt jedoch, abgesehen von einem zu langsamen Netzwerk, auch andere Ursachen, beispielsweise

Netzwerk-Latenzen durch niedrige Bandbreite oder große räumliche Entfernung zwischen Primär- und Standby-Server.
Hohe Auslastung durch sonstige Aufgaben, für die der Standby-Server verwendet wird.
Ausstattung des Standby-Servers mit weniger Ressourcen, insbesondere weniger CPUs zur Reduzierung von Lizenzkosten oder preiswerterem, langsamerem Storage.

Im ersten Fall kann schon ein einfacher ping-Test Aufschluss geben und ggf. erkennen lassen, dass die Bandbreite erhöht werden muss. In den anderen beiden Fällen kann eventuell der Resource-Governor (Resource Governor - SQL Server) eingesetzt werden, mit dem Aufgaben gedrosselt werden können, die eine hohe Last verursachen, aber eine niedrigere Priorität als eine zeitnahe Verarbeitung der TLogs haben. Hierbei müssen allerdings auch die Einschränkungen des Resource Governors berücksichtigt werden, die in einem entsprechenden Abschnitt des hier verlinkten Dokumentes behandelt werden.

Fazit

In diesem Beitrag wurde ein Beispiel zur Analyse verlangsamter TLog-Verarbeitung auf der Standby-Seite einer AlwaysOn Konfiguration vorgestellt. Dieser wurde durch zueinander inkompatible Sperrmechanismen im Zusammenhang mit Datenselektion und Datenbankobjektänderungen hervorgerufen. Der hier geschilderte Fall wird sicherlich eine Ausnahmesituation in Ihrem Unternehmen darstellen. Trotzdem kann das hier vorgestellte Verfahren zur Anzeige der Sperren und der Größe der Redo-Warteschlange verwendet werden und Aufschluss über eventuelle Probleme geben. Im Fall von Überlastungen des Standby-Servers kann der Resource Governor der Schlüssel zu eienr Lösung sein.

Wenn Sie mehr zu diesem Thema erfahren möchten, stehen Ihnen unsere Experten gerne zur Verfügung. Vereinbaren Sie unverbindlich ein Beratungsgespräch über unser Kontaktformular. Wir helfen Ihnen gerne weiter!