SQL Server 2016 Trace Flags 1118 and 1117 for Page Allocations

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

Problem

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.

Solution

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.

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
GO

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.

INSERT INTO Test VALUES('SQL2016')
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.

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
GO

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

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,
extent_page_id,page_type_desc,is_mixed_page_allocation 
FROM sys.dm_db_database_page_allocations(DB_ID('mixed_page_allocation_test_1'),
OBJECT_ID('dbo.test_1'),NULL,NULL,'DETAILED')
GO

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.

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:

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:

--ALTER DATABASE <dbname> MODIFY FILEGROUP <filegroup> { AUTOGROW_ALL_FILES | AUTOGROW_SINGLE_FILE }

-- 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

• Download and explore SQL Server 2016
• Check out SQL Server 2016 tips
• Read more about ALTER DATABASE options
• Read more about sys.database catalog views

About the author

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