SQL Server & BI Blog by Andreas Wolter

(DE)
In einer meiner letzten Master-Classes tauchte die nicht ganz ernste aber dennoch interessante Fragestellung auf:

Wie viele Zeilen passen eigentlich maximal auf eine Datenseite? – wenn die Datensätze/Records so klein wie möglich sind.

Zunächst, Part 1, was ist der kleinstmögliche Datensatz in SQL Server?

Um das zu beurteilen, ist es gut, die Datentypen sowie die Struktur eines Datensatzes genau zu kennen.
Man könnte versucht sein, anzunehmen, dass eine Spalte vom Datentyp bit der kleinstmögliche Datensatz ist. Der erfahrene SQL‘er wird wissen, dass ein bit allein auch immer mindestens 1 byte in einem Record benötigt – erst bei mehreren Spalten dieses Datentyps, kommt ein Platzersparnis ins Spiel (bit (Transact-SQL)).

Der nächste Kandidat sind Datentypen, die laut der Liste in Books Online nur 1 byte Speicherplatz benötigen. Das wären folgende:

(EN)
In one of my last Master classes, a not quite so serious but nevertheless interesting question was brought up:

How many rows maximally do actually fit on a data page? – if the data sets/records are as small as possible.

First of all, part 1, what is the smallest possible data set in SQL Server?

In order to assess this, it is commendable to know exactly the data types as well as the structure of a data set. 

One could be tempted to assume that a column of the data type bit is the smallest possible data set. The experienced SQL people will know that a bit alone always also requires at least 1 byte in one record – only with several columns of this data type, the place-saving aspect comes into play (bit (Transact-SQL)).

The next candidate are data types which according to the list in Books Online use only 1 byte of storage. These would be the following:

Tatsächlich benötigen in diesem besonderen Fall, einer einzigen Spalte je Datensatz, auch Records mit Nettowert von 2 Bytes, 9 Bytes auf der Datenseite:

As a matter of fact, in this particular case of a single column per record, also records with a net value of 2 bytes use 9 bytes on the data page:

Wie kommt das?
Das liegt an der Struktur der Datensätze. Diese ist hinlänglich in diversen Blogs dokumentiert (z.B. hier Inside the Storage Engine: Anatomy of a record und hier: SQL Server Storage Internals 101 ), jedoch eher für allgemeine Zwecke und dieser Sonderfall (eine 1-byte-Spalte) ist dabei weniger im Fokus.

Theoretisch würden für einen Datensatz 8 Bytes ausreichen:

4 Bytes: Datensatzkopf (Typ + Zeiger auf Null-Bitmap)
1 Byte: Daten
2 Bytes: Anzahl der Spalten im Record
1 Byte: NULL Bitmap

Dazu kommt noch der 2 Bytes große Zeiger im Page-Offset, was dann 10 Bytes ergeben würde.
Dennoch belegt ein solcher Datensatz 9 Bytes auf der Seite/Page + Offset.

Woran das liegt, sehen wir uns an.

How come?
This has to do with the structure of the data sets, which is sufficiently documented in various blogs (e.g. here: Inside the Storage Engine: Anatomy of a record and here: SQL Server Storage Internals 101 ), but rather for general purposes, and this special case (a 1-byte-column) is less focused on here.

Theoretically, 8 bytes would be sufficient for a data set:

4 bytes: data set head (type + pointer to NULL-bitmap)
1 byte: data
2 bytes: number of columns in record
1 byte: NULL bitmap

Add to that the 2-bytes-pointer in the page offset, which would then result in 10 bytes.
Despite this, such a data set uses 9 bytes on the page/page + offset.

We will now look at the reason for this.

Im Folgenden definiere ich 2 Tabellen mit jeweils einer bzw. 2 Spalten von Datentyp tinyint, der ja bekanntlich einen Byte benötigt:

In the following, I am defining 2 tables with one and 2 columns each of the data type tinyint, which is known to use 1 byte:

Danach füge ich zuerst Datenätze mit dem Wert „255“ bzw „255, 255“ ein, und danach einige mit Wert „NULL“.

