Mainzer Datenfabrik ist Microsoft Gold Partner

Interesse geweckt?

Lassen Sie uns reden!

blog.Mainzer Datenfabrik

AG replica falling behind - Ursachen und Folgen

cover image of blog article '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 inkompatible 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
IS und wartende Sch-M Sperren
IS und wartende Sch-M Sperren
Melden Sie sich zu unserem monatlichen Newsletter an.

Seitennavigation

Zur Artikel Übersicht

Auf dieser Seite

SQL Server 2014 Migration SupportNEU
Im Sommer 2024 endet der Extended Support des Microsoft SQL Server 2014 SP3. Erfahren sie wie wir Sie bei Ihrer Migration unterstützen können! mehr erfahren