SQL Server Pages and Extents Architecture (2008 R2)

Page – fundamental unit of storage in SQL Server

Extent – eight physically contiguous pages

Pages

·     page size is 8kb in SQL Server

·      SQL Server databases have 128 pages/mb  (1024k/8k=128)

·       each page begins with 96b header (header stores info about page like page number, page type, amount of free space, allocation unit ID the object that owns page, etc.)

·        page types

o   data – rows with data (except text, ntext, image,nvarchar(max), varchar(max), varbinary(max), xml [when text in row is set to ON]

o   index

o   text/image - text, ntext, image,nvarchar(max), varchar(max), varbinary(max), xml and variable length columns when data row exceeds 8kb (varhcar, nvarchar, varbinary, sql_variant)

o   GAM/SGAM (Global Allocation Map/Shared Global Allocation Map) – into about whether extents are allocated

o   page free space – info about page allocation and free space available on pages

o   index allocation map – info about extents used by a table or index

o   bulk changed map – info about extents modified by bulk operations (???) since the last

BACKUP LOG statement

o   differential changed map – info about extents that have changed since the last BACKUP DATABASE statement

·       data rows are put on the page serially following the header

·        row offset table starts at the end of the page and contains one entry for each row on the page recording how far the first byte of the row is from the start of the page (entries in the row offset table are in reverse order from the sequence of the rows on the page

 

 

 

·         Rows cannot space pages

·         Portions of a row may be moved off the row’s page(24b pointer on original page is maintained)

Log files DO NOT contain pages but contain a series of log records

 

Extents

·         basic unit by which space is managed

·         8 physically contiguous pages (64kb)

·         SQL Server databases have 16 extents per mb (1024kb / 64kb = 16)

·         Two types of extents

o   uniform

o   mixed

·         uniform extents – owned by a single object; all 8 pages in the extent can only be used by the owning object

·         mixed extents – shared by up to 8 objects; each of the eight pages in the extent can be owned by a different object

·         new tables and indexes are created as mixed extents; when those objects exceed 8 pages then it switches to use uniform extents for subsequent allocations ( if object has enough rows to generate 8 pages initially then it starts out using uniform extents)