Allokation von Datenseiten – Schritt für Schritt

Erneut eine interessante Fragestellung eines von mir sehr geschätzten Kollegen Johannes Curio (e | w). Beim experimentieren wurde eine neue Tabelle mit einem Datensatz erstellt. Bei der Überprüfung der alloziierten Datenseite kam die Frage auf, warum Microsoft SQL Server ausgerechnet Datenseite X und nicht Y für die Allokation verwendet. Ehrlich gesagt habe ich mir darüber noch nie Gedanken gemacht und fand es höchst interessant, dieser Fragestellung einmal nachzugehen.

Ein Blick in die Interna einer Datenbankdatei

Um zu verstehen, wie Microsoft SQL Server die Daten einer Datenbank verwaltet, müssen ein paar Grundlagen zu internen Strukturen bekannt sein. Damit eine Datenbank Tabellen, Indexe, Views, etc. speichern kann, verwendet Microsoft SQL Server Datenseiten (Pages) mit einer festen Größe von 8.192 Bytes. In jeder Datenbankdatei werden diese Pages durchgehend von 0 – x nummeriert. Der Datenspeicher wird in Extents organisiert. Ein Extent besteht immer aus 8 logisch hintereinander folgenden Datenseiten.

Das Speichermodell von Microsoft SQL Server hat sich in den letzten Jahren nicht deutlich weiter entwickelt und so wurde zu Zeiten, als Storage noch teuer war, die Speicherbelegung in einer Datenbank “optimiert”, indem man beim Anlegen von neuen Tabellen nicht sofort 64 KByte (ein Extent) für das anzulegende Objekt verwendete sondern es wurden mehre Objekte in einem Extent gespeichert (http://technet.microsoft.com/de-de/library/ms190969.aspx). Sobald ein Objekt mehr als 8 Datenseiten füllt, wird bei der weiteren Allokation ein vollständiges Extent (64 KByte) für die Tabelle reserviert. Diese beiden “Extent-Typen” unterscheidet man als:

  • uniform extent
  • mixed extent

Ein uniform extent alloziiert immer den Inhalt EINES Objekts während ein mixed extent Daten verschiedener Objekte speichern kann!

Mixed vs uniform extent

Die Abbildung zeigt die Belegung von Datenseiten durch zwei verschiedene Objekte ([dbo].[A] und [dbo].[B]). Microsoft SQL Server alloziiert für ein Objekt zunächst eine Datenseite in einem “Mixed” Extent. Erst, wenn 8 Datenseiten durch ein Objekt belegt sind, alloziiert Microsoft SQL Server ein “Uniform” Extent, das exklusiv für das Objekt [dbo].[A] reserviert ist. Die ersten 8 Datenseiten in jeder Datenbankdatei sind fest reserviert für Systeminformationen zur Datenbank selbst. Von diesen 8 Datenseiten sind 3 Datenseiten ein wichtiger Bestandteil für die Zuweisung von Speicherbereich für Objekte:

PFS = Page Free Space

Die PFS verwaltet 8.088 Datenseiten (64 MB) in einer Datenbankdatei. Ist die Datenbank größer als 64 MB, wird für die nächsten 64 MB erneut eine PFS Seite angelegt. Man spricht in diesem Fall von PFS-Intervallen. Die PFS verwaltet Datenseiten in einer Bytemap, die neben dem verfügbaren Speicherplatz Informationen über die Verwendung der Datenseite bereit hält. Berücksichtigt man den Umstand, dass eine Datenseite 8.192 Bytes groß ist, wird schnell klar, warum die Anzahl der zu verwaltenden Datenseiten pro PFS begrenzt ist.

GAM = Global Allocation Map

Microsoft SQL Server verwaltet den Speicher in 8 zusammenhängenden Datenseiten (Extents). Ob ein Extent belegt ist, wird in der GAM durch ein Bitmap verwaltet. Jedes Bit von insgesamt 8.000 Bytes repräsentiert jeweils 1 Extent. Insgesamt können so über eine GAM-Seite 64.000 Extents verwaltet werden (4 GB). Die erste GAM ist immer die 2. Datenseite in einer Datenbankdatei. Es ist wichtig, zu wissen, dass in der GAM JEDES Extent (unabhängig ob Uniform oder Mixed) verwaltet wird! Je nach Wert eines BIT gilt das Extent als belegt (alloziiert) oder frei:

  • 1 = frei. Das Extent ist für eine Allokierung verfügbar
  • 0 = belegt. Das Extent ist bereits in Benutzung (entweder als Uniform oder Mixed Extent)

SGAM = Shared Global Allocation Map

Die SGAM ist strukturell identisch mit der GAM. Auch die SGAM verwaltet einen Datenraum von 4 GB (64.000 Extents). Die erste SGAM ist immer die 3. Datenseite in einer Datenbankdatei. Das Bitmap der SGAM ist etwas komplizierter als das der GAM, da der Wert nur in Verbindung mit dem Bitmap der GAM auszuwerten ist.

  • 1 = das Extent ist ein “Mixed” Extent mit mindestens einer (von 8) nicht alloziierten Datenseite
  • 0 = das Extent ist entweder ein “uniform” Extent oder aber ein “Mixed” Extent, in dem alle Datenseiten allokiert sind

    Bitmuster GAM SGAM

Die Abbildung zeigt die möglichen Kombinationen von GAM und SGAM. Die Kombination beider Bitmuster geschieht in Microsoft SQL Server sehr effizient und einen Alloziierung von Datenseiten kann schnell durchgeführt werden.

Das Beispiel erzeugt eine leere Datenbank und liest anschließend den Inhalt von GAM und SGAM Datenseiten aus.

CREATE DATABASE demo_db;

GO

 

USE demo_db;

GO

 

-- Ausgabe von Informationen in SSMS

DBCC TRACEON (3604);

 

-- Ausgabe von GAM (immer Seite 2)

DBCC PAGE (demo_db, 1, 2, 3);

 

-- Ausgabe von SGAM (immer Seite 3)

DBCC PAGE (demo_db, 1, 3, 3);

Das Resultat für die GAM-Datenseite stellt sich nach der Erstellung der Datenbank wie folgt dar:

GAM: Extent Alloc Status @0x000000001C2CA0C2

 

(1:0)        - (1:208)      =     ALLOCATED

(1:216)      -              = NOT ALLOCATED

(1:224)      -              =     ALLOCATED

(1:232)      -              = NOT ALLOCATED

(1:240)      -              =     ALLOCATED

(1:248)      - (1:256)      = NOT ALLOCATED

(1:264)      - (1:296)      =     ALLOCATED

Aktuell sind in der – neu erstellten – Datenbank die Datenseiten von 0 – 215 (27 Extents) allokiert. Das bedeutet jedoch nicht, dass diese Datenseiten auch belegt sind. Sie können von Objekten belegt werden. In Verbindung mit dem Auszug aus der SGAM-Seite wird erkennbar, wo Datenseiten zu Verfügung stehen:

SGAM: Extent Alloc Status @0x000000001466A0C2

 

(1:0)        - (1:80)       = NOT ALLOCATED

(1:88)       -              =     ALLOCATED

(1:96)       - (1:104)      = NOT ALLOCATED

(1:112)      - (1:120)      =     ALLOCATED

(1:128)      -              = NOT ALLOCATED

(1:136)      - (1:176)      =     ALLOCATED

(1:184)      - (1:256)      = NOT ALLOCATED

Der Ausdruck “not allocated” ist etwas verwirrend; er bedeutet nicht, dass die Datenseiten als Mixed Extent zur Verfügung stehen! Besinnt man sich auf den Zustand eines Extent im Bitmuster, steht “not allocated” für den Wert 0! Um das Ergebnis zu interpretieren, sollen die erste Zeile (Datenseiten 0 – 208) als Beispiel verwendet werden:

Grundsätzlich sind die Datenseiten von 0 – 215 alloziiert und stehen der Datenbank als Speichermedium zur Verfügung. Innerhalb dieser alloziierten Datenseiten sind die Datenseiten 0 – 87 bereits vollständig belegt (11 Extents). Es ist jedoch auf den ersten Blick nicht erkennbar, ob sie als Uniform Extent oder Mixed Extent allokiert wurden. Der Wert 0 (NOT ALLOCATED) besagt, dass es sich entweder um ein Uniform Extent handelt oder aber um ein Mixed Extent, in dem keine freien Datenseiten mehr zur Verfügung stehen (es ist voll). Das nächste freie Extent beginnt bei Datenseite 88 und erstreckt sich bis Datenseite 95 (exakt 8 Datenseiten). Die Extents von Datenseite 96– 111 wiederum sind vollständig belegt. Ein Auszug der PFS (Page Free Space) zeigt, dass in dem erstgenannten Extent (88 – 95) lediglich eine IAM-Datenseite (Index Allocation Map) zur Verfügung steht.

-- Ausgabe von PFS (immer Seite 1)

DBCC PAGE (demo_db, 1, 1, 3);

(1:88)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:89)       - (1:91)       =     ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:92)       -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:93)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:94)       -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:95)       -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext

