SQL server stores everything inside pages in different
types of pages.
Disk space is allocated to data files (.MDF/.LDF/.NDF) is
logically organized in contiguous pages (0-n)
Disk operation is perform at page level to read /write
1 Page =
8 KB
1 Extent =
8 Pages/ 64 KB
1 MB =128
Pages
Each page has 96 byte header which contains system
information which is Page number, Page type, free space on page and allocation
unit id of object which contain page and Row offset which contain row number
info growing from end of the page
Pages are following types:
|
S#
|
Page type
|
Description
|
|
1
|
Data page
|
Data records, Leaf of Clustered Index
|
|
2
|
Index page
|
Non Clustered index, Non leaf of Clustered Index
|
|
3
|
Text/Image Page
|
for large object data types(text,ntext,images,varbinary etc.)
|
|
4
|
GAM/SGAM
|
info for extent tracking
|
|
5
|
Page Free Space
|
Info about Page allocation and free space
|
|
6
|
IAM
|
info for extent usage for table or index
|
|
7
|
Bulk Changed map
|
info for extent
modification by bulk operation since last log bkp per allocation unit
|
|
8
|
Differential Changed map
|
info for extent modification since last db bkp per allocation
unit
|
|
9
|
Sort Page
|
Temp page for sorting
|
|
10
|
Boot Page
|
Info about Page
|
|
11
|
File Header Page
|
info about file
|
|
12
|
Server Config Page
|
page for sp_configure
|
Extent
Extent is used to manage space,
extent are formed by eight contiguous pages
A new table or index generally
consist mixed extent once its grows to accommodate eight pages then it switches
to uniform extent. If we create index on large table which can accommodate
eight contiguous pages then it allocate pages to uniform extents.
No comments :
Post a Comment