Untersuchung, wie das RDBMS des SQL Servers Seiten für eine
neue Tabelle allokiert.
Von einem geschätzten Kollegen wurde an mich die Frage
gestellt, warum der SQL Server in einer neuen Datenbank für die erste Tabelle
die Seite x (x >70) allokiert und nicht z.B. die Seite 1,2,3 usw.
Das die ersten 8 Seiten durch die speziellen Steuerseiten
besetzt sind ( DB-Header, PFS,GAM,SGAM usw.) war auch ihm gekannt, allerdings
war hier der exakte Ablauf gefragt.Die Seitenzuweisung muss im laufenden Betrieb schnell durchzuführen sein. Daher erfolgt die Allokierung von Seiten durch ein Zusammenspiel von PFS, GAM, SGAM und IAM Seiten.
Dazu wird eine Datenbank auf höchst simple Art und Weise
erstellt:
create database db_index
Zuerst mal ein Blick auf die GAM und SGAM Einträge einer
neuen Datenbank direkt nach der Erstellung:
GAM (Global Allocation Map)–Einträge:
Mittels des Befehls DBCC PAGE bekommt man einen Überblick (Der
voranstehende DBCC TRACEON (3604) Befehl leitet nur die Ausgabe auf das SQL
SERVER Management Studio um):
DBCC TRACEON (3604)
GO
DBCC PAGE (db_index, 1, 2, 3);
Hier wird nun nur der relevante Teil angezeigt:
Dabei bedeutet ‚ALLOCATED‘ = BIT 0 (Die Seite ist zugewiesen zur Benutzung)
‚NOT ALLOCATED‘ = BIT 1 (Die Seite kann zur Benutzung zugewiesen werden)
SGAM (Shared
Global Allocation Map) –Einträge:
DBCC PAGE (db_index, 1, 3, 3);
Hier wird nun nur der relevante Teil angezeigt:
Dabei bedeutet
‚ALLOCATED‘ = BIT 1 (Gemischter Block mit mindestens 1 freier Page)
‚NOT ALLOCATED‘ = BIT 0 (Vollständig besetzter gemischter Block ode rein dedizierter Block)
‚ALLOCATED‘ = BIT 1 (Gemischter Block mit mindestens 1 freier Page)
‚NOT ALLOCATED‘ = BIT 0 (Vollständig besetzter gemischter Block ode rein dedizierter Block)
Seiten
|
GAM-BIT
|
SGAM-BIT
|
1:000 - 1:111
|
0
|
0
|
1:112- 1:127
|
0
|
1
|
1:128 – 1:135
|
0
|
0
|
1:136 – 1:183
|
0
|
1
|
Bei Microsoft gibt es hierzu folgende Tabelle:
Aktuelle Blockverwendung
|
GAM-Biteinstellung
|
SGAM-Biteinstellung
|
Frei, wird nicht verwendet
|
1
|
0
|
Einheitlicher Block oder vollständig belegter
gemischter Block
|
0
|
0
|
Gemischter Block mit freien Seiten
|
0
|
1
|
Quelle: http://technet.microsoft.com/de-de/library/ms175195(v=sql.105).aspx
Das bedeutet, dass für die erste Tabelle (aus GAM und SGAM
Sicht) Seiten ab Seite 1:112 benutzt werden können.
Leider ist das nicht die ganze Wahrheit. Zur Allokierung
werden auch noch die IAM Seiten herangezogen.
Schauen wir hierzu erstmal in die PFS:
DBCC PAGE (db_index, 1, 1, 3);
Auch hier nur der Ausschnitt aus dem relevanten Teil ab
Eintrag für Seite 1:109:
So sehen wir, dass die ersten verfügbaren Blöcke die Seiten 1:112
bis 1:119 und 1:120 bis 1:127 umfasst. Genauer gesagt die Seiten 1:119,1:120,
1:121, 1:126, 1:127.
Hierbei sind die Seiten 1:119, 1:121 und 1:127 als IAM Seiten vormarkiert .
Hierbei sind die Seiten 1:119, 1:121 und 1:127 als IAM Seiten vormarkiert .
Der Inhalt dieser Seiten mittels
DBCC Page (1,xxx,3)
xxx = Nummer der entsprechenden Seite
zeigt folgendes Ergebnis:
DBCC Page (1,xxx,3)
xxx = Nummer der entsprechenden Seite
zeigt folgendes Ergebnis:
Nun wird die neue Tabelle erstellt und ein Datensatz
importiert.
CREATE
TABLE [dbo].[tbl_Kunde](
[KundenNr] [char](400) 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)
INSERT INTO tbl_Kunde VALUES
('1001', 'Gabi', 'Schmidt', 'Spielweg 9', '40444', 'Düsseldorf', '0211/968596')
Welche Seiten hat der SQL Server gewählt :
SELECT
sys.fn_PhysLocFormatter(%%physloc%%) AS Location, * FROM dbo.tbl_Kunde;Hier fällt also die Wahl auf die Seite 1:121.
Die IAM Seite wird mittels DBCC IND aufgelistet:
dbcc ind ('db_index','tbl_Kunde',-1)
Also ist die dazugehörige IAM Seite die Seite 1:126.
Frage: Was ist auf den Seiten 1:119 und 1:120 geschehen?
Die Abfrage der Seite 1:119 mittels
DBCC PAGE (db_index, 1, 119, 1);ergibt:
Diese Seite gehört nun zum Index der Systemtabelle ‚sysschobjs‘ (ObjectID = 34).Jede Zeile dieser Tabelle stellt ein Objekt in der Datenbank
dar. Offenbar wurde diese Seite beim Erstellen der Metadaten der neuen Tabelle
miterzeugt.
Die Abfrage der Seite 1:120 mittels
DBCC PAGE (db_index, 1, 120, 1);ergibt:
Diese Seite gehört nun zum Index der Systemtabelle ‚
syscolpars‘ (ObjectID = 41). Diese Systemtabelle enthält eine Zeile für
jede Spalte in einer Tabelle. Da auch diese Metadaten erweitert wurden, wurden
diese hier abgelegt.
ERGO:Die erste freie Seite war nun die Seite 1:121! Diese Seite wird nun für die erste Seite der Tabelle verwendet. Die darauffolgende nächste freie Seite 1:126 wird dann für die dazugehörige IAM Seite verwendet.
Ich danke Ihnen für ihr Interesse.