Datenspeicherung in SQL Server-Datenseiten und Extends

Eine Datenbank ist eine Sammlung von Daten, die wiederum in Tabellen organisiert sind. Diese Daten werden physisch in den zur Datenbank gehörenden Datenbankdateien gespeichert.

Sämtliche Änderungen an den Daten einer Datenbank werden im Transaktionsprotokoll protokolliert (aufgezeichnet). Eine Datenbank kann theoretisch aus bis zu 32767 Dateien bestehen.

Die eigentlichen Daten, werden in SQL Server in Datenseiten mit einer Größe von 8KB verwaltet.

Eine sogenannte Datenseite beinhaltet einen 96Byte großen Page Header der wichtige Informationen über die Inhalte der Seite enthält. Anschließend kommt der Data Storage Space gefolgt von der Row Offset Tabelle.

Extends

Datenseiten sind zusätzlich in sogenannten Extends organisiert. Ein Extend besteht aus jeweils 8 Datenseiten. Es gibt zwei Arten von Extends die in der folgenden Tabelle erläutert werden.

	Bezeichnung	Beschreibung
	Mixed Extend	Jede im Extend zugeordnete Datenseite kann zu einem anderen Datenbankobjekt (Index etc.) gehören.
	Uniform Extend	Alle im Extend enthaltenen Datenseiten gehören zum selben Datenbankobjekt

 

Normalerweise ist das Wissen darüber, dass eine Datenseite 8KB groß ist, für die Administration und den Betrieb von SQL Server unerheblich. Trotzdem könnte es Situationen geben, wo dieses Wissen hilfreich ist.

Machen Sie doch einmal folgendes Experiment.

Geben Sie in Ihrer Testdatenbank folgende Befehle zum Anlegen einer Tabelle in SSMS (SQL Server Management Studio) ein.

create table testtab1 (spalte1 char(8000));

create table testtab2 (spalte1 char(4000),spalte2 char(4100));

Der erste Befehl legt erfolgreich die Tabelle testtab1 an.

Der zweite Befehl schlägt mit folgender Fehlermeldung fehl:

Creating or altering table ‚testtab2‘ failed because the minimum row size would be 8107, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Das ist ja eigentlich merkwürdig, da ein char Datentyp einer Spalte ja bis zu 8000 Zeichen lang sein kann.  Befehl 1 beweist dies ja, da testtab1 ohne Probleme angelegt werden konnte.

Was ist passiert? Warum können dann keine zwei Spalten angelegt werden die jeweils weit unter der 8000 Zeichen Grenze liegen?

Da eine Datenseite 8KB also 8000Bytes groß ist und die Tabellendaten in einer Datenseite gespeichert werden, können auch nicht mehr Daten für eine Tabelle definiert werden.

Der char Datentyp belegt ja immer die feste Spaltengröße,  die bei der Definition der jeweiligen Spalte angegeben wurde. Selbst wenn Sie eine Spalte mit char(4000) anlegen, Sie aber nur eine Zeichenkette mit 10 Zeichen darin abspeichern. Die restlichen 3990 Zeichen werden aufgefüllt. Es werden 4000 Bytes pro Spalte belegt.

SQL Server verhindert das Anlegen einer Tabelle, wo von vornherein klar ist, dass dieses Limit gesprengt wird. Dies ist bei der zweiten Anweisung zwei der Fall.

Die erste Spalte belegt grundsätzlich 4000 Bytes und die zweite Spalte würde 4100 Bytes benötigen und dies sind insgesamt mehr Bytes als 8KB nämlich 8100Bytes insgesamt. Nichts anderes, sagt auch die Fehlermeldung hierzu aus.

Wer anmerkt, dass ja wohl eher selten in einer Tabelle char Spalten mit 4000er Länge angelegt werden hat natürlich recht. Jedoch ist es eventuell für Datenbankdesigner wichtig, diese Grenze zu kennen, da der gleiche Effekt auftritt, wenn man z.B. 81 Spalten a char(100) Zeichen anlegt. Das Ergebnis dürfte das gleiche sein. Es wird ein Fehler ausgegeben, da hier ebenfalls ein Datensatz nicht in eine Datenseite passen würde.

 

Das würde ja bedeuten, dass es nicht möglich ist einen Datensatz anzulegen, der mehr als 8000 Bytes hat? Zum Glück ist dies nicht so.

Führen Sie im Management Studio die nachfolgende Anweisung aus.

create table testtab3 (spalte1 varchar(4000),spalte2 varchar(4100));

Diese Anweisung erzeugt keinen Fehler. Die Tabelle testtab3 wird angelegt.

Wenn die Spalten mit dem Datentyp varchar angelegt werden, ist es möglich die Tabelle zu erzeugen. Eine varchar Spalte belegt nur soviel Platz, wie wirklich in ihr abgespeichert wird. Eine Zeichenkette von 10 Zeichen belegt auch nur 10 Byte. Demzufolge genehmigt SQL Server das Anlegen dieser Tabelle. Mit ein bisschen Glück, sind alle Datensätze unter der 8KB Begrenzung.

Was aber wenn nicht?

Was passiert also wenn in Spalte1 4000 Zeichen gespeichert werden und in Spalte2 4100 Zeichen. Eine Datenseite würde nicht mehr ausreichen um die Daten aufzunehmen.

Dann werden die Daten die in dieser Spalte gespeichert sind in die sogenannten ROW_OVERFLOW Zuordnungseinheit (Allocation Unit) gespeichert.

