SQL Server 2016 Trace Flags 1118 and 1117 for Page Allocations

By:   |   Comments (1)   |   Related: > SQL Server 2016


SQL Server 2016 provides several new features along with modification and simplification of existing features. There are a few trace flag changes as well, which is important to know since it might help in performance troubleshooting and improvement of the overall database system. In this tip we are going to see these Trace Flag changes and the default behavior in SQL Server 2016.


Before we start with the specific Trace Flags, let me first give a brief introduction to SQL Server pages and extent allocations.

  • Page: A page is the most basic element of storage in SQL Server. The size of a page is 8KB.
  • Extent: An extent is a set of 8KB contiguous data pages, so it is 8 * 8KB = 64KB in size.

There are two types of extents in SQL Server:

  • Uniform Extent: An extent where all 8 pages are reserved or filled with one table's data.
  • Mixed Extent: An extent where 8 pages are shared by multiple objects.

Now that we have that covered, let's look at how the trace flags work.

SQL Server Trace Flag 1118

Up to SQL Server 2014 we had Trace Flag 1118 which is used to allocate a Uniform Extent instead of Mixed Extents to minimize contention in extent allocation. If this trace flag is enabled, then the first 8 data pages for tables were also Uniform Extents rather than Mixed Extents.

In SQL Server 2016, this uniform extent allocation is the default behavior and we can change this behavior if required by using an ALTER DATABASE command, however we cannot change the behavior for the TempDB database.  We can check the default behavior using the sys.databases catalog view which includes an additional column: is_mixed_page_allocation_on.

SQL Server Mixed Page Allocations per Database

As we can see, is_mixed_page_allocation_on is set to 1 for master, model and msdb which means these database tables will get the first 8 pages from the Mixed Extent and subsequent pages from the Uniform Extent. We can also see that is_mixed_page_allocation_on is set to 0 for TempDB and user defined databases which means all the pages for the table are from a Uniform Extent.

Now let's explore this default behavior with an example:

CREATE DATABASE mixed_page_allocation_test
USE mixed_page_allocation_test GO
CREATE TABLE Test ( testallocation char(8000) ) GO

A page in SQL Server is 8 KB in size (i.e. 8192 bytes) with approximately 8060 bytes available for data due and the rest for the page header and row offset.  Here is a formula:

Page (8 KB/8192 bytes) = Page header (96 bytes) + Actual data (Whatever bytes) + Row offset (2 bytes per row)

As our Test table contains a column of char(8000) data type, we should have one data page per row, so let's add some sample data into the table and review the behavior. We will be using the DMV sys.dm_db_database_page_allocations (or we can use DBCC IND) to view the extent allocation.

GO 20
SELECT DB_NAME(database_id) as DBName, OBJECT_NAME(object_id) as ObjectName, extent_page_id, page_type_desc FROM sys.dm_db_database_page_allocations(DB_ID('mixed_page_allocation_test'), OBJECT_ID('test'),NULL,NULL,'DETAILED') go

As we can see below, SQL Server 2016 is allocating uniform extents by default and once an extent is completely allocated another uniform extent is allocated.

SQL Server Page Allocations for the Test table

When we enabled Trace Flag 1118 in SQL Server 2014, the setting is applied to the SQL Server instance (i.e. for all databases).  Now with SQL Server 2016 we can control the behavior at the database level also.  With SQL Server 2016 for TempDB page allocations, there will always be Uniform Extents without using Mixed Extents.

Now we will disable the behavior with the ALTER DATABASE command:

CREATE DATABASE mixed_page_allocation_test_1
USE mixed_page_allocation_test_1 GO
CREATE TABLE test_1 ( testallocation_1 char(8000) ) GO
-- command to set mixed_page_allocation to ON ALTER DATABASE mixed_page_allocation_test_1 SET MIXED_PAGE_ALLOCATION ON

Configure a database with MIXED_PAGE_ALLOCATION ON

Now insert the same test data into the table as we did above to see the behavior for page allocations.

SELECT DB_NAME(database_id) as DBName,OBJECT_NAME(object_id) as ObjectName,
FROM sys.dm_db_database_page_allocations(DB_ID('mixed_page_allocation_test_1'),

We can see the first 8 data pages have been allocated from different extents. Here the pages for these extents are page_id 32, 40, 72, 80 and 88.

Review the SQL Server Page Allocations for the Test_1 table

As previously stated, TempDB by default uses mixed page allocation set to 0 and we cannot turn this Mixed_Page_Allocation On for Tempdb. If we try as shown below, we will get the following error:

Error message when trying to change the page allocations for TempDB

SQL Server Trace Flag 1117

You might have multiple TempDB data files and SQL Server uses them in a round robin fashion, unless they are not the same size. If one file has to grow and the others do not grow with it, SQL Server will tend to use the larger file for most operations. So up to SQL Server 2014, we had to size all TempDB data files the same, set the same autogrowth for each as a fixed size and implement trace flag T1117 by which SQL Server forces all of the database data files to grow when any individual file needs to grow.  For user defined databases we can also enable the files to equally grow. 

With SQL Server 2016 this setting is automatically enabled which means SQL Server will try to grow all of the files, but will only grow the ones that can.  Here are some sample scripts:

-- to autogrow all files in the filegroup ALTER DATABASE Adventureworks2012 MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES GO
--to autogrow single file in the filegroup ALTER DATABASE Adventureworks2012 MODIFY FILEGROUP [PRIMARY] AUTOGROW_SINGLE_FILE GO

An important point to note is that the default value is AUTOGROW_SINGLE_FILE which is the same as when the Trace Flag is disabled.  Also keep in mind this setting is set at the file group level not at the database level.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Rajendra Gupta Rajendra Gupta is a Consultant DBA with 14+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Monday, August 22, 2022 - 5:12:18 AM - Bastian Back To Top (90394)
Hi, thanks for explanation.

For TF 1117 you say in one sentence:
"With SQL Server 2016 this setting is automatically enabled which means SQL Server will try to grow all of the files [...]"

In another sentence you say:
"An important point to note is that the default value is AUTOGROW_SINGLE_FILE which is the same as when the Trace Flag is disabled."

So whats the correct info? In my opinion the default is "AUTOGROW_SINGLE_FILE" so the same behaviour as the TF would not be set.

I think this is a problem because you have to set this for every new database. You even can not set this in model db as default for new databases! Also to set this setting on existing databases you need exclusive access (single_user) which will not be that easy on production systems!


get free sql tips
agree to terms