Où est mon enregistrement ?

Publié le par

Voici un petit article présentant une fonction non documentée qui permet de localiser l’endroit où est stockée une ligne donnée. Cela peut notamment permettre de situer les autres lignes présentes dans la même page, et qui pourraient subir les effets d’un verrou de la page lors d’une modification.

Pour rappel, les données sont stockées par SQL Server sous forme de pages de 8Ko. Cette taille est fixe; elle permet de comprendre certaines limites au niveau des types de données (varchar(8000), nvarchar(4000), …), en sachant qu’un enregistrement ne peut pas être à cheval sur plusieurs pages (hormis pour certains types qui peuvent être stockés en dehors de la ligne elle-même).

A partir de SQL Server 2008, une nouvelle colonne cachée est disponible pour permettre de situer la page dans laquelle se trouve chacun des enregistrements retournés.

select *,%%physloc%% as [%%physloc%%] from HumanResources.Department

Bon, c’est vrai, vu comme ça, ce n’est pas très parlant…

Les 4 premiers octets correspondent à l’identifiant de page, puis 2 octets pour l’identification du fichier, et enfin les deux derniers octets pour le slot, ce qui correspond en gros au numéro d’enregistrement dans la page.

Heureusement pour décrypter cela, nous avons aussi droit à une magnifique fonction qui nous permet d’avoir un affichage beaucoup plus lisible de la chose.

select *,sys.fn_PhysLocFormatter(%%physloc%%) as [Emplacement] from HumanResources.Department

Nous constatons ici que les divers enregistrement se situent tous sur la page 665 du premier fichier de ma base. Allons donc y jeter un oeil :

dbcc page (8,1,665,1)
PAGE: (1:665)
BUFFER:
BUF @0x0000000085FDBE80bpage = 0x00000000859FC000 bhash = 0x0000000000000000 bpageno = (1:665)
bdbid = 8 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 16708 bstat = 0xc00009
blog = 0x3212159 bnext = 0x0000000000000000PAGE HEADER:
Page @0x00000000859FC000m_pageId = (1:665) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x220
m_objId (AllocUnitId.idObj) = 135 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594046775296
Metadata: PartitionId = 72057594044612608 Metadata: IndexId = 1
Metadata: ObjectId = 757577737 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 14 m_slotCnt = 16 m_freeCnt = 6456
m_freeData = 1704 m_reservedCnt = 0 m_lsn = (41:2945:22)
m_xactReserved = 0 m_xdesId = (0:1194) m_ghostRecCnt = 0
m_tornBits = 263213638Allocation StatusGAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x60 MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

DATA:
Slot 0, Offset 0x60, Length 93, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 93
Memory Dump @0x000000000CE3A060

0000000000000000: 30000e00 01000000 00001e92 00000400 †0……….’….
0000000000000010: 0002002d 005d0045 006e0067 0069006e †…-.].E.n.g.i.n
0000000000000020: 00650065 00720069 006e0067 00520065 †.e.e.r.i.n.g.R.e
0000000000000030: 00730065 00610072 00630068 00200061 †.s.e.a.r.c.h. .a
0000000000000040: 006e0064 00200044 00650076 0065006c †.n.d. .D.e.v.e.l
0000000000000050: 006f0070 006d0065 006e0074 00††††††††.o.p.m.e.n.t.

Slot 1, Offset 0xbd, Length 93, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 93
Memory Dump @0x000000000CE3A0BD

0000000000000000: 30000e00 02000000 00001e92 00000400 †0……….’….
0000000000000010: 0002002d 005d0054 006f006f 006c0020 †…-.].T.o.o.l.
0000000000000020: 00440065 00730069 0067006e 00520065 †.D.e.s.i.g.n.R.e
0000000000000030: 00730065 00610072 00630068 00200061 †.s.e.a.r.c.h. .a
0000000000000040: 006e0064 00200044 00650076 0065006c †.n.d. .D.e.v.e.l
0000000000000050: 006f0070 006d0065 006e0074 00††††††††.o.p.m.e.n.t.

Slot 2, Offset 0x11a, Length 71, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 71
Memory Dump @0x000000000CE3A11A

