Größe einer DB: erwartet vs. real

28. Februar 2011

Das Schöne an der SDX-Community ist, dass man bei Problemen immer sehr kompetente Kollegen fragen kann, die auch meistens eine Antwort wissen und weiterhelfen können. So auch in diesem Fall…

Das Problem

Zur Schätzung der Größen-Entwicklung einer MS SQL Datenbank wollte ich die Größe einer Tabelle ermitteln. Zunächst habe ich manuell die erwartete Größe errechnet. Eine Zeile nimmt anhand ihrer Definition maximal 298 Bytes ein, die Anzahl der Datensätze beträgt 2.853.506 Sätze. Das macht eine erwartete Größe von maximal ~811 MB.

Anschließend habe ich diese erwartete Größe mit der tatsächlichen Größe abgeglichen. Diese beträgt laut sp_spaceused bzw. laut Report ~1784 MB im Bereich “data” (also ohne Indizes und reservierten freien Speicher, sondern wirklich die Größe der benutzten Daten). Mit einem kleineren Unterschied hätte ich gerechnet, aber gleich das Doppelte?

Zu Testzwecken habe ich eine Tabelle identischer Struktur angelegt und die Daten per INSERT aus der alten Tabelle übernommen. Und schau an: die Größe dieser neuen Tabellen beträgt bei gleichen Daten ~794 MB, was unterhalb des errechneten Maximums liegt.

Für mich war das unschlüssig und ich dachte, ich hätte etwas übersehen, daher habe ich die Frage über unser firmeninternes Portal mal an meine SDX-Kollegen weitergereicht. Und die Antworten ließen wie immer nicht lange auf sich warten…

Erste Antwort, erste Hinweise

Die folgende Antwort kam von einem Kollegen, die zwar noch nicht des Rätsels Lösung war, mir aber erste wertvolle Hinweise lieferte:

1) Wichtig ist die Page-Size: 8000 Byte pro Page. Du hast also einen “Verschnitt”, da Zeilen einer DB immer vollständig in eine Page rein passen müssen (außer bei “image”- und “text”-Feldern).

2) Page-Fill-Factor: Deine Zeilen sind anhand des PK sortiert. Damit Inserts in der Mitte der Tabelle nicht immer zu einem Page-Split führen, gibt es einen Fill-Factor, welcher angibt, wie viel Platz für zukünftige Zeilen innerhalb der Seiten zu reservieren ist. Wenn der auf 50% steht, verbraucht die Tabelle bei Initial-Befüllung 200% des errechneten Platzes.

3) Rechenfehler – du musst die physikalische Menge Bytes beachten – die kann bei einem BIT z.B. zwischen 1 Bit und 1 Byte liegen … ja nachdem, wie viele Bit-Felder Du hast. Nullable-Types brauchen ebenfalls mehr Speicherplatz.

Die zweite Antwort

Ein anderer Kollege lieferte mir dann weitere wichtige Informationen…

Auf Deiner Originaltabelle liegt höchstwahrscheinlich ein Clustered Index. Der “Data”-Bereich besteht hier aus den Leaf-Nodes des Clustered Index Trees. Selbst wenn der gewünschte Fillfactor auf 100% steht (das ist der normale Default-Wert), kann es durch den Clustered Index zu Fragmentierung kommen.

Das Verhältnis von fast 1:2 spricht für einen sehr hoch fragmentierten Index, der z.B. entsteht, wenn man einen Clustered Index nicht in der Sortierreihenfolge befüllt oder die Sortierreihenfolge durch Updates und Deletes geändert wird. Der Füllgrad liegt dann durch dauernde Page-Splits nur knapp über 50%.

Die Fragmentierung kannst Du u.a. im Management Studio über die Eigenschaften des Index abfragen. Relevant in Deinem Fall ist dann der Clustered Index. Ich würde hier einen Wert zwischen 90 und 100% erwarten. Die neue Tabelle ist deshalb kleiner, weil Du sie wahrscheinlich ohne Index angelegt hast. Sie ist also ein Heap, der nur die Daten speichert (unsortiert).

Des Rätsels Lösung

Die zweite Antwort lieferte den entscheidenden Hinweis. Auf der Tabelle existierte ein Clustered Index mit den folgenden Eigenschaften:

Page Fullness: 98,37%
Total fragmentation: 55,20%
Depth: 4
Leaf-level rows: 2852100
Pages: 101641