Das erstgenannte Extent fällt für die Allokation von Datenseiten aus. Der zweite angegebene Datenbereich (Datenseite 112 – 127) jedoch besitzt ausreichend freie Datenseiten, um eine neue Tabelle anlegen zu können:

(1:109)      - (1:113)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:114)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:115)      - (1:116)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:117)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:118)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:119)      -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:120)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:121)      -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:122)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:123)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:124)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:125)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

(1:126)      -              = NOT ALLOCATED   0_PCT_FULL                     Mixed Ext

(1:127)      -              = NOT ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

Analyse der Objektanlage

Nach so viel Theorie die Praxis. Die Tabelle wird mit dem nachfolgenden Script angelegt und mit einem Datensatz befüllt. Es ist wichtig, dass mindestens ein Datensatz in die Tabelle eingetragen wird,da ansonsten die Alloziierung der Datenseiten nicht durchgeführt wird! Genau dieser Umstand muss bei der Analyse des belegten Speichers berücksichtigt werden. Wenn ein Objekt in Microsoft SQL Server neu angelegt wird, werden die Datenseiten für die Speicherung von Datensätzen nicht sofort physikalisch alloziiert! Erst mit der Speicherung des ersten Datensatzes werden die Datenseiten von Microsoft SQL Server alloziiert!

