Wie legt der SQL-Server die Daten physisch ab wie sieht die Datenstruktur aus?

Veröffentlicht am Veröffentlicht in SQL, SQL Server Internals Keine Kommentare Tagged with , , , , , , , , , , , , , , , ,
Wie legt der SQL-Server die Daten physisch ab wie sieht die Datenstruktur aus?
Bild von Pixabay

Mich hat es schon immer interessiert, wie der SQL-Server seine Daten physisch ablegt. Als ich dann auf die nicht dokumentierte Funktion  DBCC IND und DBCC page gestoßen bin, hab ich das gleich mal ausprobiert und die Datenstruktur analysiert.

Als erstes lege ich eine leere Datenbank und einen kleinen Table mit 4 Sätzen an

use master;
go
create database SQL_Internals_1;

use SQL_Internals_1;
go
create table dbo.test (
  id smallint identity(1,1) not null
, ch char(5) not null
, vch varchar(50) null
, tint tinyint null
, bint bigint null
);
insert into dbo.test
select 'abcde','fghijklmnopqrst', 1, 2 Union all
select 'fghij','klmnopqrstuv', 255, -999999999999999999 union all
select 'fghij',null, null, -1 union all
select 'fghij','', null,null

select * from dbo.test

Jetzt kommen wir zum interessanteren. Mit Hilfe des DBCC IND Befehls ermittle ich mir, wo die Daten liegen.

 

DBCC IND ('SQL_Internals_1','dbo.test',-1)

Output von DBCC IND ('SQL_Internals_1','dbo.test',-1)

für uns interessant PageType = 1. Wen es interessiert hier noch die unterschiedlichen Typen (soweit ich sie rausbekommen hab)

1 – data page
2 – index page
3 + 4 – text pages
8 – GAM page
9 – SGAM page
10 – IAM page
11 – PFS page

Die Daten stehen in der PageID 224 im File 1. Das kann man sich jetzt mit DBCC page genauer ansehen – vorher müssen wir aber den Output auf die Konsole umleiten:

DBCC TRACEON (3604); --- Ausgabe umleiten auf Console
DBCC PAGE  ('SQL_Internals_1',1,224,3) with no_infomsgs

 

OUTPUT:

PAGE: (1:224)

BUFFER:

BUF @0x000001D0E2450A80

bpage = 0x000001D065BAE000 bhash = 0x0000000000000000 bpageno = (1:224)
bdbid = 39 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 13203 bstat = 0x10b
blog = 0xccdb21cc bnext = 0x0000000000000000 bDirtyContext = 0x000001D0DA3626B0
bstat2 = 0x0

PAGE HEADER:

Page @0x000001D065BAE000

m_pageId = (1:224) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 177 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594049527808
Metadata: PartitionId = 72057594043105280 Metadata: IndexId = 0
Metadata: ObjectId = 901578250 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 20 m_slotCnt = 4 m_freeCnt = 7961
m_freeData = 223 m_reservedCnt = 0 m_lsn = (36:200:32)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = 0 DB Frag ID = 1

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED PFS (1:1) = 0x41 ALLOCATED 50_PCT_FULL
DIFF (1:6) = CHANGED ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 42

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 42
Memory Dump @0x00000086430FA060

0000000000000000: 30001400 01006162 63646501 02000000 00000000 0…..abcde………
0000000000000014: 05000001 002a0066 6768696a 6b6c6d6e 6f707172 …..*.fghijklmnopqr
0000000000000028: 7374 st

Slot 0 Column 1 Offset 0x4 Length 2 Length (physical) 2

id = 1

Slot 0 Column 2 Offset 0x6 Length 5 Length (physical) 5

ch = abcde

Slot 0 Column 3 Offset 0x1b Length 15 Length (physical) 15

vch = fghijklmnopqrst

Slot 0 Column 4 Offset 0xb Length 1 Length (physical) 1

tint = 1

Slot 0 Column 5 Offset 0xc Length 8 Length (physical) 8

bint = 2

Slot 1 Offset 0x8a Length 39

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 39
Memory Dump @0x00000086430FA08A

0000000000000000: 30001400 02006667 68696aff 01009c58 4c491ff2 0…..fghijÿ..œXLI.ò
0000000000000014: 05000001 0027006b 6c6d6e6f 70717273 747576 …..‘.klmnopqrstuv

Slot 1 Column 1 Offset 0x4 Length 2 Length (physical) 2

id = 2

Slot 1 Column 2 Offset 0x6 Length 5 Length (physical) 5

ch = fghij

Slot 1 Column 3 Offset 0x1b Length 12 Length (physical) 12

