Auf Grund zahlreicher Emails unter MVP-Kollegen bin ich der Frage nachgegangen, wie sich die Änderung der Größe eines Datenfeldes mit fester Größe auf den Datenkonsum auswirkt. Ein weiterer sehr interessanter Aspekt war der Frage, wie sich die Änderung der Datengröße auf das Transaktionsvolumen auswirkt. Der nachfolgende Artikel geht diesen Fragen nach und beleuchtet die internen Prozesse des Vorgangs.
Wie häufig kommt es vor, dass das ursprünglich entwickelte Design der Metadaten durch die aufkommenden Datenmengen obsolet wird? Letztendlich bleibt dem DBA die Möglichkeit, die bestehende Metadatenstruktur mittels ALTER TABLE … ALTER COLUMN schnell und einfach zu ändern. Bei wenigen Datenzeilen gestaltet sich die Operation – in der Regel – recht einfach und schnell; deutlich mehr Überlegungen sind ratsam, wenn es um mehrere Millionen Datensätze geht. In diesem Fall sollte der komplette Vorgang zunächst in einer Testumgebung durchspielt werden und die Messwerte für Zeit und Volumen sorgfältig mit einer sinnvollen Strategie abgewogen werden!
Testumgebung
Für die Testumgebung wird eine Tabelle mit 10.000 Datensätzen gefüllt. Pro Datenseite werden – basierend auf einer Datensatzlänge von 2.015 Bytes – 4 Datensätze gespeichert.
-- Falls die Tabelle bereits existiert, wird sie gelöscht
IF OBJECT_ID('dbo.demo_table', 'U') IS NOT NULL
DROP TABLE dbo.demo_table;
GO
-- Erstellen der Testtabelle
CREATE TABLE dbo.demo_table
(
Id INT NOT NULL,
c1 CHAR(2001) NOT NULL DEFAULT ('A'),
c2 DATE NOT NULL
);
GO
-- Clustered Index liegt auf Attribut c2
CREATE UNIQUE CLUSTERED INDEX ix_demo_table_c2 ON dbo.demo_table (c2)
GO
Die Tabelle besitzt einen Clustered Key auf [c2]. Jeder Datensatz hat eine feste Datenlänge von 2.015 Bytes. Mit insgesamt 10.000 Datensätzen belegt die Tabelle in der Datenbank 2.502 Datenseiten im Leaf-Level (http://msdn.microsoft.com/de-de/library/ms190457.aspx).
-- Anzahl belegter Datenseiten in Tabelle
SELECT OBJECT_NAME(p.object_id) AS table_name,
au.type_desc,
au.total_pages,
au.used_pages,
au.data_pages
FROM sys.partitions AS P INNER JOIN sys.allocation_units AS AU
ON (p.partition_id = au.container_id)
WHERE p.object_id = OBJECT_ID('dbo.demo_table', 'U');
Basierend auf der Definition des Clustered Keys wird ein Datensatz auf einer Datenseite in der folgenden Reihenfolge gespeichert:
-- Interne Struktur eines Datensatzes auf einer Datenseite
SELECT c.name AS column_name,
c.column_id,
pc.max_inrow_length,
pc.system_type_id,
t.name AS type_name,
pc.leaf_offset
FROM sys.system_internals_partition_columns pc INNER JOIN sys.partitions p
ON (p.partition_id = pc.partition_id) LEFT JOIN sys.columns c
ON (
column_id = partition_column_id AND
c.object_id = p.object_id
) INNER JOIN sys.types AS T
ON (pc.system_type_id = t.system_type_id)
WHERE p.object_id = OBJECT_ID('dbo.demo_table');
Alle Datentypen sind Datentypen mit fester Länge. Die Spalte [leaf_offset] speichert die Offsets der einzelnen Spaltenwerte eines Datensatzes. Ein Datensatz beginnt immer bei Offset 0x04 da der Datensatzheader eine feste Länge von 4 Bytes besitzt (0x00 – 0x03). Nach der Speicherung des Datums (3 Bytes) werden die Daten des Attributs [Id] (4 Bytes) an der Position 0x07 gespeichert und der Inhalt des letzten Attributs [c1] wird ab Position 0x0B gespeichert.
Unabhängig von der Struktur der Metadaten werden die Clustered Key(s) einer Tabelle am Beginn eines Datensatzes gespeichert wenn es sich beim Datentypen des Clustered Keys um einen Datentypen mit fester Länge handelt.
ALTER TABLE … ALTER COLUMN
Die bestehende Datenstruktur soll im Beispiel angepasst werden. Der Datentyp des Attributs [Id] soll von einem INT-Datentypen (4 Bytes) zu einem BIGINT-Datentypen (8 Bytes) umgewandelt werden. Durch die Konvertierung verdoppelt sich das benötigte Speichervolumen für [Id]. Auf einer Datenseite sind maximal 8.060 Bytes für Daten reserviert. Wird die Datenlänge vergrößert, passen die Daten nicht mehr vollständig auf eine Datenseite und es erfolgt ein – kostenintensiver – Page Split!
BEGIN TRANSACTION;
-- Änderung des Datentypen INT zu BIGINT
ALTER TABLE dbo.demo_table ALTER COLUMN [Id] BIGINT NOT NULL;
-- Wie häufig wurden Page Splits durchgeführt?
SELECT Operation, COUNT_BIG(*)
FROM sys.fn_dblog(NULL, NULL) AS FD
WHERE Operation IN
(
'LOP_DELETE_SPLIT',
'LOP_MODIFY_COLUMNS'
)
GROUP BY
Operation;
COMMIT TRANSACTION;
Das Ergebnis zeigt die Modifikation von 10.000 Datensätzen innerhalb der Transaktion. Da mit jeweils 4 Datensätzen die Datenseiten vollständig belegt waren, mussten die Daten JEDER Datenseite auf weitere Datenseiten verteilt werden – eine sehr kostenintensive Operation.
Interne Verwaltung
Die Operation als solche ist – bedingt durch die auftretenden Page Splits – sehr kostenintensiv und würde deutlich kostspieliger werden, wenn beim Erweitern des benötigten Speichers Bestandsdaten überschrieben würden. Microsoft SQL Server verfährt bei der Erweiterung anders als man vermutet.
Slot 0 Offset 0x60 Length 2015
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 2015
Memory Dump @0x0000000015E9A060
Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
c2 = 1953-01-01
Slot 0 Column 1 Offset 0x7 Length 4 Length (physical) 4
Id = 2447
Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
c1 = P46Q7LE7JCKDLJPWX5JRZYKO0297SN5A8TTI3F9SWZTK1JGL66CNZN1R...
<... SNIP ...>
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (0950748c2d13)
Der obige Code zeigt den Auszug einer Datenseite vor der Änderung des Datentypen. Der Auszug der Datenseite entspricht einem Datensatz in der Tabelle. Sehr gut zu erkennen ist, dass das Schlüsselattribut [c2] an erster Position gespeichert wird. Anschließend folgen [Id] und [c1].
Nachdem der Datentyp für das Attribut [Id] von INT auf BIGINT geändert wurde, sieht die Datenseite wie folgt aus:
Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
c2 = 1953-01-01
Slot 0 Column 67108865 Offset 0x7 Length 0 Length (physical) 4
DROPPED = NULL
Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
c1 = P46Q7LE7JCKDLJPWX5JRZYKO0297SN5A8TTI3F9SWZTK1JGL66CNZN1R...
Slot 0 Column 1 Offset 0x7dc Length 8 Length (physical) 8
Id = 2447
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (0950748c2d13)
Der obige Auszug aus der gleichen Datenseite zeigt, wie Microsoft SQL Server vorgeht, wenn der feste Datentyp eines Attributs vergrößert wird. Aus transaktioneller Sicht wäre das “verschieben” des Offsets eine Katastrophe. In diesem Fall müsste Microsoft SQL Server jeweils die ganze Datenseite verschieben. Die nachfolgenden Abbildungen zeigen, welchen Aufwand Microsoft SQL Server bewältigen müsste, wenn der Prozess so umständlich ablaufen würde.
Die obige Abbildung zeigt vier Datensätze, die auf einer Datenseite gespeichert sind. Die Offsets mit dem * sollen die Attribute repräsentieren, die durch einen neuen – größeren – Datentypen ersetzt werden sollen. Würde nun tatsächlich der Speicherbereich an “Ort und Stelle” erweitert werden, müssten alle nachfolgenden Speicherbereiche verschoben werden.
Würde Microsoft SQL Server tatsächlich dieses Verfahren verwenden, müssten für die Erweiterung von Datensatz “A” 38 “Einheiten” verschoben werden (Vorgang 1); um Datensatz “B” zu verschieben, müssten weitere 28 “Einheiten” verschoben werden. Für Datensatz “C” wären es immerhin noch 18 “Einheiten und für den letzten Datensatz weitere 8 “Einheiten – somit insgesamt 92 “Einheiten”. Es scheint logisch, dass für diesen Vorgang nicht nur erhebliche Daten im Transaktionsprotokoll entstehen sondern dass auch der Vorgang selbst sehr zeitintensiv ist. Aus diesem Grund geht Microsoft SQL Server wie folgt vor, wenn ein größerer Datentyp verwendet wird.
Statt die ganze Datenseite zu verschieben, wird die ursprüngliche Spalte als [DROPPED] gekennzeichnet und am Ende des Datenbereichs mit fester Länge wird das Attribut neu angelegt. Durch dieses Verfahren reduziert sich der Overhead für das Verschieben von Daten erheblich.
Ein Blick auf die Datenseite selbst zeigt, wie Microsoft SQL Server die Attribute neu ausgerichtet hat:
Slot 0 Offset 0x60 Length 2023
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 2023
Memory Dump @0x0000000015E9A060
Slot 0 Column 3 Offset 0x4 Length 3 Length (physical) 3
c2 = 1953-01-01
Slot 0 Column 67108865 Offset 0x7 Length 0 Length (physical) 4
DROPPED = NULL
Slot 0 Column 2 Offset 0xb Length 2001 Length (physical) 2001
c1 = 7O9E4PMJ0SEX8N6PN2GPC54XBNY65LOQG1TU4LXJKIKPLYY4JAB...
<;... SNIP...>
Slot 0 Column 1 Offset 0x7dc Length 8 Length (physical) 8
Id = 2447
Slot 0 Offset 0x0 Length 0 Length (physical) 0
KeyHashValue = (0950748c2d13)
Der Auszug gibt eine Vielzahl von Informationen über den Vorgang preis. Auffällig ist, dass der Datensatz nicht um 4 Bytes gewachsen ist sondern um 8 Bytes! Dieses Ergebnis ist dem Umstand geschuldet, dass das ursprüngliche Attribut nicht physikalisch aus dem Datensatz entfernt wurde sondern weiterhin 4 Bytes konsumiert (rot gekennzeichnet). Zum bestehenden Datensatz ist ein *neues* Attribut hinzugekommen, dass weitere 8 Bytes konsumiert. Dieses Attribut wird im Datensatz als [Column 1] deklariert. Sehr gut erkennbar ist, dass sich die Anordnung der Attribute im Datensatz geändert hat. Die ursprüngliche physikalische Reihenfolge war [c2], [Id], [c1]. Durch die Erweiterung sieht die physikalische Reihenfolge nun wie folgt aus:
-- Interne Struktur eines Datensatzes auf einer Datenseite
SELECT c.name AS column_name,
c.column_id,
pc.max_inrow_length,
pc.system_type_id,
t.name AS type_name,
pc.leaf_offset
FROM sys.system_internals_partition_columns pc INNER JOIN sys.partitions p
ON (p.partition_id = pc.partition_id) LEFT JOIN sys.columns c
ON (
column_id = partition_column_id AND
c.object_id = p.object_id
) INNER JOIN sys.types AS T
ON (pc.system_type_id = t.system_type_id)
WHERE p.object_id=object_id('dbo.demo_table');
GO
Das zuvor an Position 2 geführte Attribut [Id] ist logisch nicht mehr vorhanden. Es konsumiert weiterhin 4 Bytes und beginnt an Offset 0x07. Das Attribut wurde “an anderer Stelle” im Datensatz neu implementiert. Das Attribut [Id] ist nun an das Ende des Bereichs für feste Datenlänge “gerutscht” und konsumiert dort 8 Bytes.
Lösung
Um den überflüssigen Speicherverbrauch wieder aus den Datenseiten zu eliminieren, reicht es aus, den Clustered Index neu aufzubauen. Durch den REBUILD wird der Speicherbereich wieder freigegeben und die Attribute wieder in der korrekten physikalischen Reihenfolge angeordnet.
ALTER INDEX ix_demo_table_c2 ON dbo.demo_table REBUILD;
Zusammenfassung
Das Erweitern von festen Datentypen erfordert eine sorgfältige Planung, wenn es sich um sehr große Tabellen handelt. Bedingt durch den gewaltigen Overhead sind bei solchen Metadaten-Änderungen folgende Aspekte zu berücksichtigen und nach Möglichkeit in einer Testumgebung sorgfältig zu prüfen:
Vollständig protokollierte Transaktion
Änderungen von Metadaten sind immer vollständig protokollierte Transaktionen. Dieser Umstand bedeutet in der Umsetzung, dass bei sehr hoher Datenmenge ein nicht zu unterschätzendes Transaktionsvolumen generiert wird. Aus diesem Grund sollte bereits vor der Umsetzung das Transaktionsprotokoll ausreichend dimensioniert sein, um Wartezeiten durch AUTOGROWTH zu vermeiden. Dateien eines Transaktionsprotokolls partizipieren nicht von Instant File Initialization und somit ist der zeitliche Overhead für Vergrößerungsvorgänge der Protokolldatei ein Faktor bei der Berechnung des Zeitaufwands.
Fragmentierung von Indexen
Durch die Vergrößerung der Datensatzlänge muss dieser Platz im Datensatz reserviert werden. Wird die Vergrößerung auf einen frisch aufgebauten Index angewendet, ist davon auszugehen, dass bereits durch die Page Splits ca. 4 – 6 KByte zusätzliches Transaktionsvolumen pro Datenseite generiert werden.
Man kann diese Overhead weitestgehend vermeiden, indem man folgende Punkte VOR der Vergrößerung beachtet und umsetzt:
- Analyse der durch den Index belegten Datenseiten (siehe Abfrage oben)
- Überprüfung der durchschnittlichen Anzahl von Datensätzen / Datenseite
- Neuaufbau des Index (im geeigneten Servicefenster) mit einem FILLFACTOR, der so viel Platz auf der Datenseite belässt, dass eine Erweiterung ohne Page Splits durchgeführt werden kann.
Beispiel für die Berechnung des FILLFACTOR für den Neuaufbau des Index:
Ein Index belegt 100.000 Datenseiten. Ein Datensatz hat eine Länge von ~400 Bytes. Somit passen auf eine Datenseite 20 Datensätze. Ein Attribut vom Datentypen [INT] soll zu einem [BIGINT] geändert werden. Somit müssen auf einer Datenseite für den Erweiterungsvorhang 20 * 8 Bytes = 160 Bytes zusätzlich zur Verfügung stehen. Ein FILLFACTOR von 5% ist für den zu bewältigenden Änderungsvorgang ein guter Ausgangswert für den Index. Der gesamte Vorgang der Vergrößerung unterteilt sich somit in drei Phasen:
- Neuaufbau des Index mit einem FILLFACTOR von 5%
- Anpassung des Datentypen (INT –> BIGINT)
Herzlichen Dank fürs Lesen!
PS: Ich wünsche allen Lesern meines Blogs einen guten Start, Glück und Gesundheit für 2015. Ich hoffe, dass die von mir gewählten Themen interessant und informativ sind. Ich freue mich auf 2015 mit vielen Konferenzen und neuen Kontakten.
Es würde mich sehr freuen, die Leser meines Blogs persönlich kennenzulernen – also einfach mal ansprechen, wenn wir uns auf einer Konferenz begegnen sollten.
Die erste Möglichkeit in diesem Jahr wäre z. B. die SQL Konferenz (http://www.sqlkonferenz.de). Dort spreche ich am 05.02.2015 über tägliche Indexprobleme, wie man sie erkennt und wie man sie lösen kann!
