For Any sql server pages and extents are very important because those are the basic units of the data storage.
Before going into the topic we must know about where is this pages concept in a database. So basically we use pages for the data storage. Data storage is a database, which is a collection of tables. The data in the database are stored in primary data files with an extension (.mdf). Secondary data files, identified with a (.ndf) extension, are used to allow the data of a single database to be spread across more than one file. Log files are identified with the (.ldf) extension.
PAGES:
The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages.
- In SQL Server, the page size is 8 KB.
- Each page begins with a 96-byte header that is used to store system information about the page.
- The row off set is of 36byte 2 bytes for each row.
- This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page.
- Data rows are put on the page serially, starting immediately after the header.
- A row in a database table cannot span more than one page, so is limited to 8 KB in size. However, if the data exceeds 8 KB and the row contains Varchar or Varbinary data, the data in those columns are moved to a new page.
- A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page.
- The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.
Different types of Pages in sql server:
In general there are 9 types of pages are there.
They are:
- Data Page
- Index Page
- Text/image page
- GAM/SGAM page
- IAM page
- BCM page
- DCM page
- Page free space
- Boot Page
Now let checks what page will do what work.
- Data Page:
This holds data records. In these data records it stores table data. Each row you add to a table is stored on a page, and depending on the size of the data in the row, the row can be stored either on a page with other rows, or on its own page or pages. 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.
- Index Page:
Index page is a page which gives the index entries and it points to the next page when the data in a page exceeds 8kb.
- Text/Image Page:
Text/Image page gives the information of large object data types: Text, ntext, image, nvarchar (max), Varchar (max), Varbinary (max), xml data and also the
Variable length columns when the data row exceeds 8 KB: Varchar, nvarchar, Varbinary, and sql_variant
- GAM/SGAM page:
Global Allocation Map and Shared Global Allocation Map are pages which give the information about whether the extents are allocated or not allocated. The major difference between the GAM and SGAM is GAM looks is used to trace the allocation of an extent whereas the SGAM is used to trace the allocation of shared extent.
- IAM page:
Index allocation map page is to track which pages have been allocated to which allocation unit. Each IAM page covers an area of 64000 extents in a file, a so-called GAM-interval. If multiple IAM pages are needed for a single allocation unit, the form a double linked list, the IAM chain.
- BCM page:
Bulk change map page gives the Information about extents modified by bulk operations. Whenever there is a change in any extent by bulk operations then this BCM sets its bit 0 to 1 so that whenever we are using bulk recovery model BCM helps sql to know about the changed extents. Once after the full backup these extents again change their bits 1 to 0.
- DCM page:
Differential change map page gives the Information about extents modified after a full backup. Whenever we take a differential backup after a full back up the DCM looks for the extents that are changed after a full backup and sets the extents bits 0 to 1. By this Differential backup can take the backup of those extents only. Once after the full backup these extents again change their bits 1 to 0.
- Page free space:
PFS gives the free space present in a page.
- Boot Page:
Boot page stores the information of the page.
Do you want to see what pages are used for your table in which you've stored your record? Run the following DBCC command:
DBCC PAGE ({'dbname' | dbid}, filenum, pagenum [, printopt= {0|1|2|3} ])
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
The per-row interpretation work for all page types, including allocation bitmaps.
By default, the output is sent to the error log. If you want the output to come back to your current connection, turn on trace flag 3604.
EXTENTS:
Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. SQL Server has two types of extents:
- Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object. All pages from a dedicated extent must be allocated to the same IAM chain
- Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object. These mixed pages are allocated from mixed extents that are not allocated to any particular IAM chain.
(FIG: Uniform and Mixed Extents)