0000000000000000: 30000e00 03000000 00001e92 00000400 †0……….’….
0000000000000010: 00020021 00470053 0061006c 00650073 †…!.G.S.a.l.e.s
0000000000000020: 00530061 006c0065 00730020 0061006e †.S.a.l.e.s. .a.n
0000000000000030: 00640020 004d0061 0072006b 00650074 †.d. .M.a.r.k.e.t
0000000000000040: 0069006e 006700††††††††††††††††††††††.i.n.g.

Slot 3, Offset 0x161, Length 79, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 79
Memory Dump @0x000000000CE3A161

0000000000000000: 30000e00 04000000 00001e92 00000400 †0……….’….
0000000000000010: 00020029 004f004d 00610072 006b0065 †…).O.M.a.r.k.e
0000000000000020: 00740069 006e0067 00530061 006c0065 †.t.i.n.g.S.a.l.e
0000000000000030: 00730020 0061006e 00640020 004d0061 †.s. .a.n.d. .M.a
0000000000000040: 0072006b 00650074 0069006e 006700††††.r.k.e.t.i.n.g.

Slot 4, Offset 0x1b0, Length 83, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 83
Memory Dump @0x000000000CE3A1B0

0000000000000000: 30000e00 05000000 00001e92 00000400 †0……….’….
0000000000000010: 0002002b 00530050 00750072 00630068 †…+.S.P.u.r.c.h
0000000000000020: 00610073 0069006e 00670049 006e0076 †.a.s.i.n.g.I.n.v
0000000000000030: 0065006e 0074006f 00720079 0020004d †.e.n.t.o.r.y. .M
0000000000000040: 0061006e 00610067 0065006d 0065006e †.a.n.a.g.e.m.e.n
0000000000000050: 007400†††††††††††††††††††††††††††††††.t.

Slot 5, Offset 0x203, Length 119, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 119
Memory Dump @0x000000000CE3A203

0000000000000000: 30000e00 06000000 00001e92 00000400 †0……….’….
0000000000000010: 00020047 00770052 00650073 00650061 †…G.w.R.e.s.e.a
0000000000000020: 00720063 00680020 0061006e 00640020 †.r.c.h. .a.n.d.
0000000000000030: 00440065 00760065 006c006f 0070006d †.D.e.v.e.l.o.p.m
0000000000000040: 0065006e 00740052 00650073 00650061 †.e.n.t.R.e.s.e.a
0000000000000050: 00720063 00680020 0061006e 00640020 †.r.c.h. .a.n.d.
0000000000000060: 00440065 00760065 006c006f 0070006d †.D.e.v.e.l.o.p.m
0000000000000070: 0065006e 007400††††††††††††††††††††††.e.n.t.

Slot 6, Offset 0x27a, Length 69, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 69
Memory Dump @0x000000000CE3A27A

0000000000000000: 30000e00 07000000 00001e92 00000400 †0……….’….
0000000000000010: 0002002b 00450050 0072006f 00640075 †…+.E.P.r.o.d.u
0000000000000020: 00630074 0069006f 006e004d 0061006e †.c.t.i.o.n.M.a.n
0000000000000030: 00750066 00610063 00740075 00720069 †.u.f.a.c.t.u.r.i
0000000000000040: 006e0067 00††††††††††††††††††††††††††.n.g.

Slot 7, Offset 0x2bf, Length 85, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 85
Memory Dump @0x000000000CE3A2BF

0000000000000000: 30000e00 08000000 00001e92 00000400 †0……….’….
0000000000000010: 0002003b 00550050 0072006f 00640075 †…;.U.P.r.o.d.u
0000000000000020: 00630074 0069006f 006e0020 0043006f †.c.t.i.o.n. .C.o
0000000000000030: 006e0074 0072006f 006c004d 0061006e †.n.t.r.o.l.M.a.n
0000000000000040: 00750066 00610063 00740075 00720069 †.u.f.a.c.t.u.r.i
0000000000000050: 006e0067 00††††††††††††††††††††††††††.n.g.

Slot 8, Offset 0x314, Length 125, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 125
Memory Dump @0x000000000CE3A314

