In einem Forenbeitrag der deutschen msdn SQL Server Foren wurde ein Problem beschrieben, bei dem die nachträgliche Konvertierung eines Attributs mit fester Zeichenlänge dazu führt, dass in der Ausgabe der Daten die Informationen mit Leerzeichen aufgefüllt werden. Das es sich hierbei um ein “normales” Verhalten von Microsoft SQL Server handelt, beschreibt der nachfolgende Artikel.

Teststellung

Für die Demonstration des oben beschriebenen Verhaltens wird eine Tabelle mit ~280.000 Datensätzen erstellt

USE demo_db;
GO

SELECT language_id,
       message_id,
       severity,
       CAST(LEFT(text, 20) AS CHAR(100)) AS [text]
INTO   dbo.messages
FROM   sys.messages;

Die Tabelle besitzt ein Attribut „TEXT“ mit einer festen Zeichenlänge von 100 Zeichen. Bei solchen Attributen füllt Microsoft SQL Server automatisch das Attribut mit Leerzeichen, bis der Eintrag 100 Zeichen lang ist.

SELECT page_type_desc
       allocated_page_file_id,
       allocated_page_page_id
FROM   sys.dm_db_database_page_allocations
       (
           DB_ID(),
           OBJECT_ID(N'dbo.messages', N'U'),
           0,
           NULL,
           N'DETAILED'
       );
GO

Mit dem obigen T-SQL-Code werden alle allokierten Datenseiten der neu angelegten Tabelle ausgegeben und ein Blick in die Datenseite 360 zeigt, wie die Datensätze gespeichert werden.

DBCC TRACEON (3604);
DBCC PAGE (0, 1, 360, 1);
GO

Der markierte Bereich zeigt den Hexwert 0x20, der ein Leerzeichen repräsentiert. Microsoft SQL Server füllt den verbleibenden Platz mit Leerzeichen auf, damit 100 Zeichen in das Attribut geschrieben werden. Feste Datenlängen werden sehr häufig verwendet, um “Forwarded Records” in Heaps oder “Page Splits” in gruppierten Indexen zu vermeiden, wenn die Attribute häufig mit unterschiedlichen Datenlängen aktualisiert werden müssen. Entscheidet man sich nachträglich, aus Attributen mit fester Datenlänge Attribute mit variabler Datenlänge zu machen, kann es zu Überraschungen kommen, wie das nächste Beispiel zeigt.

Los geht’s!

Das Attribut „TEXT“ in der Beispieltabelle wird mit Hilfe von DDL-Befehlen vom Datentypen CHAR zu einem VARCHAR geändert.

ALTER TABLE dbo.messages
ALTER COLUMN [text] VARCHAR(100);
GO

Sperren während der Operation

Da es sich um DDL-Operationen handelt, muss Microsoft SQL Server sicherstellen, dass während der Aktualisierung niemand auf die Tabelle zugreift. Das wird mit Hilfe einer SCH-M-Sperre gewährleistet.

SELECT OBJECT_NAME(resource_associated_entity_id) AS object_name,
       request_mode,
       request_type,
       request_status
FROM   sys.dm_tran_locks
WHERE  request_session_id = @@SPID
       AND resource_type = N'OBJECT';
GO

Transaktionsprotokoll

Interessant ist ein Blick in das Transaktionsprotokoll um festzustellen, welche Schritte genau Microsoft SQL Server durchführen musste, um die Typenkonvertierung abzuschließen. Da es sich bei der Testtabelle um einen HEAP handelt, werden nur die Aktionen, die sich auf den HEAP beziehen, summiert.

SELECT Context,
       Operation,
       COUNT_BIG(*)
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Context = N'LCX_HEAP'
GROUP BY
       Context,
       Operation;
GO

Hervorzuheben bei der Untersuchung des Transaktionsprotokolls ist die Anzahl der “LOP_INSERT_ROWS” sowie “LOP_FORMAT_PAGES”. Die Erklärung dazu folgt etwas weiter unten!

Microsoft SQL Server ändert 275.370 Datensätze in der Tabelle (LOP_MODIFY_ROW); man könnte also vermuten, dass die Leerzeichen aus dem Attribut entfernt wurden. Ein Blick auf die Datenseiten zeigt jedoch, dass die Anpassungen sich nicht auf die Leerzeichen, sondern auf die Zeilenstruktur auswirken!

