MIXED_PAGE_ALLOCATION is a new option introduced with SQL Server 2016. It controls whether the database can create initial pages using a mixed extent for the first eight pages of a table or index. There is a bit column in the sys.databases view with values:
- OFF -The database always creates initial pages using uniform extents. This is the default value, and
- ON – The database can create initial pages using mixed extents.
The memory management in SQL Server happens on extent level. There are two types of extents:
- Uniform extent. Its 8 pages are reserved and used by only one table, and
- Mixed extent. Its pages are shared by multiple objects (data, indexes). The maximum number of objects that can use one extent is eight.
All system databases, except tempdb have this option enabled.
It’s not possible to change this option for the system databases.
ALTER DATABASE TempDB SET MIXED_PAGE_ALLOCATION ON
Results with the following error message
Msg 5058, Level 16, State 9, Line 3Option ‘MIXED_PAGE_ALLOCATION’ cannot be set in database ‘TempDB’.
When you create a new database, it creates with MIXED_PAGE_ALLOCATION set to OFF, even though the model databases is having it set to ON. Then you can set it ON for the user created databases.
You can run this query to get info about the mixed pagle allocation setting for all databases
SELECT [name], is_mixed_page_allocation_on FROM sys.databases