Basierend auf dem Ergebnis der PFS-Analyse sollten für das neu anzulegende Objekt mit einem neuen Datensatz die Datenseiten 118 und 119 verwendet werden können; eine Datenseite muss für die IAM-Seite alloziert werden, während eine weitere Datenseite für die Speicherung der Daten alloziiert wird. Um die Ergebnisse im Anschluss besser erläutern zu können, werden sowohl die Erstellung des Objekts als auch die Speicherung eines Datensatzes in einer separaten Transaktion ausgeführt. Damit lassen sich die Log-Operationen besser herausfiltern!

-- Anlegen der Tabelle [dbo].{tbl_Kunde]

BEGIN TRANSACTION CreateTable;

 

CREATE TABLE [dbo].[tbl_Kunde]

(

    [KundenNr] [char](340) NULL,

    [Vorname]  [char](100) NULL,

    [Nachname] [char](100) NULL,

    [Strasse]  [char](100) NULL,

    [PLZ]      [char](100) NULL,

    [Ort]      [char](100) NULL,

    [Telefon]  [char](100) NULL

);

 

COMMIT TRANSACTION CreateTable;

GO

 

-- Speicherung eines Datensatzes in der Tabelle [dbo].[tbl_Kunde]

BEGIN TRANSACTION InsertData;

 

INSERT INTO tbl_Kunde VALUES

('1001', 'Gabi', 'Schmidt', 'Spielweg 9', '40444', 'Düsseldorf', '0211/968596')

 

COMMIT TRANSACTION InsertData;

GO

Sobald die Tabelle angelegt wurde, kann die Position des neu hinzugefügten Datensatzes mit Hilfe einer Systemfunktion sichtbar gemacht werden:

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.tbl_Kunde;

