SQL Server Extents: what are they?

Extents are used by SQL Server to efficiently manage pages. It is a basic unit that allocates space for eight pages, regardless of what is stored on the page. Pages always take up 8 KB so extents are always written as 64 KB blocks to the disk. Because each page is owned by a single owner, there are two types of extents.

Lets take a look at an example of a uniform extent and then we’ll look at a mixed extent. All pages in a uniform extent are owned by a single object:

Each page in a mixed extent can belong to a different owner. Because each extent is made up of 8 pages, this type of extent can contain up to 8 separate owners. Most of the time you can think of an owner as a table. In this example, the color of the header indicates page ownership:

By default, to make space allocation more efficient, SQL Server 2014 (12.x) and prior does not allocate uniform extents to tables with less than 8 pages. This allows the table to reserve less disk space; however, every solution has its trade-offs, right? Storing the page next to pages with different owners is a recipe for fragmentation. Disk read are faster when read spindle moves less to get to the next sector; however, mixed mode means the spindle needs to jump from sector to sector in order to find the pages it needs.

SQL Server 2014 (12.x) or older will move pages to a uniform style extent when a table or index grows large enough for it. Moving pages puts stress on the disk and introduces new considerations so best practice is to enforce uniform extents for all database objects.

SQL Server 2016 (13.x) changed to respect this best practice so uniform extents will be provisioned by default now. This means that a new table with 0 rows will take up at least 64 KB. These days, the wasted disk storage is cheaper than the performance loss incurred with mixed extents.

You can run this query to check the extent type for a database if you are curious:

SELECT name, is_mixed_page_allocation_on 
 FROM Sys.Databases
 WHERE name = '<INSERT DB NAME>'

-- name    is_mixed_page_allocation_on
-- <INSERT DB NAME>    0

is_mixed_page_allocation_on = 0 means that the extent is uniform; 1 means that the extent is mixed.

You can run this query if you want to change the extent type for a database:

ALTER DATABASE 
 SET MIXED_PAGE_ALLOCATION ON