0000000000000000: 30000e00 09000000 00001e92 00000400 †0… ……’….
0000000000000010: 00020035 007d0048 0075006d 0061006e †…5.}.H.u.m.a.n
0000000000000020: 00200052 00650073 006f0075 00720063 †. .R.e.s.o.u.r.c
0000000000000030: 00650073 00450078 00650063 00750074 †.e.s.E.x.e.c.u.t
0000000000000040: 00690076 00650020 00470065 006e0065 †.i.v.e. .G.e.n.e
0000000000000050: 00720061 006c0020 0061006e 00640020 †.r.a.l. .a.n.d.
0000000000000060: 00410064 006d0069 006e0069 00730074 †.A.d.m.i.n.i.s.t
0000000000000070: 00720061 00740069 006f006e 00††††††††.r.a.t.i.o.n.

Slot 9, Offset 0x391, Length 109, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 109
Memory Dump @0x000000000CE3A391

0000000000000000: 30000e00 0a000000 00001e92 00000400 †0……….’….
0000000000000010: 00020025 006d0046 0069006e 0061006e †…%.m.F.i.n.a.n
0000000000000020: 00630065 00450078 00650063 00750074 †.c.e.E.x.e.c.u.t
0000000000000030: 00690076 00650020 00470065 006e0065 †.i.v.e. .G.e.n.e
0000000000000040: 00720061 006c0020 0061006e 00640020 †.r.a.l. .a.n.d.
0000000000000050: 00410064 006d0069 006e0069 00730074 †.A.d.m.i.n.i.s.t
0000000000000060: 00720061 00740069 006f006e 00††††††††.r.a.t.i.o.n.

Slot 10, Offset 0x3fe, Length 135, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 135
Memory Dump @0x000000000CE3A3FE

0000000000000000: 30000e00 0b000000 00001e92 00000400 †0……….’….
0000000000000010: 0002003f 00870049 006e0066 006f0072 †…?.‡.I.n.f.o.r
0000000000000020: 006d0061 00740069 006f006e 00200053 †.m.a.t.i.o.n. .S
0000000000000030: 00650072 00760069 00630065 00730045 †.e.r.v.i.c.e.s.E
0000000000000040: 00780065 00630075 00740069 00760065 †.x.e.c.u.t.i.v.e
0000000000000050: 00200047 0065006e 00650072 0061006c †. .G.e.n.e.r.a.l
0000000000000060: 00200061 006e0064 00200041 0064006d †. .a.n.d. .A.d.m
0000000000000070: 0069006e 00690073 00740072 00610074 †.i.n.i.s.t.r.a.t
0000000000000080: 0069006f 006e00††††††††††††††††††††††.i.o.n.

Slot 11, Offset 0x485, Length 89, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 89
Memory Dump @0x000000000CE3A485

0000000000000000: 30000e00 0c000000 00001e92 00000400 †0……….’….
0000000000000010: 00020037 00590044 006f0063 0075006d †…7.Y.D.o.c.u.m
0000000000000020: 0065006e 00740020 0043006f 006e0074 †.e.n.t. .C.o.n.t
0000000000000030: 0072006f 006c0051 00750061 006c0069 †.r.o.l.Q.u.a.l.i
0000000000000040: 00740079 00200041 00730073 00750072 †.t.y. .A.s.s.u.r
0000000000000050: 0061006e 00630065 00†††††††††††††††††.a.n.c.e.

Slot 12, Offset 0x4de, Length 91, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 91
Memory Dump @0x000000000CE3A4DE

