Durch einen Thread in den MSDN-Foren von Microsoft wurde ich auf ein sehr interessantes Verhalten von Microsoft SQL Server 2012 aufmerksam, das beim Hinzufügen von neuen Attributen zu bestehenden Relationen zu Tage tritt. Bis Microsoft SQL Server 2008 R2 wurde beim Anfügen eines neuen Attributs ein eine bestehende Relation automatisch der benötigte Platz für die Datensätze reserviert, wenn dem hinzugefügten Attribut ein DEFAULT-Wert zugewiesen wurde. Durch die neue Methode von Microsoft SQL Server 2012 wird dieses Verhalten vollständig “über Board” geworfen und zu Gunsten von Performance beim Erstellen von neuen Attributen radikal geändert.

Ausgangssituation

Zunächst wird eine Relation erstellt, die einen trivialen Aufbau besitzt:

USE tempdb;
GO

IF OBJECT_ID('dbo.foo', 'U') IS NOT NULL
    DROP TABLE dbo.foo;
    GO

CREATE TABLE dbo.foo
(
    Id    int        NOT NULL IDENTITY (1, 1),
    col1  char(100)  NOT NULL DEFAULT ('Uwe'),
    col2  char(100)  NOT NULL DEFAULT ('Ricken'),

    CONSTRAINT foo_id PRIMARY KEY CLUSTERED (id)
);
GO

Nachdem die Relation erstellt wurde, können ein paar Beispieldaten hinzugefügt werden, um das Verhalten zu untersuchen. Gleichwohl wird nach dem Eintragen der Daten der Clustered Index neu aufgebaut, um keine Fragmentierungen im Index zu haben.

SET NOCOUNT ON
GO

INSERT INTO dbo.foo DEFAULT VALUES;
GO 1000

SET NOCOUNT OFF

ALTER INDEX foo_id ON dbo.foo REBUILD;

Ein erster Blick auf den Index zeigt, dass keine Fragmentierung auf allen 27 Leafs besteht. Insgesamt sind 1000 Datensätze in der Relation.

SELECT  index_type_desc,
        index_depth,
        avg_fragmentation_in_percent,
        page_count,
        record_count
FROM    sys.dm_db_index_physical_stats(db_id(), object_id('dbo.foo', 'U'), 1, DEFAULT, 'DETAILED');

Nun wird der Relation ein weiteres Attribut [col3] hinzugefügt. Gleichwohl wird für dieses Attribut eine DEFAULT-Einschränkung definiert!

BEGIN TRANSACTION AddColumn
ALTER TABLE dbo.foo ADD [col3] char(200) NOT NULL DEFAULT ('Eine neue Spalte');
COMMIT TRANSACTION AddColumn

Ein SELECT auf die Relation zeigt, dass das gewünschte Ergebnis vorhanden ist – das neue Attribut wurde erfolgreich angelegt und mit “Eine neue Spalte” vorbelegt:

