SQL Server Architecture: Pages and Extents


Logical Architecture: Pages and Extents

Pages:
  • Page is the fundamental unit of data storage in SQL Server (datablocks in Oracle).
  • Eight(8) physically contiguous pages => One (1) EXTENT.
  • 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. (SQL Server reads/writes WHOLE pages).
  • Page size: 8kb. ==> 128 pages = 1Mb.

On SQL Server Pages

  • Header: 96-bytes. (page#, type, free space, allocation unit ID of the object that owns the page)
  • data rows: inserted serially in the page.

Types of Pages:
  • Data
  • Index
  • Text/Image
  • Global allocation Map: extent allocation info
  • Shared allocation Map: extent allocation info
  • Page Free Space: free space info
  • Index allocation map: extents used by table or index per allocation unit.
  • Bulk changed map: extents modified by BULK ops since last BACKUP LOG
  • Differential Changed Map: extents modified since last BACKTUP DATABASE

Extents
  • The basic unit of space management.
  • One EXTENT = EIGHT (8) contiguous pages (64Kb). ==> 16 Extents = 1Mb
Types of extents: Mixed and Uniform

(a) Uniform: owned by a single object

(b) Mixed: shared by up to eight objects.





1 comment:


  1. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, Internal architecture of SQL Server Extent

    http://www.dbrnd.com/2016/04/sql-server-understanding-the-role-of-extent/

    ReplyDelete