SQL server organizes and stores records in smaller units of data, known as pages.
The size of the page in SQL server is 8 KB. This means SQL Server databases have 128 pages per megabyte.
Page mainly divided as Page Header, Data Row, and Row Offset.
A page can have maximum storage of 8060 bytes (ie., (Total Page Capacity 8192)-Page Header-Row Offset)=8192-96-36).
Page Header:
Each page begins with a 96-byte header that is used to store metadata information about the page.
This information includes Page Type (Data Page, Index Page, Text Page, GAM, IAM), Page Number, Pointer to next page and previous page, Amount of Free Space, Amount of Space Used, and the allocation unit ID of the object that owns the page.
Data Rows:
This stores the actual data which is 8060 Bytes.
If data in any row exceeds the limit of 8060 Bytes then the rest of the data is stored in the new page and the pointer to that is stored in the header.
Row Offset :
It saves the data row's pointer in the reverse order. The last row in the data page may be the first record in the row offset.
It holds the information, that is how far is the specific row is from the header.
Types of Pages in SQL Server:
SQL server use different types of pages to store different types of data. No matter what types of pages are, the layout of the page is the same.
Basically these pages are categorize into the following 3 types:
1.Data Pages.
2.Index Page.
3.System Pages.
Data Pages:
SQL Severer uses this pages to store actual data of table.
Again data pages are categorize into following types.
1.IN_ROW_DATA Page:
It holds data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON. That means when the row size stays within the 8,060-byte limit, SQL Server stores all of the data in the IN_ROW_DATA Pages.
2.ROW_OVERFLOW_DATA Page:
When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA pages , starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8,060-byte limit.
3.Text/Image Page/LOB_DATA Page:
It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data. LOB pages are used to store the large objects which are not stored as part of row data.
Index Page:
This pages are used to stores the all index entries.
System Pages:
Page Free Space Page:
The PFS pages are used to track allocation status of pages and determines the amount of free space.
Global Allocation Map (GAM) pages:
SQL Server uses Global Allocation Map (GAM) pages to keep track of all free extents in a database file.
Shared Global Allocation Map (SGAM) pages:
SGAM pages track what extents are currently being used as mixed extent and also have at least one unused page.
Differential Changed Map (DCM):
This page is used to keeps track of which extents in a file have been modified since the last full database backup.
Bulk Changed Map (BCM):
This page is used to keep tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement.
Index Allocation Map (IAM):
This page is used to keeps track of the extents that belong to specific allocation unit of a table\object.
Sort Page:
Temporary page for sort operation.Usually tempdb, but can be in user database for online operations.
The size of the page in SQL server is 8 KB. This means SQL Server databases have 128 pages per megabyte.
Page mainly divided as Page Header, Data Row, and Row Offset.
A page can have maximum storage of 8060 bytes (ie., (Total Page Capacity 8192)-Page Header-Row Offset)=8192-96-36).
Page Header:
Each page begins with a 96-byte header that is used to store metadata information about the page.
This information includes Page Type (Data Page, Index Page, Text Page, GAM, IAM), Page Number, Pointer to next page and previous page, Amount of Free Space, Amount of Space Used, and the allocation unit ID of the object that owns the page.
Data Rows:
This stores the actual data which is 8060 Bytes.
If data in any row exceeds the limit of 8060 Bytes then the rest of the data is stored in the new page and the pointer to that is stored in the header.
Row Offset :
It saves the data row's pointer in the reverse order. The last row in the data page may be the first record in the row offset.
It holds the information, that is how far is the specific row is from the header.
Types of Pages in SQL Server:
SQL server use different types of pages to store different types of data. No matter what types of pages are, the layout of the page is the same.
Basically these pages are categorize into the following 3 types:
1.Data Pages.
2.Index Page.
3.System Pages.
Data Pages:
SQL Severer uses this pages to store actual data of table.
Again data pages are categorize into following types.
1.IN_ROW_DATA Page:
It holds data rows with all data, except text, ntext, image, nvarchar(max), varchar(max), varbinary(max), and xml data, when text in row is set to ON. That means when the row size stays within the 8,060-byte limit, SQL Server stores all of the data in the IN_ROW_DATA Pages.
2.ROW_OVERFLOW_DATA Page:
When the total row size of all fixed and variable columns in a table exceeds the 8,060-byte limitation, SQL Server dynamically moves one or more variable length columns to pages in the ROW_OVERFLOW_DATA pages , starting with the column with the largest width. This is done whenever an insert or update operation increases the total size of the row beyond the 8,060-byte limit.
3.Text/Image Page/LOB_DATA Page:
It stores LOB ( Large Object data) like text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data. LOB pages are used to store the large objects which are not stored as part of row data.
Index Page:
This pages are used to stores the all index entries.
System Pages:
Page Free Space Page:
The PFS pages are used to track allocation status of pages and determines the amount of free space.
Global Allocation Map (GAM) pages:
SQL Server uses Global Allocation Map (GAM) pages to keep track of all free extents in a database file.
Shared Global Allocation Map (SGAM) pages:
SGAM pages track what extents are currently being used as mixed extent and also have at least one unused page.
Differential Changed Map (DCM):
This page is used to keeps track of which extents in a file have been modified since the last full database backup.
Bulk Changed Map (BCM):
This page is used to keep tracks the extents that have been modified by bulk logged operations since the last BACKUP LOG statement.
Index Allocation Map (IAM):
This page is used to keeps track of the extents that belong to specific allocation unit of a table\object.
Sort Page:
Temporary page for sort operation.Usually tempdb, but can be in user database for online operations.
Thanks for the nice post.
ReplyDelete