0000000000000000: 30000e00 0d000000 00001e92 00000400 †0……….’….
0000000000000010: 00020039 005b0051 00750061 006c0069 †…9.[.Q.u.a.l.i
0000000000000020: 00740079 00200041 00730073 00750072 †.t.y. .A.s.s.u.r
0000000000000030: 0061006e 00630065 00510075 0061006c †.a.n.c.e.Q.u.a.l
0000000000000040: 00690074 00790020 00410073 00730075 †.i.t.y. .A.s.s.u
0000000000000050: 00720061 006e0063 006500†††††††††††††.r.a.n.c.e.

Slot 13, Offset 0x539, Length 147, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 147
Memory Dump @0x000000000CE3A539

0000000000000000: 30000e00 0e000000 00001e92 00000400 †0……….’….
0000000000000010: 0002004b 00930046 00610063 0069006c †…K.“.F.a.c.i.l
0000000000000020: 00690074 00690065 00730020 0061006e †.i.t.i.e.s. .a.n
0000000000000030: 00640020 004d0061 0069006e 00740065 †.d. .M.a.i.n.t.e
0000000000000040: 006e0061 006e0063 00650045 00780065 †.n.a.n.c.e.E.x.e
0000000000000050: 00630075 00740069 00760065 00200047 †.c.u.t.i.v.e. .G
0000000000000060: 0065006e 00650072 0061006c 00200061 †.e.n.e.r.a.l. .a
0000000000000070: 006e0064 00200041 0064006d 0069006e †.n.d. .A.d.m.i.n
0000000000000080: 00690073 00740072 00610074 0069006f †.i.s.t.r.a.t.i.o
0000000000000090: 006e00†††††††††††††††††††††††††††††††.n.

Slot 14, Offset 0x5cc, Length 107, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 107
Memory Dump @0x000000000CE3A5CC

0000000000000000: 30000e00 0f000000 00001e92 00000400 †0……….’….
0000000000000010: 00020043 006b0053 00680069 00700070 †…C.k.S.h.i.p.p
0000000000000020: 0069006e 00670020 0061006e 00640020 †.i.n.g. .a.n.d.
0000000000000030: 00520065 00630065 00690076 0069006e †.R.e.c.e.i.v.i.n
0000000000000040: 00670049 006e0076 0065006e 0074006f †.g.I.n.v.e.n.t.o
0000000000000050: 00720079 0020004d 0061006e 00610067 †.r.y. .M.a.n.a.g
0000000000000060: 0065006d 0065006e 007400†††††††††††††.e.m.e.n.t.

Slot 15, Offset 0x637, Length 113, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 113
Memory Dump @0x000000000CE3A637

0000000000000000: 30000e00 10000000 00001e92 00000400 †0……….’….
0000000000000010: 00020029 00710045 00780065 00630075 †…).q.E.x.e.c.u
0000000000000020: 00740069 00760065 00450078 00650063 †.t.i.v.e.E.x.e.c
0000000000000030: 00750074 00690076 00650020 00470065 †.u.t.i.v.e. .G.e
0000000000000040: 006e0065 00720061 006c0020 0061006e †.n.e.r.a.l. .a.n
0000000000000050: 00640020 00410064 006d0069 006e0069 †.d. .A.d.m.i.n.i
0000000000000060: 00730074 00720061 00740069 006f006e †.s.t.r.a.t.i.o.n
0000000000000070: 00†††††††††††††††††††††††††††††††††††.

OFFSET TABLE:

Row – Offset
15 (0xf) – 1591 (0x637)
14 (0xe) – 1484 (0x5cc)
13 (0xd) – 1337 (0x539)
12 (0xc) – 1246 (0x4de)
11 (0xb) – 1157 (0x485)
10 (0xa) – 1022 (0x3fe)
9 (0x9) – 913 (0x391)
8 (0x8) – 788 (0x314)
7 (0x7) – 703 (0x2bf)
6 (0x6) – 634 (0x27a)
5 (0x5) – 515 (0x203)
4 (0x4) – 432 (0x1b0)
3 (0x3) – 353 (0x161)
2 (0x2) – 282 (0x11a)
1 (0x1) – 189 (0xbd)
0 (0x0) – 96 (0x60)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Nous retrouvons bien ici tous nos enregistrements, comme attendu.

Il est important de bien noter que la page indiquée correspond à l’index qui a été utilisé pour retourner les enregistrements. Il faut donc bien veiller par exemple à ne pas pas espérer retrouver la page de l’index clustered si la requête n’utilise qu’un index non clustered. En effet, chaque index utilise ses propres pages.

Par exemple :

select
	d.DepartmentID,
	d.GroupName,
	sys.fn_PhysLocFormatter(%%physloc%%) as [Emplacement]
from HumanResources.Department d with (index(PK_Department_DepartmentID))
order by d.DepartmentID

select
	d.DepartmentID,
	d.GroupName,
	sys.fn_PhysLocFormatter(%%physloc%%) as [Emplacement]
from HumanResources.Department d with (index(AK_Department_Name))
order by d.DepartmentID

Nous constatons que les pages ne sont pas identiques suivant l’index utilisé.

Voilà donc pour une petite présentation de la colonne cachée %%physlock%%, qui permet de localiser précisément où les données sont rangées.  Et le stockage des données étant au coeur de tout moteur, il est toujours intéressant d’avoir le maximum d’informations à son sujet…

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Etes-vous un robot ? *Chargement du capcha...

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur la façon dont les données de vos commentaires sont traitées.