Saturday, August 2, 2008

SQL Server Architecture - Different Types Of Pages In SQL Server

All objects in SQL Server is stored on an 8KB page. The page is the common for all objects in SQL Server. Many types of pages exist, but every page has some factors in common. Pages are always 8KB in size and always have a header, leaving about 8,060 bytes of usable space on every page.

SQL Server has eight primary types of pages:

Data pages
Data pages hold the actual database records. The data page is 8,192 bytes, but only 8,060 of those bytes are available for data storage because a header at the beginning of each data page contains information about the page itself. Rows are not allowed to span more than one page, but if you have variable-length columns that exceed this limit, you can move them to a page in the ROW_OVERFLOW_DATA allocation unit.

Index pages
Index pages store the index keys and levels making up the entire index tree. Unlike data pages, you have no limit for the total number of entries you can make on an index page.

Text/image pages
Text and image pages hold the actual data associated with text, ntext, and image datatypes. When a text field is saved, the record will contain a 16-byte pointer to a linked list of text pages that hold the actual text data. Only the 16-byte pointer inside the record is counted against the 8,060-byte record-size limit.

Global Allocation Map pages
The Global Allocation Map (GAM) page type keeps track of which extents in a data file are allocated and which are still available.

Index Allocation Map pages
Index Allocation Map (IAM) pages keep track of what an extent is being used for—specifically, to which table or index the extent has been allocated.

Page Free Space pages
This is not an empty page; rather, it is a special type of page that keeps track of free space on all the other pages in the database. Each Page Free Space page can keep track of the amount of free space of up to 8,000 other pages.

Bulk Changed Map pages
This page contains information about other pages that have been modified by bulk operations (such as BULK INSERT) since the last BACKUP LOG statement.

Differential Changed Map pages
This page contains information about other pages that have changes since the last BACKUP DATABASE statement.
SQL Server pages. data pages. index pages. sql server architecture. sql architecture. sql pages. sql free space pages. different types of pages. types of pages in sql server. sql server 2012. sql server 2012 data pages. sql server 2008 data pages. sql server 2012 index pages. sql server 2005 index pages. sql server 2008 r2 index pages. data and index pages in sql server. sql index pages.


nice article, is there anything added in sql2008?

Post a Comment