SQL Server Pages and Extents

August 8, 2017 Execution Plans 2054 Views


SQL Server reads and writes data in bunches of 8KB pages. Space allocation is based on batches of 8 pages, which is called Extents.

SQL Server's unit of IO

SQL Server's unit of IO is called Page. What that means is that SQL Server stores all data in the form of Pages. To put it in other words, Page is SQL Server's virtual container in which data is stored. The virtual container is 8KB. So, SQL Server persists data in chunks of 8KB.

What implications can it have?

Pages are not just about storing data, they are also about reading data. SQL Server reads and writes data in chunks of 8KB.

What is inside Pages?

Since SQL Server persists everything in Pages, Pages can contain anything. It can be table data, index data, image data, system data, ....
The only exception to the Page contents is SQL Server LDF (Log Data File) contents. LDF data are stored in series of log records, and not in Pages. Anything else, obviously in data files, is stored in Pages.

Different types of pages

Depending on Page contents, Pages fall into one of the following eight categories.

  1. Data Page:
    User table data is stored in Data Pages.

  2. Index Page:
    Indexes created on user tables are stored in Index Pages.

  3. Text/Image Page:
    LOB data are stored in Text/Image Page. LOB data are data types that can happen to contain more than 8KB data, such as VARCHAR ( MAX ), NVARCHAR ( MAX ), VARBINARY ( MAX ), XML.

  4. Global Allocation Map, Shared Global Allocation Map Page
  5. Page Free Space
  6. Index Allocation Map
  7. Bulk Changed Map
  8. Differential Changed Map

Pages number 4 to 8 are system Pages and SQL Server uses them to manage and keep track of data and other Pages.

It's hard to manage so many pages

Now that we know what a Page is, let's count up possible number of pages in a database.

Every 1MB is 128 Pages. Now, imagine a database is 5GB; it ends up owning 655,360 Pages. Doesn't it look difficult and time consuming to manage so many pages for a 5GB database? What if the database is 50GB?

It surely is difficult and time-consuming to manage many tiny pages. Therefore, SQL Server groups Pages in batches of 8 Pages each and calls that an Extent. So, an Extent is a collection of 8 contiguous Pages. SQL Server reserves and allocates space to objects using Extents.

One big difference between Pages and Extents is That Pages are about unit of reading and writing, while Extents are about space allocation on disc.

Different types of Extents

There are two different types of Extents, Mixed and Uniform.

Uniform Extents are Extents whose all 8 Pages belong to one single object (table, index, ...).

Mixed Extents are Extents that house Pages that belong to more than one object (table, index, ...).

How does SQL Server allocate Extents and Pages

As the amount data in tables and indexes increases, SQL Server needs to allocate disc space for that. Allocation is where the Extents come into play.

SQL Server's algorithm to allocate Pages and Extents is pretty simple. For every object, SQL Server starts by allocating Pages, not extents. Therefore, Mixed Extents start to appear. Once the number of Pages allocated to an object reaches 8 Pages, SQL Server starts allocating one extent at a time for subsequent allocations. At this point, there will be both types of Extents available in database : Mixed and Uniform.

From the sounds of that, every object that has more than 8 Pages owns both Mixed and Uniform Extents. But, that's NOT true. If you create an index on an existing table and the index has enough rows to generate eight pages in the index, all allocations to the index are in Uniform Extents; and no Mixed Extent for that index at all.

How do you rate this topic?

Further reading:
Unit of data in Buffer Pool
Why does SQL Server consume so much memory?
Some SQL functions are constants
Pages in Buffer Pool fall in to two different types.......