vch = klmnopqrstuv

Slot 1 Column 4 Offset 0xb Length 1 Length (physical) 1

tint = 255

Slot 1 Column 5 Offset 0xc Length 8 Length (physical) 8

bint = -999999999999999999

Slot 2 Offset 0xb1 Length 23

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 23

Memory Dump @0x00000086430FA0B1

0000000000000000: 10001400 03006667 68696aff ffffffff ffffffff ……fghijÿÿÿÿÿÿÿÿÿ
0000000000000014: 05000c …

Slot 2 Column 1 Offset 0x4 Length 2 Length (physical) 2

id = 3

Slot 2 Column 2 Offset 0x6 Length 5 Length (physical) 5

ch = fghij

Slot 2 Column 3 Offset 0x0 Length 0 Length (physical) 0

vch = [NULL]

Slot 2 Column 4 Offset 0x0 Length 0 Length (physical) 0

tint = [NULL]

Slot 2 Column 5 Offset 0xc Length 8 Length (physical) 8

bint = -1

Slot 3 Offset 0xc8 Length 23

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 23

Memory Dump @0x00000086430FA0C8

0000000000000000: 10001400 04006667 68696aff ffffffff ffffffff ……fghijÿÿÿÿÿÿÿÿÿ
0000000000000014: 050018 …

Slot 3 Column 1 Offset 0x4 Length 2 Length (physical) 2

id = 4

Slot 3 Column 2 Offset 0x6 Length 5 Length (physical) 5

ch = fghij

Slot 3 Column 3 Offset 0x0 Length 0 Length (physical) 0

vch =

Slot 3 Column 4 Offset 0x0 Length 0 Length (physical) 0

tint = [NULL]

Slot 3 Column 5 Offset 0x0 Length 0 Length (physical) 0

bint = [NULL]

Ganz schön viel Information! Aber das für mich wirklich interessante – und ich muss gestehen nicht alles was da steht hat einen Sinn für mich (leider) – hab ich hier nochmal rauskopiert:

0000000000000000: 30001400 01006162 63646501 02000000 00000000 0…..abcde………
0000000000000014: 05000001 002a0066 6768696a 6b6c6d6e 6f707172 …..*.fghijklmnopqr
0000000000000028: 7374                                         st

0000000000000000: 30001400 02006667 68696aff 01009c58 4c491ff2 0…..fghijÿ…XLI.ò
0000000000000014: 05000001 0027006b 6c6d6e6f 70717273 747576   …..‘.klmnopqrstuv

0000000000000000: 10001400 03006667 68696aff ffffffff ffffffff ……fghijÿÿÿÿÿÿÿÿÿ
0000000000000014: 05000c                                       …

0000000000000000: 10001400 04006667 68696aff ffffffff ffffffff ……fghijÿÿÿÿÿÿÿÿÿ
0000000000000014: 050018                                       …

… was man sofort sieht, die Datensätze brauchen nicht alle gleich viel Platz – was den aufmerksamen Leser nicht überraschen wird.
Weiters kann man auch gleich die Länge des Datensatzes ablesen (Record Size=),
ob Null-fähige Spalten vorhanden sind, oder nicht (NULL_BITMAP)
und ob Variabel lange Felder vorhanden sind (VARIABLE_COLUMNS) –> siehe Datensatz 3 und 4!!! – da scheinen keine solchen Felder vorhanden zu sein – komisch, die Tabelle hat ja die Spalte „vch“ als varchar(50) definiert. – doch zu diesem Phänomen komme ich später noch zurück.

Aber was bedeutet das jetzt alles?“

Byte-Position (dezimal)

Länge (decimal)

Inhalt (hexadecimal)

