MSSQL pages & extents notes
Pages - The fundamental unit of storage in mssql.
Extents - Contigous 8 blocks or pages form 1 extent. The extents simplify page management.
Page size default: 8KB
Pages have header - storing page type (data or index or large object - lob, PFS - Page Free Space, shared/global allocation map , type of allocation unit (IAM), block change info after log backup & database backup), amount of free space in the page, allocation unit id of the object owning it.
Row offsets are used to identify the position of the row in a page.
Rows cant span multiple pages.
ROW_OVERFLOW_DATA allocation unit is used to support tables with row size more than 8060 bytes, the address to ROW_OVERFLOW_DATA is kept in IN_ROW_DATA allocation unit.
The individual column size cant exceed 8K bytes. Only combined sizes of columns in a table can exceed 8K bytes and become eligible for this ROW_OVERFLOW_DATA and IN_ROW_DATA operation.
It is recommended to avoid ROW_OVERFLOW_DATA columns in clustered key indexes; otherwise further inser/update operations could fail. In nonclustered no issues.
Extents: MSSQL allocates and deallocates storage space using extents.
Uniform and mixed are 2 types of extents. Uniform only one object owns the extent, but with multiple several; upto 8 per extent 1 page per object at max can own a extent.
From SQL Server 2016, user and tempdb by default are allocated uniform extents only.
Functions : sys.dm_db_database_page_allocations - provides information about page allocation vs sys.dm_db_page_info - provides information about pages
Manage Extent allocation: (To retrieve the information about weather a extent is free or not & allocate them on demand)
Uniform extents use - GAM (Global Allocation Map) ; free is set to 1 vs otherwise set to 0
Mixed extents use - SGAM (Shared Global Allocation Map) ; used as SGAM and it has free page, hence it is set to 1 vs otherwise set to 0 (not free or not SGAM)
Page Free Space (PFS) - page maintains the free space information about other pages like weather it is allocated, % of free space. A single PFS can track 8088 pages, so after 8088 pages a new PFS will be allocated.
DB engine manage space in a file using the below sequence of allocation in an extent:
Page 0 -> File Header
Page 1 -> PFS
Page 2 -> GAM
Page 3 -> SGAM
etc...
To manage space used by object: An object has 3 types of allocations; the allocations are managed using Index Allocation Mapping.
Index Allocation Mapping (IAM): 3 types of allocation unit, 1. IN_ROW_DATA, 2. ROW_OVERFLOW_DATA, 3. LOB_DATA.
As the name implies, IN_ROW_DATA stores the heap data. The overflow data of a heap is kept in ROW_OVERFLOW_DATA and large objects of a heap is kept in LOB_DATA.
Note a single IAM page supports 4GB worth of extents. Every file per allocation unit has 1 IAM or if the AU size is more than 4GB file, then a new IAM page will be created.
IAM pages are randomly located, but they are chained. Information about IAM can be found in sys.system_internals_allocation_units view.
No comments:
Post a Comment