SQL Server Page Type

Types of Data Pages

In SQL Server, we have different type of Pages and its 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 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.

Here is the Structure of the Data Page:

There are around 17 types of page in SQL Server data file and the arrangement of the data pages is same as shown below:

     Type 1 – Data page
       - Data records in heap
       - Clustered index leaf-level
       - Location can be random

    Type 2 – Index page
       - Non-clustered index
       - Non-leave-level clustered index
       - Location can be random

    Type 3 – Text Mixed Page
       - Small LOB value(s), multiple types and rows.
       - Location can be random

    Type 4 – Text Page
       - LOB value from a single column value
       - Location can be random

    Type 7 – Sort Page
       - Temporary page for sort operation.
       - Usually tempdb, but can be in user database for online operations.
       - Location can be random

    Type 8 – GAM Page
       - Global Allocation Map, track allocation of extents.
       - One bit for each extent, if the bit is 1 then its free, otherwise its allocated.
       - The first GAM page in each file is page 2

    Type 9 – SGAM Page
       - Shared Global Allocation Map, track allocation of shared extents
       - One bit for each extent, if the bit is 1, then it has free space, otherwise its full
       - The first SGAM page in each file is page 3

    Type 10 – IAM Page
       - Index Allocation Map. Extent allocation in a GAM interval for an index or heap table.
       - Location can be random.

    Type 11 – PFS Page
       - Page Free Space. Byte map, keeps track of free space of pages
       - The first PFS is page 1 in each file.

    Type 13 – Boot Page
       - Information about the page
       - Only page 9 in file 1.
       - To review "What's on boot page?" run below commands:

         DBCC DBINFO (‘DB_Name’);
         GO
                OR
         DBCC PAGE(DB_Name, 1, 9, 3)
         GO
       - More details click here to refer Paul's Article
    
    Type 14 – Server Configuration Page (Don't know the official name)
       - Part of information returned from sp_configure.
       - It only exists in master database, file 1, page 10

    Type 15 – File Header Page
       - Information about the file.
       - It’s always page 0 every data page.
       - To review this page, run below command:

         DBCC PAGE(DB_name, 1, 0, 3)
         DBCC PAGE(DB_name, 1, 0, 3) WITH TABLERESULTS
         GO

    Type 16 – Differential Changed map
       - Extents in GAM interval have changed since last full or differential backup
       - The first Differential Changed Page is page 6 in each file

    Type 17 – Bulk Change Map
       - Extents in GAM interval modified by bulk operations since last backup
       - The first Bulk Change Map page is page 7 in each file.