Mit DBCC Page kann man sich den Header der Datenseiten beider Tabellen ansehen, und findet eine kleine Überraschung (Ergebnis reduziert):

Next, I am inserting, first, data set of the value “255” or “255, 255” and then a few of the value “NULL.”

With the DBCC page, one can look at the header of the data pages of both tables, and one will find a small surprise (reduced result). 

Obwohl die Größe der Daten fixer Länge (pminlen) mit 5 bzw. 6 unterschiedlich angegeben wird, ist der freie Speicherplatz auf der Seite (m_freeCnt) identisch! (rot)

Der Datensatz belegt in beiden Fällen jedoch 9 Bytes im Page-body (blau).

So sieht die Tabelle, bestehend aus einer Spalte, mit einigen Datensätzen gefüllt, auf der Festplatte aus:

Even though the size of the fixed-length data (pminlen) is specified differently, with 5 and 6 respectively, the free storage on the page (m_freeCnt) is identical! (red)

The record, however, uses in both cases 9 bytes in the page body. (blue)

This is what the table, consisting of one column, filled up with a few records, looks like on the hard drive:

Man sieht, dass 9 Bytes belegt sind, jedoch nur, da nach der NULL Bitmap noch ein Byte jedem Datensatz anhängig ist (gelb markiert).

Hier die Tabelle mit 2 Spalten:

One can see that 9 bytes are used, but only because after the NULL bitmap, one extra byte is attached to each data set (marked in yellow).

Below, see the table with 2 columns:

Auch hier also 9 Bytes, mit dem Unterschied, wie das letzte Byte verwendet wird.

Noch etwas deutlicher wird es im Binärformat. Das ist die Tabelle mit 2 Spalten – auch diese benötigt 9 Bytes, und man sieht unten, wie die NULL Bitmap zu ihrem Namen kommt:

Here, too, it is 9 bytes, with the difference being how the last byte is used.

It becomes a bit clearer in the binary format.
This is the table with 2 columns –this one uses 9 bytes as well, and you can see below how the NULL bitmap gets its name:

Dieser eine Byte, der für mich keinen klaren Nutzen hat, führt also zu dem Ergebnis, das beide Tabellen letztlich 9 Bytes je Record auf der Festplatte benötigen.

This one byte, which to me does not have any clear purpose, is what leads to the result that both tables ultimately use 9 bytes per record on the hard drive.

Die minimale Datensatzgröße ist daher in der Tat 9 Bytes. Der zweitgrößte Datensatz ist jedoch auch 9 Bytes. :-D

Dabei darf beliebig gewählt werden zwischen 2 Spalten à 1 Byte oder 1 Spalte à 2 Bytes :-)
Daher die Liste:

The minimal record size is thus in fact 9 bytes. The second biggest record, however, is also 9 bytes. :-D

Here, one may freely choose between 2 columns à 1 byte or 1 column à 2 bytes :-).
Hence the list:

Kommen wir nun zu Part 2:

Wie viele Datensätze passen maximal auf eine Datenseite, wenn wir jetzt wissen, dass jeder Datensatz mindestens 9 Bytes + Offset benötigt?

Let us now look at Part 2:

How many records fit maximally on a data page if we now know that every data set requires a minimum of 9 bytes + offset?

Wir testen mit einer Tabelle, bestehend aus einer Spalte mit Datentyp tinyint – wohlwissend, dass es dasselbe Ergebnis bringen würde, wenn wir smallint oder etwas anderes aus der Liste oben nehmen würden.

We are testing with one table of one column with datatype tinyint – knowing full well that it would have the same outcome if we were to take smallint or something else from the list above.

Sehen wir uns an, wie voll die Datenseite geworden ist, und welche Page_ID diese hat:

Then, we will check again how full the data page has become, and which Page_ID it has:

Ausgehend davon, dass eine Datenseite 8192 Bytes groß ist und abzüglich Header 8096 Bytes für Daten zur Verfügung stehen, bedeutet ein Füllgrad von ~95,107%, das noch gut ~396 Bytes zur Verfügung stehen. Durch 11 ergibt das 36. - 36 Datensätze haben also noch Platz!