Bildlich gesehen, um es sich besser vorstellen zu können, werden die Daten in einem anderen Bereich (ROW_OVERFLOW) abgelegt und in der zugehörigen Spalte wird ein Zeiger, auf die Position der Daten gespeichert. Somit ist zwar immer noch die Grenze der 8KB Datenseite vorhanden aber sie wird quasi umgangen.

Was hat das jetzt mit Datenbankoptimierung oder Datenbank Performance zu tun? 

Es ist auf jeden Fall gut diese Grenze zu kennen selbst wenn das nachfolgende Szenario  eher selten vorkommen dürfte.

Wenn Sie eine wirklich große Tabelle mit vielen Datensätzen haben könnte es passieren, das Sie mit einem Update Befehl über eine größere Menge an Datensätzen eventuell an diese Grenze stoßen bzw. die Daten über die 8KB Begrenzung laufen. SQL Server muss die betroffen Daten jetzt in den oben angegebenen ROW_OVERFLOW Bereich umspeichern. Das könnte Zeit kosten.

 

Zuordnungseinheiten (Allocation Unit’s)

Eine Zuordnungseinheit, ist ein Organisationsbereich von Datenseiten in einer Tabelle.  Zuordnungseinheiten werden verwendet, um Daten zu verwalten die einen speziellen Seitentyp benötigen. Also Daten die in der Tabelle gespeichert werden müssen, jedoch nicht ohne weiteres in die 8KB Seitenstruktur passen. Gemeint sind hier konkret zum Beispiel Binäre Daten oder eben Daten die die Größe einer Datenseite sprengen würden.

Es gibt in einer Tabelle genau drei Zuordnungseinheiten.

	Bezeichnung	Beschreibung
	IN_ROW_DATA	In diesem Bereich sind alle „normalen“ Daten gespeichert. Der Name sagt es ja schon. Alle Daten die gewöhnlicherweise in  eine Datenzeile passen.
	ROW_OVERFLOW_DATA	Ist ein Bereich in dem Text und Binärdaten gespeichert werden die die Datenzeile zum „überlaufen“ bringen. Zum Beispiel, wenn Daten von Datenzeilen mit Spalten Variabler Länge gespeichert werden müssen deren Gesamtgröße 8KB übersteigt. Dies wurde in dem Beispiel weiter oben erläutert.
	LOB_DATA (Large Object Data)	Daten der Datentypen die mit max Angabe definiert werden. Also varbinary(max),varchar(max),nvarchar(max) und demnach ebenfalls nicht in die 8KB Struktur einer Datenseite passen.

 

Also fassen wir zusammen.

Die Daten der SQL Tabellen einer Datenbank werden in Datenseiten von 8KB Größe gespeichert, die wiederum in sogenannten Extend‘s zusammengefasst sind.  Die Größe von 8KB stellt das Limit für eine Datenseite dar. Wenn dieses Limit überstiegen wird, läuft die Datenseite bildlich gesehen über. Diese Daten „übergelaufenen“ Daten werden in Zuordnungseinheiten unterschiedlichen Typ‘s gespeichert.

In der folgenden Demo wird Ihnen gezeigt, wie Sie eine Datenseite analysieren können.

Demo:

Anzeigen einer Datenseite.

1. Öffnen Sie SSMS und legen Sie eine Datenbank demo an.
2. Legen Sie mit der nachfolgenden Anweisung die Tabelle zeigeSeite an.

— lege Tabelle in Datenbank demo an.

CREATE TABLE zeigeSeite(ID int identity(1,1) not null,Daten varchar(100))

 

3. Führen Sie die nachfolgende Insertanweisung aus um den Datensatz mit den Daten ‚Robert Bossofski‘ hinzuzufügen.

 

— schreibe Testdaten in die Tabelle

insert into zeigeSeite (daten) values(‚Robert Bossofski‘)

 

4. Führen Sie die nachfolgende Select Anweisung aus um mit Hilfe der Dynamic Management View dm_db_database_page_allocations die zur Tabelle zeigeSeite zugehörigen Datenseiten anzuzeigen.

 

— Suche die Datenseiten zu dieser Tabelle

select * from sys.dm_db_database_page_allocations(db_id(‚demo‘),
object_id(‚zeigeSeite‘),NULL,NULL,’DETAILED‘) where page_type = 1;

 

5. Merken Sie sich in der Ausgabe den Wert der Spalte allocated_page_page_id (in diesem Beispiel 384)

 

6. Setzen das Traceflag 3604 durch Ausführen der nachfolgenden Anweisung. Das Traceflag weist SQL Server an, die Ausgabe von Anweisung 7. direkt an den Client zu senden und dort anzuzeigen (also Management Studio).

DBCC TRACEON(3604);

 

7. Setzen Sie den gemerkten Wert aus allocated_page_page_id in die DBCCPAGE Anweisung (dritter Parameter) ein und führen Sie diese aus um den Inhalt der Datenseite anzuzeigen.

 

— gemerkte Page ID einsetzen in dbcc page

— Zeige die Inhalte der Datenseite 384 an

dbcc page(‚demo‘,1,384,3);

 

 

8. Betrachten Sie den Inhalt der Ausgabe und suchen Sie den Namen Robert Bossofski.

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNSRecord Size = 31

Memory Dump @0x000000635899A0600000000000000000:   30000800 01000000 02000001 001f0052 6f626572  0…………..Rober0000000000000014:   7420426f 73736f66 736b69                      t Bossofski

 

---

Brauchen Sie noch einen Xpertenrat?

---