Im
Allgemeinen kann man sich merken, dass eine Seite (page), die
Datenbankspeichereinheit, rund 8 KB Speicherplatz für unsere Daten hat. Wenn
man mit einem ungefähren Wert zufrieden ist, dann passt das so. Wer es aber ein
bisschen genauer wissen möchte, ist irgendwann schon auf die Zahl 8060 Byte
gestoßen.
Wenn man
dann 8192 – 8060 (Daten) – 96 (header) rechnet, bleiben einem
verwirrende 36 Byte übrig. Das liegt daran, dass diese Rechnung falsch ist.
Fälschlicherweise
wird oft davon ausgegangen, dass wir maximal 8060 Byte zum Abspeichern unserer
Daten auf einer Seite (page) zur Verfügung hätten – aber wenn wir es
ganz genau nehmen, ist das nicht korrekt.
8060 Byte
ist die Anzahl an Byte, die eine Zeile (ein Datensatz) maximal haben darf. Auf eine Seite
passen tatsächlich noch ein paar wenige Byte mehr.
Sehen wir
uns die Seite einmal genauer an:
Insgesamt
sind es 8192 Byte; davon entfallen 96 Byte fix auf den page header. Jede
Zeile erzeugt 7 Byte overhead und 2 Byte row offset. Bei einem
einzelnen Datensatz hätten wir also theoretisch 8087 Byte für Daten zur
Verfügung – der darf aber maximal 8060 Byte haben, 27 Byte würden in diesem
Fall einfach leer bleiben.
Theoretisch
können wir eine Page aber zu 100% befüllen; wenn wir mindestens zwei Datensätze
haben.
8192 – 96 (page
header) – 4 (2 x 2 row offset) – 14 (2 x 7 overhead) = 8078
Bei zwei
Datensätzen dürften wir insgesamt 8078 Byte für Daten auf einer einzelnen Page
verwenden. Bei mehr Datensätzen wird es um jeweils 9 Byte (2 row offset
+ 7 overhead) pro Zeile weniger.
Die 8060
Byte würden also theoretisch mit 4 Datensätzen zu jeweils 2015 Byte stimmen,
wenn wir eine Seite zu 100% befüllen wollen.
8060 (2015 x 4 Daten) + 8 (4 x 2 row offset) + 28
(4 x 7 overhead) + 96 (page header) = 8192
Wenn wir
mehr als 4 Datensätze haben, bleibt für die Daten entsprechen weniger Platz.
In der
Theorie würde die Rechnung bei 100 Datensätzen, die auf eine Seite passen
sollen, so aussehen:
8192 – 200 (100 x 2 row offset) – 700 (100 x 7 overhead) – 96 (page
header) = 7196
Wir hätten
also 7196 Byte für Daten zur Verfügung. Das würde bedeuten, jeder unserer
Datensätze dürfte noch 71 Byte lang sein; 96 Byte würden freibleiben. Wären
unsere Datensätze länger als 71 Byte, würden keine 100 davon mehr auf eine
Seite passen.
(In der
Praxis würde die Datenbank allerdings ab einer Befüllung von 81% entscheiden,
es ist Zeit für eine neue Seite, es sei denn, man arbeitet mit Clustered Index
und fillfactor oder verwendet ein einziges INSERT statement.)
Meistens
wäre für uns die umgekehrte Überlegung interessanter: Mein Datensatz ist n Byte
lang, wieviele davon passen auf eine Seite?
Die
unerklärlichen übrigbleibenden 36 Byte aus der ersten Rechnung ergeben sich aus
einer falschen Annahme. Fix reserviert sind nur die 96 Byte für den header,
2 Byte row offset pro Zeile und 7 Byte overhead pro Zeile.
Genug Theorie – sehen wir uns das Ganze mit Datenbankabfragen an:
Test: Passen tatsächlich mehr als 8060 Byte an Daten auf eine Seite?
Laut der
Rechnung von oben haben wir maximal 8078 Byte zur Verfügung:
8192 – 96 (page header) –
4 (2 x 2 row offset) – 14 (2 x 7 overhead) = 8078
Wir
erstellen eine Testdatenbank:
CREATE DATABASE PageTestDB
USE PageTestDB
Wir brauchen auch eine Tabelle.
Wir wissen bereits, dass ein Datensatz
(eine Zeile) maximal 8060 Byte haben darf; um die 8078 Byte nützen zu können,
brauchen wir also zwei Datensätze zu je 4039 Byte (8078 / 2).
CREATE TABLE Test0(Testtext char(4039))
INSERT INTO Test0 (Testtext)
VALUES
('ABC'), ('DEF')
Zwar haben
wir jeweils nur 3 Zeichen eingefügt, aber durch die Verwendung des Datentyps
char werden die nicht verbrauchten Byte mit Leerzeichen aufgefüllt.
Jetzt müssen
wir die PageID herausfinden:
DBCC TRACEON(3604)
DBCC IND('PageTestDB', 'Test0', -1)
Wir sehen, dass 1 Datapage mit
der ID 264 erstellt worden ist:
Bei der Page
mit der PagePID 80 handelt es sich um eine IAM-Page (Index Allocation Map); wir
sehen bei IAMPID dass unsere Daten-Seite dieser IAM-Page zugeordnet ist. Die
für uns interessante Seite ist die mit der ID 264.
Jetzt sehen
wir uns im Header an, wieviele Einträge auf unserer Seite sind und wie viel
Speicherplatz noch frei ist – wenn wir insgesamt nur 8060 Byte belegen dürften,
müsste ja der zweite Eintrag auf einer neuen Seite abgespeichert sein.
DBCC PAGE('PageTestDB', 1, 264)
Zwei Informationen
aus dem Header sind für uns interessant:
m_slotCnt
= 2
m_freeCnt
= 0
m_slotCnt sagt uns, wie viele Einträge auf der
Seite sind (es sind tatsächlich beide unserer Einträge hier abgespeichert) und m_freeCnt
sagt uns, wie viele Byte noch frei sind (keine).
Auch die
Auslastung der Seite in Prozent können wir noch abfragen, wenn wir es noch
nicht glauben wollen:
DBCC SHOWCONTIG('Test0')
Auch mit diesem Befehl können
wir feststellen, zu wieviel Prozent unsere Seite beschrieben ist und wie viel
freier Speicherplatz noch besteht:
Theoretisch
haben wir auf einer Seite (page) also 8078 Byte für unsere Daten zur
Verfügung. In der Realität ist es aber tatsächlich weniger. Obwohl ein
Datensatz in den meisten Fällen deutlich weniger als 4039 Byte beansprucht,
wird der Platz, den wir zur Verfügung haben, pro Zeile um 9 Byte kleiner – denn
pro Zeile fallen 2 Byte row offset und 7 Byte overhead an.
Viel Spaß
beim Austesten (ich weiß doch, dass man so etwas selbst probieren muss)!
Vielleicht
sehen wir uns ja in einem unserer Kurse zum Thema SQL!