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.
    • 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, means the extent is free, otherwise means the extent is allocated (not necessary full).
    • 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, means the extent is allocated but has free space, otherwise means the extent is 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.
  • Type 14 – Server Configuration Page (It may not be the official name)
    • Part of information returned from sp_configure.
    • It only exists in master database, file 1, page 10
    • SQL Server 2008 Only
  • Type 15 – File Header Page
    • Information about the file.
    • It’s always page 0 every data page.
  • 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.
Page Type

You May Also Like

4 thoughts on “Page Type

  1. Hi John,

    This is very useful. The Microsoft site doesn’t list all these page types, it has just 8 of them listed.
    Any luck on finding the actual name of the type 14 page type ? Also, can you clarify if it does not exist on other versions of SQL like SQL Server 2008 R2 or 2012 ?

  2. Hi John , Excellent Article.

    Please clarify , if FILEHEADER_PAGE – Is it File zero for Every Data Page or Per Data File.

    Thanks,
    Tarriq

Leave a Reply to John H Cancel reply

Your email address will not be published. Required fields are marked *

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.