Was sagt DBCC Page dazu?

Based on the fact that a data page is 8192 bytes in size and that, less the header, 8096 bytes are available for data, a fill degree of ~95,107% means that some ~396 are still available. Divided by 11 this equals 36 – there is still room for 36 records!

What does DBCC Page have to say to this?

Auch hier: 396 Bytes frei – na dann war unsere Überschlagsrechnung gar nicht so schlecht. :-)

Das heißt rein rechnerisch müssten weitere 36 Datensätze auf die Seite passen.
Also „rauf damit“.

396 bytes free – well, then, our back-of-the-envelope calculation wasn’t so bad at all. :-)

That is to say that in purely arithmetical terms, a further 36 records should fit on the page.
So “let’s put them on.”

2 Seiten, direkt ab dem 701. Datensatz.
Was ist da los?
Im Hex Editor betrachten wir „das Grauen“:

2 pages, directly from the 701^st data set.
What is going on there?
In the hex editor, we are looking at “the horror”:

Freier Platz! – Fragmentierung, „Igitt“ ;-)
Was machen wir da?
Ein Rebuild der Tabelle, was sonst.

Free space! – Fragmentation, „yuck“ ;-)
What to do?
A rebuild of the table, what else.

Und schon ist die Seite zu glatten 100% gefüllt: 736 Datensätze.
Und das sagt der Header:

And just like that, the page is filled with a sleek 100%: 736 records.
And this is what the header says:

Ja, die Page_ID ist eine andere – aber nicht, weil ich gemogelt hätte, sondern weil die Storage Engine für den Rebuild neuen Platz reserviert, und den alten nach getaner Arbeit freigegeben hat.

Und auch auf Platte sieht es jetzt so aus, wie es sein sollte – bis zum Ende aufgefüllt:

Yes, the Page_ID is a different one – but not because I might have cheated, but because the storage engine allocated new space for the rebuild, and released the old one after the done work.

On the drive, too, it now looks exactly how it’s supposed to – filled up to the end:

Übrigens: Wenn ich anstelle der Heap-Speicherung der Tabelle einen Clustered Index als Struktur gewählt hätte, wären die Daten in den allermeisten Fällen sofort auf der einen Seite zu 100% abgelegt worden
– aber was tut man nicht alles für die Spannungskurve :-)

Ansonsten gilt auch hier die Regel: „Niemals auf etwas verlassen, was man nicht selbst getestet hat“ ;-)

By the way, had I chosen a Clustered Index as structure instead of the Heap-storage, in most cases, the data would have been stored immediately to a 100% on the one page
– but what’s the fun in that! :-)

Otherwise, the rule also applies here: “Never rely on something you haven’t tested yourself” ;-)

Ergebnis:
Die Antwort auf die Frage lautet daher nicht 700, nicht 732, oder gar 809, sondern: 736 Datensätze passen maximal auf eine Seite.

- und dabei macht es noch nicht einmal einen Unterschied, ob man eine Spalte mit 1 oder 2 Bytes Größe, oder gar 2 Spalten mit je einem Byte Größe verwendet.

Da jeder Datensatz 9 Bytes zzgl. 2 Bytes Record Offset benötigt, haben wir damit die maximale Kapazität einer SQL Server Datenseite mit 8096 Bytes exakt ausgereizt! :-)

- Nicht zu verwechseln mit der maximalen Zeilenlänge von 8060 Bytes.

Result:
The answer to this question, therefore, is not 700, not 732, or even 809, but: a maximum of 736 data records fits on one page.

- and it does not even make a difference, if one uses one column with 1 or 2 bytes in size, or even 2 columns with one byte in size.

Since every data record uses 9 bytes plus 2 bytes record offset we will have exactly exhausted the maximum capacity of an SQL Server data page with 8096 bytes! :-)

- Not to be confused with the maximal row length of 8060 bytes.