Nach einem Rebuild des Index hatte dann auch die Originaltabelle mit ihren ursprünglichen 1784 MB dann eine Größe von 813 MB. Ich hätte nicht gedacht, dass dies einen so großen Unterschied machen kann. Hier die Eigenschaften nach dem Rebuild:

Page Fullness: 98,26%
Total fragmentation: 0,02%
Depth: 4
Leaf-level rows: 2856700
Pages: 101648

Oder doch nicht?

Mein direktes Problem war damit gelöst, doch meine Kollegen wollten sich damit nicht zufrieden geben, da die Ursache noch etwas unklar war. Also folgten eine weitere Antwort und die Detailsuche:

Hm. Jetzt bin ich verwirrt. An irgendeiner Stelle in Deinen Daten muss der SQL-Server gelogen haben.
Bei den Pages sollte er sich eigentlich nicht verzählen. 101000 pages á 8192 Bytes macht ca. 827 MB. Der Unterschied in der Anzahl Pages vor und nach dem Rebuild ist marginal, also hat sich auch am effektiven Speicherverbrauch nichts geändert (siehe auch Page Fullness, die ist praktisch gleich geblieben).

Die Fragmentierung bezieht sich nur darauf, dass die physische Reihenfolge der Index-Nodes nicht mit der logischen übereinstimmt. In Bezug auf den Speicherverbrauch ist das irrelevant.
Da wie gesagt der Page-Count nicht lügen sollte, scheint mir dann die ursprüngliche Anzeige von 1784 MB der zweifelhafte Wert zu sein. Auf diese Zahl würde man eigentlich nur mit einer Page Fullness von in Deinem Fall unter 50% kommen, was aber durch die Anzahl Pages nicht validiert wird.
Offenbar geht in die Berechnung des Speicherverbrauchs noch ein anderer Wert ein. Denkbar wären hier durch die Fragmentierung entstandene “verwaiste” Pages und Extents, die zwar logisch leer sind, aber durch ihre ungünstige Lage in der Datendatei zunächst nicht wieder befüllt wurden. Gut möglich, dass so etwas in die Berechnung eingeht.

Gib mir doch bitte mal den Output der sp_spaceused und das Ergebnis von

SELECT * FROM sys.dm_db_index_physical_stats
(
    DB_ID('<DbName>'),
    OBJECT_ID('<Tabellenname>'),
    NULL,
    NULL,
    'DETAILED'
)

Problem erkannt, Problem gebannt!

Ich führte die Abfrage aus und sendete sie meinem Kollegen, der sie mit folgender Antwort korrekt interpretieren konnte:

Mit den abgefragten Daten ist mein Vertrauen in den SQL-Server wieder hergestellt 🙂

Das Ergebnis der sys.dm_db_index_physical_stats liefert für den Clustered Index (also den Datenbereich) 273742 pages. Eine Page hat 8192 Bytes, macht 2,2 GB, was mit der Angabe aus der sp_spaceused übereinstimmt.

Auch meine Vermutung über Fragmentierung und Füllgrad bestätigt sich, denn auf Ebene der Leaf-Nodes (Index-Level 0, dort sind die Datenrecords enthalten) beträgt die Fragmentierung über 90% mit einer durchschnittlichen Fragmentgröße von nur 1,09 pages und einem Füllgrad von nur 44 Prozent.

Mehr als die Hälfte des Platzes in den zugeordneten Pages ist also ungenutzt, was die hohe Speicherallokation erklärt. Die Fragmentierung von 90% auf der Datenebene und 99% auf der darüberliegenden Indexebene erklärt die Ursache, nämlich dauernde Page-Splits durch Inserts und Updates außerhalb der „natürlichen“ Sortierreihenfolge des Clustered Index. Um bei einer Tabelle mit Soll-Füllgrad 0 (= Default = 100%) auf unter 50% Füllgrad zu kommen, muss man noch ein paar Deletes einstreuen, aber auch das geht.

Fazit

Das ganze Thema hat mich 2 Dinge gelehrt:

1.) Der SQL Server ist ein mächtiges, aber auch komplexes Werk, bei dem es ab und an nötig ist, auch komplexe interne Arbeitsweisen zu verstehen, um Verhalten nachvollziehen zu können.

2.) Gut, dass es in der SDX reichlich eXperts gibt, die man bei solchen komplexen Problemen um Rat fragen kann!