SQL SERVER – An In-Depth Look at MIXED_PAGE_ALLOCATION and Changes in Page Allocation

SQL Server, Microsoft’s long-standing and powerful database management system, has been consistently evolving to deliver better performance and increased efficiency. One such substantial evolution is the change in the page allocation mechanism in SQL Server 2016. This article takes a deep dive into these modifications, with a particular focus on the MIXED_PAGE_ALLOCATION property.

Understanding Page and Extent in SQL Server

Before diving into the changes, it’s crucial to understand the basic concepts of Page and Extent in SQL Server.

In SQL Server, the data associated with a database object (like a Table or Index) is stored on 8KB data pages. A group of these 8KB contiguous data pages forms what we call an extent of 64KB. In SQL Server, memory management happens at the extent level. There are two types of extents:

1. Uniform Extent: This is an extent where all 8 pages are reserved or filled with data from a single Table.
2. Mixed Extent: This is an extent where the 8 pages are shared by multiple objects. A mixed extent can have pages belonging to up to 8 different tables.

Comparing Page Allocation: Older Versions vs SQL Server 2016

Prior to SQL Server 2016

Before SQL Server 2016, the default behavior was that the first 8 pages for the tables were allocated from a mixed extent, and subsequent pages were from a uniform extent. Microsoft provided an option to override this behavior using a feature known as Trace Flag 1118. If this flag was enabled, the first 8 data pages for the tables were also allocated from Uniform Extent, instead of Mixed Extent. This feature was particularly beneficial in avoiding resource contention issues in scenarios where a large number of temporary tables were created in the TempDB.

With SQL Server 2016

SQL Server 2016 introduced a significant change in the page allocation mechanism:

TempDB Database: The TempDB database objects, by default, get the pages from the Uniform Extent. There is no option to override this behavior, and the Trace Flag 1118 has no effect on the page allocation behavior.

User Databases: By default, the objects in user databases will also get the pages from the Uniform Extent. This behavior can be modified by setting the database property MIXED_PAGE_ALLOCATION using the ALTER DATABASE statement.

Changes in the Sys.Databases Catalog View

SQL Server 2016 added a new column is_mixed_page_allocation_on to the Sys.Databases catalog view. The value of this column determines the page allocation behavior:

• A value of 1 means the database table will get the first 8 pages from the mixed extent and subsequent pages from the uniform extent.
• A value of 0 means all the pages for the table are from the uniform extent.

You can check the page allocation mechanism for your user and system databases using the following query:

SELECT name, is_mixed_page_allocation_on 
FROM Sys.Databases

Default Values and Impact

By default, SQL Server 2016 sets is_mixed_page_allocation_on to OFF (i.e., 0) for both TempDB and user databases. This means that all pages, including the first 8, are allocated from the uniform extent.

This change in default behavior has a significant impact on database performance, particularly in scenarios where a large number of temporary tables are created. By allocating from the uniform extent, SQL Server 2016 reduces contention and fragmentation issues that often occurred when pages were allocated from mixed extents.

Controlling MIXED_PAGE_ALLOCATION

While the default behavior is to allocate all pages from the uniform extent, SQL Server 2016 provides the flexibility to change this behavior. You can control the MIXED_PAGE_ALLOCATION setting using the ALTER DATABASE statement.

To turn on MIXED_PAGE_ALLOCATION (i.e., to have the first 8 pages allocated from mixed extent), you can use the following T-SQL:

ALTER DATABASE YourDatabaseName 
SET MIXED_PAGE_ALLOCATION ON

Similarly, to turn off MIXED_PAGE_ALLOCATION (i.e., to have all pages allocated from the uniform extent), use the following T-SQL:

ALTER DATABASE YourDatabaseName 
SET MIXED_PAGE_ALLOCATION OFF

In-Depth Examples and Comparisons

Now, let’s understand these changes through detailed examples:

Example 1: Page Allocation in User Databases

In SQL Server 2016, the first eight data pages are allocated from the uniform extent, unlike older versions where these pages were allocated from the mixed extent. This change can significantly enhance performance, especially in high-volume environments.

Example 2: Page Allocation in TempDB

In SQL Server 2016, all the pages for the TempDB are allocated from the uniform extent. This is a departure from the olderversions, where the first 8 data pages were allocated from the mixed extent. This change reduces resource contention issues in scenarios where a large number of temporary tables are created in the TempDB.

Conclusion

The changes in the page allocation mechanism in SQL Server 2016 represent a significant step forward in enhancing performance and efficiency. By allocating all pages from the uniform extent by default, SQL Server 2016 helps avoid contention and fragmentation issues that were common in earlier versions.

The MIXED_PAGE_ALLOCATION property provides flexibility to modify this behavior, allowing database administrators to adjust the settings based on their specific needs and workloads. By understanding these changes, you can better optimize your SQL Server 2016 databases and ensure they deliver the best possible performance.

You can follow me on X (twitter).

Reference: Pinal Dave (https://blog.sqlauthority.com)