Extents

Eight physically contiguous pages in SQL Server database are called the extent. One page is 8 KB, therefore one extent is 64 KB.  Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.

SQL Server has two types of extents:

·       Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.

·       Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Uniform extents are always managed by GAM pages.

There are a few special data pages types SQL Server is using to track extents allocation. Those pages are basically bitmaps – every bit handles one extent. So one page can cover 64,000 extents or almost 4Gb of data. Let’s take a quick look at them:

GAM – Global Allocation Map – tracks if extent are available for allocation or already in use.      
SGAM – Shared Global Allocation Map – tracks if extents are mixed extent and have at least one data page available for use.        
IAM – Index Allocation Map – tracks if extents are used by specific table/index. 

Usually all new tables or indexes are allocated pages from mixed extents. SQL Server extent switches from mixed to uniform type only when a table or index has at least eight or more pages.

Up to, and including, SQL Server 2014 (12.x), SQL Server does not allocate whole extents to tables with small amounts of data. A new table or index generally allocates pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.

Starting with SQL Server 2016 (13.x), the default for most allocations in a user database and tempdb is to use uniform extents, except for allocations belonging to the first eight pages. Allocations for master, msdb, and model databases still retain the previous behaviour.

Note: Up to, and including, SQL Server 2014 (12.x), trace flag 1118 can be used to change the default allocation to always use uniform extents.

Starting with SQL Server 2016 (13.x), the functionality provided by TF 1118 is automatically enabled for tempdb and all user databases. For user databases, this behavior is controlled by the SET MIXED_PAGE_ALLOCATION option of ALTER DATABASE, with the default value set to OFF, and trace flag 1118 has no effect.

Starting with SQL Server 2012 (11.x), the sys.dm_db_database_page_allocations system function can report page allocation information for a database, table, index, and partition.

Important: The sys.dm_db_database_page_allocations system function is not documented and is subject to change. Compatibility is not guaranteed.

Starting with SQL Server 2019 (15.x), the sys.dm_db_page_info system function is available and returns information about a page in a database. The function returns one row that contains the header information from the page, including the object_id, index_id, and partition_id. This function replaces the need to use DBCC PAGE in most cases.