Schaut man sich jedoch noch einmal den Clustered Index an, wird man überrascht feststellen, dass sich dort NICHTS verändert hat (eine weitere Abbildung erspare ich den Lesern hier; die Werte sind absolut identisch mit denen der ersten Überprüfung. Es gibt 27 Seiten für die 1.000 Datensätze. Die Frage, die sich hier stellt; wie kann es sein, dass Microsoft SQL Server 2012 augenscheinlich Daten hinzufügt aber sich diese Datenmenge nicht physikalisch zeigt? Aufschluss gibt in diesem Fall zunächst einmal der Blick in das Transaktionsprotokoll. Um die Datenmenge einzugrenzen habe ich eine benannte Transaktion erstellt, nach der ich nun im Protokoll filtern kann:

SELECT  AllocUnitName,
        Operation,
        Context,
        [Page ID],
        [Slot ID],
        [Lock Information],
        Description
FROM    sys.fn_dblog(NULL, NULL)
WHERE   [Transaction ID] = (SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'AddColumn')
ORDER BY
        [Transaction ID] ASC;

Das Transaktionsprotokoll zeigt sehr deutlich, dass KEINE Daten in der Relation aktualisiert wurden. Vielmehr wurden ausschließlich DDL-Aktionen durchgeführt. Alle Objekte, die von der obigen Transaktion betroffen sind, sind Systemobjekte für die Speicherung der Metadaten; keine Benutzerdaten!

Es stellt sich also die Frage, wie sieht es auf der Datenseite aus, wenn doch keine Daten geändert wurden, diese Daten aber – anscheinend – dennoch im System sind. Dazu genügt ein Blick auf eine der Datenseiten (Leaf) des Clustered Index.

-- Informationen über die verwendeten Datenseiten
SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.foo;

Der Datensatz mit der [Id] = 1 liegt auf der Datenseite 48696 in der Datei #1 (dieser Wert kann bei eigenen Tests abweichen!). Schaut man sich den Datensatz selbst nun etwas genauer an, wird man die folgende überraschende Entdeckung machen:

DBCC TRACEON(3604);
DBCC PAGE ('tempdb', 1, 48696, 3);

Die obige Abbildung zeigt den auf der Datenseite gespeicherten Datensatz. Besondere Beachtung gilt dem Wert von (physical). Während die Bytes für [Id], [Col1] und [Col2] gemäß der Definition bei der Erstellung ihren Speicherbereich allokieren, ist bei dem neu hinzugefügten Attribut [Col3] wohl ein Rechenfehler unterlaufen! Obwohl [col3] augenscheinlich den Wert “Eine neue Spalte” gespeichert hat, allokiert dieser Wert keinen Platz auf der Datenseite; die physikalische Länge beträgt “0”!

Die Erklärung für dieses Verhalten ist relativ einfach. Durch die Änderung der Metadaten wurde für das neue Attribut durch das Bestimmen einer DEFAULT-Einschränkung gleichzeitig ein DEFAULT-Wert in den Systemrelationen hinterlegt. Die entsprechenden Informationen über dieses Verhalten kann man durch Abfrage von Systemrelationen erkennen:

SELECT  c.name                   AS column_name,
        pc.modified_count,
        pc.max_inrow_length,
        pc.has_default,
        pc.default_value
FROM    sys.system_internals_partitions p INNER JOIN sys.system_internals_partition_columns pc
        ON (p.partition_id = pc.partition_id) INNER JOIN sys.columns c
        ON (
             p.object_id = c.object_id AND
             pc.partition_column_id = c.column_id
           )
WHERE    p.object_id = object_id('dbo.foo')
ORDER BY
        c.column_id ASC;

Wie man aus der obigen Abbildung sehen kann, sind für [col3] keine Aktualisierungen durchgeführt worden. Die Datenlänge beträgt 200 Bytes und das Attribut hat einen Standardwert hinterlegt. Basierend auf diesen Informationen kommt man zu dem Schluss, dass Microsoft SQL Server 2012 den Wert selbst nicht speichert sondern – ähnlich einem SVERWEIS aus EXCEL oder einem KeyLookup in einem Index – den Standardwert aus der Systemrelation [sys].[system_internals_partition_columns] bezieht. Durch dieses Verhalten ist das Erweitern von großen Relationen um weitere Attribute natürlich sehr schnell, da ausschließlich die Metadaten der zugrunde liegenden Relation geändert werden!

Überschreiben von Standardwerten

Was passiert aber nun, wenn der Standardwert eines Datensatzes überschrieben wird? Das nachfolgende Beispiel zeigt die Veränderungen, die “intern” durchgeführt werden:

BEGIN TRANSACTION UpdateOneRow
UPDATE dbo.foo
SET    col3 = 'Always look at the bright side of live...'
WHERE  Id = 1;
COMMIT TRANSACTION UpdateOneRow
GO

Das Transaktionsprotokoll zeigt, was auf Seiten der Daten passiert – es wird EIN Datensatz aktualisiert

Ein Blick auf die Datenseite selbst zeigt, dass nun – nach der Aktualisierung des Datensatzes der Speicher allokiert wird. Andere Datensätze bleiben davon noch unberücksichtigt!

Das ist natürlich eine tolle Sache; Microsoft SQL Server allokiert also erst dann den Speicher, wenn tatsächlich eine Änderung des Standardwertes vorgenommen wird. Das hat – wie bereits oben beschrieben – den Vorteil, dass die Transaktion sehr schnell abgeschlossen wird. Man kann dieses Verhalten in etwa mit den protokollierten Vorgängen bei einem TRUNCATE vergleichen. Auch dort werden nicht Datenzeilen einzeln gelöscht sondern – aus Gründen der Performance – die Relation neu aufgebaut!

Interessant wird es aber nun, wenn nicht nur ein Datensatz geändert werden soll sondern pauschal alle Datensätze. In diesem Fall wirkt sich der “Vorteil” schnell zum – geringen - Nachteil aus:

BEGIN TRANSACTION UpdateAllRows
UPDATE dbo.foo
SET    col3 = 'Always look at the bright side of live...'
COMMIT TRANSACTION UpdateAllRows

Zunächst gilt der Blick dem Clustered Index von [dbo].[foo]:

SELECT index_type_desc,
       index_depth,
       avg_fragmentation_in_percent,
       page_count,
       record_count
FROM   sys.dm_db_index_physical_stats(db_id(), object_id('dbo.foo', 'U'), 1, DEFAULT, 'DETAILED');
GO

Durch das Eintragen der Datensätze hat sich das Volumen der Datenseiten fast verdreifacht. Gleichzeitig wurde die Fragmentierung des vorher neu aufgebauten Clustered Index extrem nach oben katapultiert. Ein Blick in das Transaktionsprotokoll zeigt die Ursache für dieses Verhalten.

SELECT AllocUnitName,
       Operation,
       Context,
       [Page ID],
       [Slot ID],
       [Lock Information],
       Description
FROM   sys.fn_dblog(NULL, NULL)
WHERE  [Transaction ID] = (SELECT [Transaction ID] FROM sys.fn_dblog(NULL, NULL) WHERE [Transaction Name] = 'UpdateAllRows')
ORDER BY
       [Transaction ID] ASC;

Sehr genau kann man erkennen, dass für JEDEN Datensatz ein Update durchgeführt wird. Da aber der Index im Vorfeld bereits neu aufgebaut wurde, bedeutet dies, dass zusätzlicher Speicher auf den Datenseiten allokiert werden muss. In diesem Fall passiert dann ein sogenannter Page Split, wie ich ihn im Artikel “Fillfactor – Vor- und Nachteile” beschrieben habe. Das zeigt auch eine Abfrage auf das Transaktionsprotokoll:

SELECT Operation,
       COUNT_BIG(*)
FROM   sys.fn_dbLog(NULL, NULL)
WHERE  Operation = 'LOP_DELETE_SPLIT'
GROUP BY
       Operation;

Insgesamt mussten 51 sogenannte “Page Splits” durchgeführt werden. Solche Page Splits treten auf, wenn ein Datensatz neu auf einer Datenseite eingetragen werden muss oder aber aktualisiert wird. Da dieser Datensatz dann u. U. nicht mehr auf die Datenseite passt, muss die Datenseite geteilt werden. Hierbei werden ca. 50% der Daten in eine neue Datenseite verschoben um Platz zu schaffen. Anschließend wird der neue Datensatz – für den nun Platz vorhanden ist – eingetragen. Auf Grund dieser Trennung kann Microsoft SQL Server nun aber die Daten nicht mehr hintereinander lesen sondern muss zwischen den Datenseiten springen. Unter Berücksichtigung der durchgeführten Page Splits kommt dieser hohe Fragmentierungsgrad zum Tragen. Wird der Index neu aufgebaut, werden die Fragmentierungen beseitigt und die Daten können wieder sequentiell gelesen werden.

ALTER INDEX foo_id ON dbo.foo REBUILD;

Unmittelbarer INDEX-Rebuild nach dem Hinzufügen eines neuen Attributs

Wenn ein neues Attribut einer Relation hinzugefügt wird, das die Einschränkung “NOT NULL” besitzt, belegt Microsoft SQL Server 2012 nicht sofort den erforderlichen Speicherbereich in der Datenseite, sondern es werden ausschließlich Metadaten geändert. Sobald der Clustered Index neu aufgebaut wird, werden jedoch die Daten sofort in die Datenseiten eingetragen. Von daher kann der REBUILD des Index bei großen Datenmengen deutlich länger dauern. Das nachfolgende Script demonstriert dieses Verhalten noch einmal:

Aufbau der Relation

USE tempdb;
GO

IF OBJECT_ID('dbo.foo', 'U') IS NOT NULL
    DROP TABLE dbo.foo;
    GO

CREATE TABLE dbo.foo
(
    Id    int        NOT NULL IDENTITY (1, 1),
    col1  char(100)  NOT NULL DEFAULT ('Uwe'),
    col2  char(100)  NOT NULL DEFAULT ('Ricken'),

    CONSTRAINT foo_id PRIMARY KEY CLUSTERED (id)
);
GO

Hinzufügen von 1000 Datensätzen und REBUILD des Clustered Index

SET NOCOUNT ON GO
INSERT INTO dbo.foo DEFAULT VALUES;
GO 1000
SET NOCOUNT OFF

ALTER INDEX foo_id ON dbo.foo REBUILD; 

Hinzufügen eines neuen Attributs und unmittelbarer Neuaufbau des Clustered Index

ALTER TABLE dbo.foo ADD [col3] char(200) NOT NULL DEFAULT ('Eine neue Spalte');
ALTER INDEX foo_id ON dbo.foo REBUILD;

Überprüfung des Clustered Index

SELECT  index_type_desc,
        index_depth,
        avg_fragmentation_in_percent,
        page_count,
        record_count
FROM    sys.dm_db_index_physical_stats(db_id(), object_id('dbo.foo', 'U'), 1, DEFAULT, 'DETAILED');

Man kann an Hand des obigen Beispiels wunderbar erkennen, dass Microsoft SQL Server 2012 bei der Neustrukturierung der Relation die Standardwerte für das neue Attribut [col3] einträgt. Sobald ALLE Zeilen mit einem individuellen Wert physikalisch gefüllt worden sind, wird der Referenzwert aus sys.system_internals_partition_columns nicht mehr benötigt. Spätestens mit dem REBUILD des Index werden – wie oben gezeigt – alle Zeilen gefüllt.

SELECT  c.name                   AS column_name,
        pc.modified_count,
        pc.max_inrow_length,
        pc.has_default,
        pc.default_value
FROM    sys.system_internals_partitions p INNER JOIN sys.system_internals_partition_columns pc
        ON (p.partition_id = pc.partition_id) INNER JOIN sys.columns c
        ON (
             p.object_id = c.object_id AND
             pc.partition_column_id = c.column_id
           )
WHERE    p.object_id = object_id('dbo.foo')
ORDER BY
        c.column_id ASC;

Dieses Ergebnis macht aus Sicht der Arbeitsweise von Microsoft SQL Server 2012 Sinn. Da keine Zeile mehr vorhanden ist, die ihren Wert aus dem “DEFAULT” für das Attribut beziehen muss, wird dieser Standardwert nicht mehr benötigt und kann aus der Systemrelation entfernt werden.

Fazit

Eine schöne neue Funktionalität, die sich das Entwicklerteam von Microsoft für Microsoft SQL Server 2012 ausgedacht hat. Durch dieses Verfahren werden die Sperren auf die Relation nicht mehr so lang benötigt, wie bisher. Statt umständlicher und transaktionslastiger Updates jeder einzelnen Zeile werde nur Schemadaten verändert. Für die Anzeige der Daten kommen dann interne Mechanismen zum Tragen.

Herzlichen Dank fürs Lesen!