Im Juli 2015 war ich als Sprecher auf dem SQL Saturday in Manchester mit dem Thema “DML deep dive” vertreten. Unter anderem wurde im Vortrag gezeigt, wie Forwarded Records entstehen und welchen Einfluss sie auf Abfragen haben. Das Thema ist recht komplex und kompliziert. Daher soll dieser Artikel die Besonderheiten von Forwarded Records detailliert – und durch Beispiele untermauert – hervorheben.
Inhaltsverzeichnis
Interner Aufbau eines HEAPS
Forwarded Records können nur in Heaps auftreten. Als Heap werden Tabellen bezeichnet, die keiner logischen Ordnung nach einem Attribut der Tabelle unterworfen sind. Werden in Heaps Datensätze gespeichert, scannt Microsoft SQL Server die PFS (Page Free Space) und sucht nach Datenseiten, die von der Tabelle allokiert sind. Ist auf einer Datenseite ausreichend Platz für die Speicherung des Datensatzes vorhanden, wird der Datensatz auf der entsprechenden Datenseite gespeichert; sind keine freien Datenseiten mehr vorhanden, werden bis zu acht neue Datenseiten für die Tabelle reserviert und der Datensatz wird auf einer neuen, leeren Datenseite gespeichert.
PFS – Datenseite
PFS-Seiten (Page Free Space) zeichnen den Zuordnungsstatus sowie den belegten Speicherplatz der einzelnen Datenseiten auf. Die PFS-Seite verwaltet jede Datenseite einer Datenbank durch die Belegung von 1 Byte pro Datenseite. Somit können pro PFS-Seite 8.088 Datenseiten verwaltet werden! Ist eine Datenseite zugeordnet und es handelt sich um die Zuordnung zu einem HEAP, wird in der PFS hinterlegt, wie die Datenseite bereits prozentual gefüllt ist. Hierzu werden die ersten beiden Bits gesetzt:
| Bit-Wert | Bedeutung |
| 0x00 | Die Datenseite ist leer |
| 0x01 | Die Datenseite ist bis zu 50% gefüllt |
| 0x02 | Die Datenseite ist zwischen 51% und 85% gefüllt |
| 0x03 | Die Datenseite ist zwischen 86% und 95% gefüllt |
| 0x04 | Die Datenseite ist zwischen 96% und 100% gefüllt |
Die Höhe des freien Speicherplatzes einer Datenseite wird ausschließlich für Heap- und Text/Image-Seiten verwaltet. Indexe erfordern keine Verwaltung in der PFS, da die Stelle, an der eine neue Zeile eingefügt werden soll, von den Indexschlüsselwerten abhängig ist und nicht vom möglichen freien Platz auf einer Datenseite.
IAM – Datenseite
Als IAM Datenseite (Index Allocation Map) wird eine Systemdatenseite in Microsoft SQL Server bezeichnet, in der zugehörige Datenseiten EINER Tabelle oder eines Indexes verwaltet werden. Microsoft SQL Server verwendet die IAM Datenseiten für Bewegungen innerhalb eines Heaps. Die Zeilen innerhalb eines Heaps weisen keine bestimmte Reihenfolge auf und die Datenseiten sind nicht verknüpft. Die einzige logische Verbindung zwischen den Datenseiten sind die Informationen, die auf den IAM-Seiten aufgezeichnet sind!
SELECT P.index_id,
P.rows,
SIAU.type_desc,
SIAU.total_pages,
SIAU.used_pages,
SIAU.data_pages,
SIAU.first_page,
SIAU.first_iam_page
FROM sys.partitions AS P
INNER JOIN sys.system_internals_allocation_units AS SIAU
ON (P.hobt_id = SIAU.container_id)
WHERE P.object_id = OBJECT_ID('dbo.demo_table', 'U');
Die Spalte [first_iam_page] aus [sys].[system_internals_allocation_units] verweist auf die erste IAM-Datenseite in der Kette möglicher IAM-Datenseiten, die zur Verwaltung des Speicherplatzes verwendet werden, der dem Heap zugeordnet ist.
FORWARDED RECORDS?
Ein Forwarded Record ist ein Datensatz in einem HEAP, der – bedingt durch eine Aktualisierung – im Volumen so stark anwächst, dass er nicht mehr vollständig auf die ursprüngliche Datenseite passt. Microsoft SQL Server erstellt eine neue Datenseite und speichert den Datensatz auf der neu erstellten Datenseite. Auf der ursprünglichen Datenseite verbleibt ein Eintrag, der auf die neue Adresse/Datenseite verweist. Dieses Verfahren ist einem “Nachsendeantrag der Post” ähnlich. Obwohl Microsoft SQL Server den Datensatz auf einer neuen Datenseite speichert, bleibt die Originaladresse immer noch gültig und ein Update der Position in eventuell vorhandenen Non Clustered Indexes muss nicht ausgeführt werden.
Testumgebung
Für die Demonstration wird eine Tabelle angelegt, in der sich 20 Datensätze befinden. Von diesen 20 Datensätzen wird ein Datensatz durch Aktualisierungen so weit vergrößert, dass der Inhalt des Datensatzes nicht mehr auf eine Datenseite passt; die Daten müssen also auf eine neue Datenseite verschoben werden. Im Ergebnis erzielt man so einen Forwarded Record.
/* Create the demo table for 20 records */
CREATE TABLE dbo.demo_table
(
Id INT NOT NULL IDENTITY (1, 1),
C1 VARCHAR(4000) NOT NULL
);
GO
/* Now insert 20 records into the table */
INSERT INTO dbo.demo_table (C1) VALUES
(REPLICATE('A', 2000)),
(REPLICATE('B', 2000)),
(REPLICATE('C', 2000)),
(REPLICATE('D', 2000));
GO 5
/* On what pages are the records stored? */
SELECT FPLC.*,
DT.*
FROM dbo.demo_table AS DT
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC;
GO
Der Code erstellt eine neue Tabelle [dbo].[demo_table] und füllt sie mit 20 Datensätzen. Da jeder Datensatz eine Satzlänge von 2.015 Bytes besitzt, sind die Datenseiten mit 8.060 Bytes zu 100% gefüllt.
Bei 20 Datensätzen ergibt sich eine Gesamtzahl von 5 Datenseiten für die Speicherung der Daten und eine zusätzliche Datenseite für die IAM Datenseite.
Ein INDEX SCAN über die vollständige Tabelle generiert ein I/O von 5 Datenseiten!
-- INDEX SCAN über 5 Datenseiten SET STATISTICS IO ON; SELECT * FROM dbo.demo_table AS DT; SET STATISTICS IO OFF; GO
Die IAM-Datenseite wird nicht als I/O gewertet, da sie nichts zur Ausgabe beiträgt. Es werden ausschließlich Datenseiten berücksichtigt, die Daten für die Ausgabe bereithalten.
FORWARDED RECORDS generieren
Welcher Prozess erstellt einen Forwarded Record? Ein Forwarded Record kann nur generiert werden, wenn ein Datensatz geändert wird und der auf der Datenseite zur Verfügung stehende Platz nicht mehr ausreicht, den vollständigen Datensatz zu speichern. In diesem Fall muss der Datensatz die ursprüngliche Datenseite verlassen und auf eine neue Datenseite “umziehen”. Der nachfolgende Code erweitert den Wert in C1 von 2.000 Bytes auf 2.500 Bytes für den Datensatz mit der [Id] = 1. Anschließend wird die Transaktion aus dem Transaktionsprotokoll ausgelesen um die einzelnen Transaktionsschritte sichtbar zu machen.
UPDATE dbo.demo_table
SET C1 = REPLICATE('Z', 2500)
WHERE Id = 1;
GO
Nachdem der Inhalt von [C1] von 2.000 Bytes auf 2.500 Bytes angewachsen ist, reicht der vorhandene Platz auf der Datenseite 119 nicht mehr aus. Microsoft SQL Server muss den Datensatz auf eine andere Datenseite verschieben, die ausreichend Platz zur Verfügung stellt, um den Datensatz zu speichern. Was genau während dieser Transaktion passiert, wird mit einem Blick in das Transaktionsprotokoll sichtbar gemacht.
-- Check the transaction log for every single step
SELECT FD.[Current LSN],
FD.Operation,
FD.Context,
FD.AllocUnitName,
FD.[Page ID],
FD.[Slot ID]
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE FD.Context <> N'LCX_NULL'
ORDER BY
FD.[Current LSN];
GO
Der Inhalt des Transaktionsprotokolls zeigt, dass zunächst Metadaten in der Datenbank angepasst wurden (Zeile 1 – 7). Diese Informationen sind für die Betrachtung irrelevant, da sie lediglich das Erstellen einer Statistik für das Attribut [Id] der Tabelle [dbo].[demo_table] protokolliert haben. Der eigentliche Prozess beginnt in Zeile 8 des Auszugs. In der SGAM-Datenseite wird eine Aktualisierung durchgeführt, da eine neue Datenseite / Extent hinzugefügt wird.
Sobald die neue Datenseite im System bekannt ist, folgt der weitere Ablauf einem fest vordefinierten Muster:
- in der PFS wird die neue Seite als “leer” gekennzeichnet (Zeile 9)
- Anschließend wird die neue Datenseite in die Verwaltung der IAM-Datenseite der Tabelle [dbo].[demo_table] aufgenommen (Zeile 10)
- Die neue Datenseite (0x9C = 156) wird zunächst formatiert (Zeile 11)
- um anschließend den ALTEN Datensatz mit der [Id] = 1 auf die neue Datenseite zu schreiben (Zeile 12)
- und die PFS-Datenseite zu aktualisieren, da die Datenseite nun <= 50% gefüllt ist (Zeile 13).
- Mit dem Verschieben des ALTEN Datensatzes geht einher, dass er nach der Speicherung aktualisiert werden muss (Zeile 14).
- Gleiches gilt natürlich für den ursprünglichen Speicherort. Dort wird statt des ursprünglichen Datensatzes lediglich ein Verweis auf den neuen Speicherort geschrieben (Zeile 15)
- Bedingt durch die Aktualisierung (aus 2.000 Bytes werden nun 8 Bytes) muss auch die PFS-Datenseite erneut aktualisiert werden; schließlich ist Seite 119 nun nicht mehr zu 100% gefüllt (Zeile 16)
FORWARDED RECORS erkennen
Forwarded Records können nur in Heaps auftreten und haben ähnliche Auswirkungen auf das I/O wie fragmentierte Indexe. Ein Forwarded Record bedeutet erhöhtes I/O, da von der Original-Datenseite, auf der der Datensatz gespeichert wurde, nur noch ein Verweis auf den tatsächlichen Speicherort zeigt. Damit wird Microsoft SQL Server gezwungen, das Lesen zunächst auf der verwiesenen Datenseite fortzusetzen. Es gilt also, rechtzeitig festzustellen, ob – und wie viele – Forwarded Records in einem Heap existieren.
SELECT DDIPS.index_id,
DDIPS.index_type_desc,
DDIPS.page_count,
DDIPS.record_count,
DDIPS.min_record_size_in_bytes,
DDIPS.max_record_size_in_bytes,
DDIPS.forwarded_record_count
FROM sys.dm_db_index_physical_stats
(
DB_ID(),
OBJECT_ID('dbo.demo_table', 'U'),
0,
NULL,
'DETAILED'
) AS DDIPS;
GO
Die Analyse des Index zeigt, dass nunmehr 6 Datenseiten im Heap vertreten sind. Besonders interessant ist, dass – obwohl kein neuer Datensatz hinzugefügt wurde – ein weiterer Datensatz hinzugekommen ist. Das liegt daran, dass der Verweis auf den neuen Speicherort innerhalb von Microsoft SQL Server wie ein normaler Datensatz behandelt wird. Das hier ein “besonderer” Datensatz gespeichert wird, erkennt man an der [min_record_size_in_bytes], die bei 9 Bytes liegt. Alle eingetragenen Datensätze haben eine Länge von 2.015 Bytes! Die Spalte [forwarded_record_count] weist darauf hin, dass es einen Datensatz gibt, der so groß ist, dass er mit seinem Volumen nicht mehr auf die ursprüngliche Datenseite passt.
FORWARDED RECORDS lesen
Ein Forwarded Record kann einen erheblichen Einfluss auf das IO für eine Abfrage haben wie das nachfolgende Beispiel zeigt. Es wird exakt die gleiche Abfrage ausgeführt wie bereits weiter oben beschrieben. Zu erwarten wäre ein IO von 6 Datenseiten wie die Statistik des Heaps in der obigen Abbildung vermuten lässt; das Ergebnis ist überraschend:
-- INDEX SCAN über 6 Datenseiten? SET STATISTICS IO ON; SELECT * FROM dbo.demo_table AS DT; SET STATISTICS IO OFF; GO
Insgesamt muss für die Abfrage auf 7 Datenseiten zugegriffen werden; und diese Zahl ist – basierend auf der internen Struktur – vollkommen in Ordnung wie die nachfolgende Abbildung demonstriert!
Wenn ein Heap gelesen wird, gibt es keinen Index, an dem sich die “Leserichtung” oder “Sortierung” orientieren kann. Zunächst wird durch einen Zugriff auf die IAM-Datenseite festgestellt, welche Datenseiten durch den Heap allokiert wurden. Durch den Forwarded Record ist – intern – eine weitere Datenseite hinzugekommen. Microsoft SQL Server “weiß” durch das Lesen der IAM-Datenseite, dass die Datenseiten 119, 121, 126, 127, 142 und 156 gelesen werden müssen. Das sind die Datenseiten, die durch den Heap belegt werden.
Mit dem ersten IO wird die Datenseite 119 gelesen. Während die Datensätze von 119 gelesen werden, trifft Microsoft SQL Server auf einen Forwarded Record und liest diesen Datensatz von der “neuen” Adresse auf Seite 156 (2. IO). Nachdem der Forwarded Record gelesen wurde, wird mit Seite 121 fortgefahren: 121 –> 126 –> 127 –> 142 -> 156! Microsoft SQL Server muss Datenseite 156 zwei Mal lesen! Beim ersten Lesevorgang ist ausschließlich der Forwarded Record betroffen. Er ist initial für den Zugriff auf Datenseiten 156. Die Reihenfolge der Lesezugriffe sieht wie folgt aus:
119 –> 156 –> 121 –> 126 –> 127 –> 142 –> 156 = 7 IO.
Die Lesevorgänge können durch den Aufruf von Sperren, die durch Microsoft SQL Server beim Lesen gesetzt werden, transparent gemacht werden. Der nachfolgende Code zeigt für jeden Zugriff die gesetzten und freigegebenen Sperren in der Tabelle.
-- make locks and releases visible! DBCC TRACEON (3604, 1200, -1); SELECT * FROM dbo.demo_table AS DT; DBCC TRACEOFF(3604, 1200, -1); GO
Die aktivierten Traceflags bewirken, dass verwendete Sperren und Freigaben in SSMS protokolliert werden. Das Ergebnis stellt sich wie folgt dar:
Process 60 acquiring IS lock on OBJECT: 6:245575913:0 (class bit0 ref1) result: OK Process 60 acquiring IS lock on PAGE: 6:1:119 (class bit0 ref1) result: OK Process 60 acquiring S lock on RID: 6:1:119:0 (class bit0 ref1) result: OK Process 60 acquiring IS lock on PAGE: 6:1:156 (class bit0 ref1) result: OK Process 60 acquiring S lock on RID: 6:1:156:0 (class bit0 ref1) result: OK Process 60 releasing lock on RID: 6:1:156:0 Process 60 releasing lock on PAGE: 6:1:156 Process 60 releasing lock on RID: 6:1:119:0 Process 60 releasing lock on PAGE: 6:1:119 Process 60 acquiring IS lock on PAGE: 6:1:121 (class bit0 ref1) result: OK Process 60 releasing lock on PAGE: 6:1:121 Process 60 acquiring IS lock on PAGE: 6:1:126 (class bit0 ref1) result: OK Process 60 releasing lock on PAGE: 6:1:126 Process 60 acquiring IS lock on PAGE: 6:1:127 (class bit0 ref1) result: OK Process 60 releasing lock on PAGE: 6:1:127 Process 60 acquiring IS lock on PAGE: 6:1:142 (class bit0 ref1) result: OK Process 60 releasing lock on PAGE: 6:1:142 Process 60 acquiring IS lock on PAGE: 6:1:156 (class bit0 ref1) result: OK Process 60 releasing lock on PAGE: 6:1:156 Process 60 releasing lock on OBJECT: 6:245575913:0
Nachdem eine IS-Sperre auf die Tabelle gesetzt wurde, wird die Datenseite 119 gelesen. Hierbei wird gleich beim ersten Datensatz eine Zeilensperre angewendet, um anschließend auf die neue Datenseite zu gelangen, auf der sich der Datensatz als „Forwarded Record“ befindet. Sobald der Datensatz gelesen wurde, wird die Sperre auf Datenseite 156 und anschließend auf Datenseite 119 wieder aufgehoben und der Prozess liest alle anderen Datenseiten. Die Datenseite 156 muss zwei Mal gelesen werden, da die zu lesenden Datenseiten über die IAM festgelegt waren.
Herzlichen Dank fürs Lesen!
