Die Inspiration für diesen Artikel habe ich beim zufälligen Surfen durch die Weiten des Internets erhalten, als ich ein Video gesehen habe, in dem es um die Speicherung von Daten in Microsoft SQL Server ging. In den Demos hat der Autor des Videos jedoch einen kleinen aber extrem wichtigen Hinweis vergessen; er verwendet für die Demos einen HEAP. Mit diesem Beispiel wollte der Autor zeigen, wie Daten auf einer Datenseite gespeichert werden. Die Struktur der Tabelle war so gewählt, dass – rechnerisch – 8 Datensätze auf eine Datenseite gehen sollten. Nach dem 7 Datensatz jedoch wurde eine neue Datenseite erstellt und es verblieben mehr als 1.000 Bytes auf der ursprünglichen Datenseite frei. Die Ursache dafür liegt – wie schon erwähnt – in der Verwendung des Heaps als Bespiel.

Was ist ein Heap

Ein HEAP ist eine Tabelle ohne gruppierten Index. Daten werden ohne bestimmte Reihenfolge im Heap gespeichert. Normalerweise werden Daten anfänglich in der Reihenfolge gespeichert, in der die Zeilen in die Tabelle eingefügt werden. Microsoft SQL Server kann die Daten jedoch im Heap verschieben, um die Zeilen effizienter zu speichern; daher kann die Reihenfolge der Daten nicht vorhergesagt werden. Ein HEAP verwendet die PFS-Seite für die Speicherung des Füllgrads einer Datenseite.

PFS

HEAPS haben gegenüber einem gruppierten Index eine Besonderheit, die ausschließlich bei HEAPS auftritt; der Füllgrad einer Datenseite wird in der PFS-Datenseite gespeichert! Wenn neue Datensätze in einem HEAP gespeichert werden, muss in der PFS-Datenseite nach einer Datenseite gesucht werden, die ausreichend Platz für die Speicherung der Daten in der Tabelle bereitstellt. Sobald ein neuer Datensatz in die Tabelle eingetragen wird, wird im Anschluss die PFS-Seite mit einem prozentualen Wert aktualisiert, der beschreibt, zu wie viel Prozent die Datenseite gefüllt ist. Hierbei können nur 5 mögliche Prozentwerte gespeichert werden:

ProzentwertBeschreibung
NULL / 0%Die allokierte Datenseite ist leer
50%Die allokierte Datenseite ist mit maximal  4.030 Bytes gefüllt.
80%Die allokierte Datenseite ist mitmaximal  6.424 Bytes gefüllt.
95%Die allokierte Datenseite ist mit  maximal ~7.629 Bytes gefüllt
100%width=“290″ valign=“top“>Die Datenseite ist voll

Diese Besonderheit kann bei einem nicht durchdachten Tabellendesign schnell dazu führen, dass Microsoft SQL Server kostbaren Speicher nicht belegt, da der prozentuale Belegungswert vom tatsächlichen Belegungswert abweicht.

Demo

Das folgende Beispiel zeigt, wie ein ungünstiges Tabellendesign zu einer Verschwendung von Ressourcen führen kann. Hierzu wird eine Tabelle mit einer primitiven Struktur angelegt. Jeder Datensatz hat eine Datensatzlänge von 1.001 Bytes. Somit passen rechnerisch bei einem Payload von 8.060 Bytes 8 Datensätze auf eine Datenseite!

CREATE TABLE dbo.Customers
(
    Id INT NOT NULL IDENTITY (1, 1),
    C1 CHAR(990) NOT NULL
);
GO

1. Datensatz wird eingetragen

Sobald die Tabelle erstellt wurde, kann der erste Datensatz eingetragen werden. Mit Hilfe von sys.dm_db_database_page_allocations kann im Anschluss der Füllgrad für die Seite bestimmt werden.

INSERT INTO dbo.Customers (C1) VALUES ('Test 1');
GO

Bevor der Füllgrad überprüft wird, gilt der erste Blick dem Transaktionsprotokoll. Die wesentlichen Punkte sind farblich hervorgehoben!

SELECT Operation,
       Context,
       AllocUnitName,
       [Page ID],
       [Slot ID]
FROM   sys.fn_dblog(NULL, NULL);
GO
image