SELECT_RESULT_01

Obwohl Datenseite 118 ausdrücklich als frei markiert war, wurde diese Datenseite “übersprungen”. Dieses Verhalten legt augenscheinlich die Vermutung nahe, dass Microsoft SQL Server willkürlich die Allokierung von Datenseiten vornimmt; dem ist aber nicht so, wenn man sich die Transaktionsprotokolle beider Aktionen etwas genauer anschaut!

Transaktion – Tabelle erstellen

Wenn ein neues Objekt in der Datenbank erstellt wird, werden dessen Metadaten wie normale Datensätze in den entsprechenden Systemtabellen gespeichert. Diese Systemtabellen sind ebenfalls in Datenseiten organisiert und unterliegen den gleichen Bedingungen für die Allokation wie Benutzerobjekte. Die Transaktion für das Anlegen der Tabelle [dbo].[tbl_Kunden] wurde unter dem Namen “CreateTable” protokolliert.

SELECT  Operation, Context, AllocUnitId, AllocUnitName, [Page ID], [Slot ID]

FROM    sys.fn_dblog(NULL, NULL)

WHERE   [Transaction ID]

IN

(

    SELECT  [Transaction ID]

    FROM    sys.fn_dblog(NULL, NULL)

    WHERE   [Transaction Name] = 'CreateTable'

);

fn_dblog_01

Im Attribut [Page Id] ist gekennzeichnet, welche Datenseite (Hex) bearbeitet wird. Die Datenseite 0x76 ist die Datenseite 118! Das bedeutet für die gesamte Transaktion, dass VOR dem Einfügen eines neuen Datensatzes zunächst die Metadaten des Benutzerobjekts gespeichert werden müssen. Da dieser Speichervorgang den gleichen Bedingungen unterlegt, wie die Speicherung eines Datensatzes in einer Benutzertabelle, wurde die Datenseite 118 bereits durch bei Erstellung des Benutzerobjekts allokiert.

Transaktion – Datensatz eintragen

Wenn eine Tabelle neu erstellt wird, resultiert daraus nicht automatisch die Allokation neuer Datenseiten. Die Datenseiten werden erst in dem Moment alloziiert, wenn ein Datensatz in die Tabelle eingetragen wird – sie wird also “manifestiert” mit dem ersten Datensatz! Das belegt auch das Transaktionsprotokoll des Vorgangs.

fn_dblog_02

Man kann sehr gut erkennen, dass VOR dem Eintragen des Datensatzes (Zeile 104) ein paar “Vorarbeiten” durchgeführt wurden. Zunächst wurde die IAM (Index Allocation Map) erstellt. Die IAM befindet sich auf Datenseite 120 (0x78). Nachdem die IAM alloziiert wurde, konnte die Datenseite 119 (0x77) alloziiert werden, um den Datensatz zu speichern.

Zusammenfassung

Microsoft SQL Server “scannt” die GAM in Verbindung mit der SGAM, um freie Datenseiten für neue Objekte zu allokieren. Dabei geht Microsoft SQL Server sehr effizient vor, da für die Suche nur die beiden Systemseiten verwendet werden müssen.

Wichtig für das Verständnis der Reihenfolge der Allokation von Datenseiten sind zwei Dinge.

  • Metadaten von Benutzerobjekten werden in den gleichen Typen von Datenseiten gespeichert wie gewöhnliche Datenseiten.
  • Wenn eine neue Benutzertabelle erstellt wird, wird der dazu benötigte Speicher erst in dem Moment alloziiert, wenn zum ersten Mal ein Datensatz in die Tabelle eingetragen wird. Aus Effizienzgründen werden neue Tabellen nicht vorher erstellt sondern nur die Metadaten in den Systemtabellen gespeichert.

Hinweis:

Mit einem Kollegen (Herr Maic Beher) habe ich die Fragestellung von Johannes gemeinsam untersucht. Auch er beginnt nun das Bloggen und hat einen ähnlichen Artikel zu “unserer” Arbeit dazu HIER geschrieben!

Herzlichen Dank fürs Lesen!

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s