SQL Server: Understanding the role of Extent

In the previous articles, I have explained basic about the architecture of Data Files and Data Pages of the SQL Server.

The SQL Server Extent is a one type of storage unit and it stores eight physical contiguous 8KB pages.
Each extent is 64KB size and we store 16 extents per megabyte.

The eight data pages are logically grouped into extents and extents is responsible to manage storage space for data pages.

As per my previous discussion, a single data page can never merge data from multiple tables.
But SQL Server does not allocate whole extents to the particular table.

There are two types of Extent, Uniform Extent and Mixed Extent.

Uniform Extent: This Extent basically for single user object and it stores all 8 data pages for a single user object. If our table has a large set of records, internally it stores all related 8 data pages into one Extent.

Mixed Extent: This Extent owned by multiple user object and it stores 8 data pages may be for multiple user object. It can also combine different type of data pages into one extent.

When you first create a table SQL Server starts by allocating a data page to it from a Mixed Extent. Once the table has enough data to warrant a full extent, SQL Server will allocate a Uniform Extent to it.

Let me explain with the simple example:

First, create a table with sample records:

DBCC IND to check the information about allocated pages:

The Result:

SQL Server Fragmented Pages

You can find first 8 pages have not sequential PagePID because the first time it stored in a Mixed Extent mode, but second 8 pages have a sequential PagePID because same table requires more 8KB pages.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of