Nachdem der Datensatz auf der Datenseite eingetragen wurde (LOP_INSERT_ROWS), wurde die PFS-Seite ebenfalls aktualisiert (LOP_SET_FREE_SPACE). Dieser Vorgang ist logisch; zunächst wird ein neuer Datensatz auf der Datenseite gespeichert. Dadurch ändert sich der Füllgrad der Seite. Gem. obiger Tabelle müsste der Füllgrad von 0% auf 50% geändert worden sein.

SELECT allocated_page_page_id,
       page_free_space_percent
FROM   sys.dm_db_database_page_allocations
(
    DB_ID(),
    OBJECT_ID(N'dbo.Customers', N'U'),
    0,
    NULL,
    N'DETAILED'
)
WHERE
    page_type < 10;   --exclude the IAM
GO
image

Das Ergebnis gibt uns Recht. Microsoft SQL Server “glaubt” nun, dass die Datenseite zu 50% gefüllt ist. Rein rechnerisch stehen somit immer noch 4.030 Bytes für die Speicherung von weiteren Datensätzen zur Verfügung. Wird nun der nächste Datensatz eingetragen, ergibt sich das folgende Bild:

2. – 4. Datensatz wird eingetragen

INSERT INTO dbo.Customers (C1) VALUES ('Test 2');
GO
image

Das Protokoll zeigt, dass ein neuer Datensatz in Slot 1 eingetragen wurde – aber die PFS-Seite wurde nicht aktualisiert. In diesem Punkt ist Microsoft SQL Server recht performant. Beim Speichern des 2. Datensatzes werden insgesamt 2 * 1.001 Bytes = 2.002 Bytes verbraucht. Damit bleibt das tatsächliche Volumen unter 4.030 Bytes und Microsoft SQL Server muss die PFS-Seite nicht aktualisieren. Das gleiche Muster ergibt sich für Datensatz 3 und Datensatz 4! Das Transaktionsprotokoll zeigt für die nächsten beiden Eintragungen das folgende Vorgehen:

image

Mit dem Eintrag des 4. Datensatzes befinden sich 4.004 Bytes auf der Datenseite. Das Transaktionsprotokoll zeigt, dass die PFS-Seite nicht aktualisiert werden musste, da das komplette Datenvolumen immer noch unter 50% bleibt. Mit dem Eintrag des 5. Datensatzes ändert sich das Verhalten wieder. Mit der Speicherung des 5. Datensatzes werden insgesamt 5.005 Bytes auf der Datenseite gespeichert. Somit wird die Grenze von 4.030 Bytes überschritten und die PFS-Seite muss wieder aktualisiert werden.

5. – 6. Datensatz wird eingetragen

INSERT INTO dbo.Customers (C1) VALUES ('Test 5');
GO
image
image

Mit einem Füllgrad von 80% sind – rechnerisch – also 8.060 * 0,8 = 6.448 Bytes auf der Datenseite belegt. Tatsächlich sind es aber erst 5.005 Bytes. Bis das Volumen von 6.448 Bytes erreicht ist, kann ein weitere Datensatz eingetragen werden ohne die PFS-Seite zu aktualisieren.

7. Datensatz wird eingetragen

INSERT INTO dbo.Customers (C1) VALUES ('Test 7');
image

Mit der Speicherung des 7. Datensatzes wird die Grenze von 6.448 Bytes erneut erreicht und Microsoft SQL Server aktualisiert die PFS-Seite auf 95%! Damit ist – rechnerisch – kein Platz mehr für einen 8. Datensatz.

image
image

Wenn bereits 95% einer Datenseite mit einem Speichervolumen von 8.060 Bytes belegt sind, passen nur noch 403 Bytes auf die Datenseite. Will man also den 8. Datensatz auf der Datenseite speichern, wird Microsoft SQL Server eine neue Datenseite erstellen und den neuen Datensatz auf dieser Datenseite speichern. Diese Speicherung erfordert erneut eine Aktualisierung der PFS-Seite (50%).

8. Datensatz wird eingetragen

INSERT INTO dbo.Customers (C1) VALUES ('Test 8');
GO
image

Aus dem Transaktionsprotokoll geht hervor, dass Microsoft SQL Server zunächst eine neue Datenseite angelegt hat (Zeile 7 – 10). Anschließend wurde der neue Datensatz auf dieser Seite (Slot 0) abgespeichert. Mit dem folgenden SQL-Statement kann man die Position jedes einzelnen Datensatzes evaluieren.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), * FROM dbo.Customers;
GO
image

Herzlichen Dank fürs Lesen!