Bedeutung
Datensatz 1 (Record Size = 42)
30001400 01006162 63646501 02000000 00000000 05000001 002a0066 6768696a 6b6c6d6e 6f707172 7374
0 1 30 Status Bit A
1 1 00 Status Bit B
2 2 1400 Offset zu Anzahl Spalten
SQL-Server speichert die Werte in „Littel-Endian“ Schreibweise – also das 1. Byte ist das niederwertige.
Aus 1400 wird 0x0014 -> dezimal: 20
somit findet sich auf Pos 20 die Information: Anzahl Spalten
4  16
(smallint -> 2
char(5) -> 5
tinyint -> 1
bigint -> 8)		 01006162 63646501 02000000 00000000 Fixed Data Length Spalten (in der Reihenfolge lt. Create)
id 0x0001 -> 1
ch 0x6162636465 -> abcde
tint 0x01
bint 0x0000000000000002
20 2 0500 Anzahl Spalten
wieder 0x0050 -> dezimal: 5
(id,ch,vch,tint,bint)
22 1 (je nach Bedarf auch länger!)
jedes Bit repräsentiert eine Spalte		 00 NULL Bitmap
23 2  01 00 Anzahl von Variable Data Length Spalten
0x0001 -> dezimal: 1
(vch)
25 je Spalte mit variabler Länge 2 2a00 Offset zum Ende von Variable Data Length Spalte 1 (… n)
0x002a -> dezimal: 42
27 15 66 6768696a 6b6c6d6e 6f707172 7374 Daten von Variable Data Length Spalte 1
66 -> f, 67 -> g, …. 74 -> t
Datensatz 2 (Record Size = 39)
30001400 02006667 68696aff 01009c58 4c491ff2 05000001 0027006b 6c6d6e6f 70717273 747576
0 1  30 Status Bit A
1 1  00 Status Bit B
2 2  1400 Offset zu Anzahl Spalten
4 16
(smallint -> 2
char(5) -> 5
tinyint -> 1
bigint -> 8)		  02006667 68696aff 01009c58 4c491ff2 Fixed Data Length Spalten
20 2 0500 Anzahl Spalten
22 1 (je nach Bedarf auch länger!) 00 NULL Bitmap
23 2 0100 Anzahl Spalten Variable Data Length
25 je Spalte mit variabler Länge 2 2700 Offset zum Ende von Variable Data Length Spalte 1
27 12 6b 6c6d6e6f 70717273 747576 Daten von Variable Data Lenght Spalte 1
Datensatz 3 (Record Size = 23)
0001400 03006667 68696aff ffffffff ffffffff 05000c
0 1 10 Status Bit A
1 1 00 Status Bit B
2 2 1400 Offset zu Anzahl Spalten
4 16
(smallint -> 2
char(5) -> 5
tinyint -> 1
bigint -> 8)		 03006667 68696aff ffffffff ffffffff Fixed Data Length Spalten
20 2 0500 Anzahl Spalten
22 1 0c NULL Bitmap
0x0c -> binär: 00001100
-> umdrehen
00110000
Feld 1: NOT NULL
Feld 2: NOT NULL
Feld 3: NULL
Feld 4: NULL
Feld 5: NOT NULL
restliche Bit auf 1 Byte aufgefüllt.
Datensatz 4 (Record Size = 23)
10001400 04006667 68696aff ffffffff ffffffff 050018
0 1 10 Status Bit A
1 1 00 Status Bit B
2 2 1400 Offset zu Anzahl Spalten
4 16
(smallint -> 2
char(5) -> 5
tinyint -> 1
bigint -> 8)		 04006667 68696aff ffffffff ffffffff Fixed Data Length Spalten
20 2 0500 Anzahl Spalten
22 1 18 NULL Bitmap
0x18 -> binär: 00011000
-> umdrehen
00011000
Feld 1: NOT NULL
Feld 2: NOT NULL
Feld 3: NOT NULL
Feld 4: NULL
Feld 5: NULL

Hat man einmal die Logik dahinter verstanden, dann ist Datensatz 1 + 2 nichts besonderes mehr. Interessant wird es bei Datensatz 3 und 4

hier sieht man, dass wenn die „Variable Length“ Felder Null sind oder eine Länge=0 haben, dann spart sich der SQL Server sogar das ablegen der Offsets.
(Anmerkung: das trifft aber nur für die hinteren Felder zu! Wären z.B. die Variablen Felder NULL, NULL, ‚abc‘, dann würden für alle 3 die Offsets (somit 6 Byte) benötigt.)

Was bedeutet das nun?

  • Felder variabler Länge die potenziell NULL sind, sollten an das Ende der Tabelle gestellt werden.
  • man benötigt für JEDE Spalte (auch NOT NULL) ein Bit in der NULL Bitmap
  • Variabel lange Felder benötigen (im Normalfall) +2 Byte für die Längeninformation (… aber das ist sicher nichts neues)

 

 

Im nächsten Artikel zum Thema SQL-Server Internals werde ich dann versuchen die einzelnen Datentypen zu zerlegen. Bin schon gespannt, wie z.B. ein Date abgelegt wird.

 

Links, die mir geholfen haben:

Inside the Storage Engine: Using DBCC PAGE and DBCC IND to find out if page splits ever roll back

More undocumented fun: DBCC IND, DBCC PAGE, and off-row columns

http://strictlysql.blogspot.co.at/2010/08/dbcc-ind-dbcc-page-intro.html?m=1

DBCC IND and DBCC PAGE

 

 

 

 

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

*

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.