Page Types:
Page type
|
Contents
|
Data
|
Data rows with all
data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max),
and xml data, whentext in row is set to ON.
|
Index
|
Contains Index entries.
|
Text/Image
|
Large object data
types:
|
text, ntext, image, nvarchar(max), varchar(max), varbinary(max),
and xml data
|
|
Variable length
columns when the data row exceeds 8 KB:
|
|
varchar, nvarchar, varbinary, and sql_variant
|
|
Global Allocation
Map, Shared Global Allocation Map
|
Information about
whether extents are allocated.
|
Page Free Space
|
Information about
page allocation and free space available on pages.
|
Index Allocation Map
|
Stores the address of the objects. Above is an index allocation map, this contains the address of all the tables.(left side is the table name and right side it is the address where the table is stored). Information about extents used by a table or index per allocation unit.
|
Bulk Changed Map
|
Information about
extents modified by bulk operations since the last BACKUP LOG statement per
allocation unit.
|
Differential Changed
Map
|
Information about
extents that have changed since the last BACKUP DATABASE statement per
allocation unit.
|
To get the pageid and page type in page:
Dbcc ind(‘dbname’,tablename,-1)
To see the records on page:
dbcc page ( {'dbname' | dbid},
filenum, pagenum [, printopt={0|1|2|3} ])
The
filenum and pagenum parameters are taken from the page IDs that come from
various system tables and appear in DBCC or other system error messages. A page
ID of, say, (1:354) has filenum = 1 and pagenum = 354.
The
printopt parameter has the following meanings:
- 0 - print just the page header
- 1 - page header plus per-row
hex dumps and a dump of the page slot array (unless it’s a page that
doesn't have one, like allocation bitmaps)
- 2 - page header plus whole page
hex dump
- 3 - page header plus detailed
per-row interpretation
Dbcc
traceon(3604)
Dbcc
page(‘dbname’, iamfid,pageid,1)
1 overal
view
3 full
view
Page Split
·
Due to regular changes in the table if the index
pages are full to allocate memory for the index key columns SS takes remaining
rows into new page. This process is called Page
split.
·
Page split increases size of index and the index
pages order changes.
Row offset is address
of every record. (2bytes per record)
There are
different types of pages in SQL Server. No matter what types of pages are, the
layout of the page is the same. A data file consists of numbers of 8k-pages. A
Page includes 8192 bytes. First 96 bytes are used for header. The rest of the
space is for data. A variable length row offset array (or slot array) is
located at the end of every page and grows backwards. Count of records (size of
array) is saved in the header. The size of each element in the offset array is
2 bytes. Records in a page are not sorted even though it is an index page. If
the data needs to be sorted, the offset array will be is sorted by the key of
the index.
As
far as I know, there are about 14 types of pages in SQL Server data file.
·
Type 1 – Data page.
o Data
records in heap
o Clustered
index leaf-level
o Location
can be random
·
Type 2 – Index page
o Non-clustered
index
o Non-leave-level
clustered index
o Location
can be random
·
Type 3 – Text Mixed Page
o Small
LOB value(s), multiple types and rows.
o Location
can be random
·
Type 4 – Text Page
o LOB
value from a single column value
o Location
can be random
·
Type 7 – Sort Page
o Temporary
page for sort operation.
o Usually
tempdb, but can be in user database for online operations.
o Location
can be random
·
Type 8 – GAM Page
o Global
Allocation Map, track allocation of extents.
o One
bit for each extent, if the bit is 1, means the extent is free, otherwise means
the extent is allocated (not necessary full).
o The
first GAM page in each file is page 2
·
Type 9 – SGAM Page
o Shared
Global Allocation Map, track allocation of shared extents
o One
bit for each extent, if the bit is 1, means the extent is allocated but has
free space, otherwise means the extent is full
o The
first SGAM page in each file is page 3
·
Type 10 – IAM Page
o Index
Allocation Map. Extent allocation in a GAM interval for an index or heap table.
o Location
can be random.
·
Type 11 – PFS Page
o Page
Free Space. Byte map, keeps track of free space of pages
o The
first PFS is page 1 in each file.
·
Type 13 – Boot Page
o Information
about the page
o Only
page 9 in file 1.
·
Type 14 – Server Configuration
Page (It may not be the official name)
o Part
of information returned from sp_configure.
o It
only exists in master database, file 1, page 10
o SQL
Server 2008 Only
·
Type 15 – File Header Page
o Information
about the file.
o It’s
always page 0 every data page.
·
Type 16 – Differential Changed
map
o Extents
in GAM interval have changed since last full or differential backup
o The
first Differential Changed Page is page 6 in each file
·
Type 17 – Bulk Change Map
o Extents
in GAM interval modified by bulk operations since last backup
o The
first Bulk Change Map page is page 7 in each file.
No comments:
Post a Comment