Strukturanpassungen

Interessant ist bei der Untersuchung der Datenseite, dass sich die Größe des Datensatzes fast verdoppelt hat – insgesamt ist der Datensatz von ursprünglich 114 Bytes auf 218 Bytes gewachsen. Es ist zu erkennen, dass Microsoft SQL Server den “ursprünglichen” Wert des Attributs „TEXT“
als neuen Wert am Ende eingefügt hat!

Um zu verstehen, warum Microsoft SQL Server einen so hohen Overhead bei einer Strukturänderung benötigt, muss man die interne Struktur eines Datensatzes verstehen. Dieser Artikel bezieht sich nur auf die Besonderheiten, die für für das Verstehen der Operation relevant sind! Wer mehr Informationen über die Anatomie eines Datensatzes benötigt, dem kann ich den Artikel “Inside the Storage Engine: Anatomy of a record” von Paul Randal (SQLSkills) empfehlen!

Eine genaue Betrachtung des Recordheaders zeigt, dass mehr als nur Daten verändert wurden. Die ersten 4 Bytes einer Datenzeile beschreiben den Typen des Datensatzes sowie das Offset zum NULL-Bitmap!

Die Abbildung zeigt einen Datensatz VOR der Umwandlung des Datentypen und einen Datensatz NACH der Umwandlung des Datentypen. Man kann erkennen, dass das erste Byte geändert wurde (aus 0x10 wird 0x30).

0x10 = Bit 4 = Datensatz besitzt ein NULL-Bitmap

0x20 = Bit 5 = Datensatz besitzt Attribute mit variablen Datentypen

Bytes 3 und 4 bestimmen das Offset des NULL-Bitmaps, das im vorliegenden Fall identisch ist (0x6F00 = 111). Ein Blick auf die 111. Position zeigt einen weiteren Unterschied!

Die markierte Position (2 Bytes) zeigt die Anzahl der Spalten im Datensatz. Microsoft SQL Server hat nicht den Inhalt des bestehenden Attributs ersetzt, sondern eine weitere Spalte hinzugefügt! Ein Blick auf den Dateninhalt zeigt diese Änderung ebenfalls.

Microsoft SQL Server hat das ursprüngliche Attribut „TEXT“ als DROPPED gekennzeichnet und den Inhalt des Datensatzes als NEUE Spalte hinzugefügt. Dabei wurde jedoch nicht der gekürzte (VARCHAR) Wert genommen sondern der vollständige ursprüngliche Wert. Durch das Wachstum der Länge des Datensatzes ist eine Kettenreaktion in Gang gesetzt worden, die die Logeinträge für LOP_FORMAT_PAGE und LOP_INSERT_ROWS erklären.

Als der Datentyp des Attributs geändert wurde, musste der Datensatz erweitert werden. Die Erweiterung führt dann dazu, dass der geänderte Datensatz nicht mehr auf die Datenseite passt und somit ein FORWARDED RECORD (die Tabelle ist ein HEAP) generiert wird.

SELECT OBJECT_NAME(object_id) AS table_name,
       index_id,
       index_type_desc
       forwarded_record_count
FROM   sys.dm_db_index_physical_stats
       (
           DB_ID(),
           OBJECT_ID(N'dbo.messages', N'U'),
           0,
           NULL,
           N'DETAILED'
);
GO

Die Anzahl der FORWARDED_RECORDS entspricht exakt der Anzahl der “eingefügten” Datensätze. Die Nachteile von FORWARDED RECORDS habe ich im Artikel “Forwarded Records intern” detailliert beschrieben. Das gleiche Verhalten ist auch in einem gruppierten Index zu beobachten – nur werden in einem gruppierten Index keine FORWARDED RECORDS generiert, sondern PAGE SPLITS führen zu einem fragmentierten Index.

Zusammenfassung

Microsoft SQL Server belässt die Leerzeichen in einem Attribut mit fester Zeichenlänge, wenn zu einem Datentypen mit variabler Zeichenlänge gewechselt wird. Dieser Wechselt geht IMMER einher mit FORWARDED RECORDS oder PAGE SPLITS und es ist empfehlenswert, nach der Änderung des Datentypen Indexe neu aufzubauen, die dieses Attribut verwenden.

Herzlichen Dank fürs Lesen!