AUTOGROW_ALL_FILES: What Replaced Trace Flags 1117 and 1118

AUTOGROW_ALL_FILES and MIXED_PAGE_ALLOCATION replaced trace flags 1117 and 1118 on current SQL Server. The flags once tuned tempdb, and SQL Server 2016 made their behavior the default. A bank that upgraded from SQL Server 2012 to 2019 still ran both flags.

What the Two Flags Did

Trace flag 1117 made all files of a filegroup grow together. Without it, one file grew when it filled up, and the files became unequal. SQL Server spreads new data across files in proportion to their free space. Unequal files made the largest one the busiest.

Trace flag 1118 changed how new objects get their pages. A new table starts on pages from mixed extents, which are shared by up to eight objects. Contention on the allocation page that tracks mixed extents slowed busy tempdb workloads. The flag told SQL Server to give every new object a whole extent of its own. On SQL Server 2014 and earlier, both flags were standard advice for tempdb.

What Replaced Them: AUTOGROW_ALL_FILES and MIXED_PAGE_ALLOCATION

SQL Server 2016 turned both behaviors into settings. AUTOGROW_ALL_FILES is a filegroup option, and MIXED_PAGE_ALLOCATION is a database option. Tempdb has both behaviors on and you can’t change them. Two read-only queries confirm it. The first checks the flags on your server.

DBCC TRACESTATUS (1117, 1118);

Both flags are off here, and nothing is missing. The second query reads the replacement settings of tempdb.

SELECT f.is_autogrow_all_files AS GrowsAllFiles, d.is_mixed_page_allocation_on AS UsesMixedExtents
FROM tempdb.sys.filegroups AS f
CROSS JOIN sys.databases AS d
WHERE d.name = N'tempdb';

Tempdb grows all its files, and it doesn’t use mixed extents. That is what the two flags used to force. A server on SQL Server 2016 or later can list -T1117 or -T1118 in its startup parameters. The flags do nothing there. For other tempdb checks, read TempDB Performance: Five Settings to Check in SQL Server.

Test the AUTOGROW_ALL_FILES Setting

User databases still have the choice. The demo creates a database with two data files of 8 MB, each set to grow by 8 MB. The script reads the default file path of the instance and removes everything at the end.

IF DB_ID(N'GrowthFlagsDemo') IS NULL
BEGIN
    DECLARE @path nvarchar(260) = CONVERT(nvarchar(260), SERVERPROPERTY('InstanceDefaultDataPath'));
    DECLARE @sql nvarchar(max) = N'CREATE DATABASE GrowthFlagsDemo ON PRIMARY
        (NAME = N''GrowthFlagsDemo_1'', FILENAME = N''' + @path + N'GrowthFlagsDemo_1.mdf'', SIZE = 8MB, FILEGROWTH = 8MB),
        (NAME = N''GrowthFlagsDemo_2'', FILENAME = N''' + @path + N'GrowthFlagsDemo_2.ndf'', SIZE = 8MB, FILEGROWTH = 8MB)
        LOG ON (NAME = N''GrowthFlagsDemo_log'', FILENAME = N''' + @path + N'GrowthFlagsDemo_log.ldf'', SIZE = 8MB, FILEGROWTH = 8MB);';
    EXEC (@sql);
END;
GO
USE GrowthFlagsDemo;
GO
SELECT f.is_autogrow_all_files AS GrowsAllFiles, d.is_mixed_page_allocation_on AS UsesMixedExtents
FROM sys.filegroups AS f
CROSS JOIN sys.databases AS d
WHERE d.name = DB_NAME();

A new user database grows one file at a time and uses uniform extents by default. Now fill a table with about 18 MB of rows and read the file sizes. Each row takes 2,000 bytes, so about four rows share a page.

CREATE TABLE dbo.Fill (ID int IDENTITY(1,1) PRIMARY KEY, Pad char(2000) NOT NULL DEFAULT 'x');
GO
SET NOCOUNT ON;
DECLARE @i int = 0;
WHILE @i < 9000 BEGIN INSERT INTO dbo.Fill DEFAULT VALUES; SET @i += 1; END;
SELECT name, size * 8 / 1024 AS SizeMB FROM sys.database_files WHERE type = 0 ORDER BY file_id;

One file grew and the other stayed at 8 MB. That is the unequal growth trace flag 1117 prevented. Switch the filegroup to grow all files, add 12 MB of rows, and read the sizes again.

ALTER DATABASE GrowthFlagsDemo MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
GO
SET NOCOUNT ON;
DECLARE @i int = 0;
WHILE @i < 6000 BEGIN INSERT INTO dbo.Fill DEFAULT VALUES; SET @i += 1; END;
SELECT name, size * 8 / 1024 AS SizeMB FROM sys.database_files WHERE type = 0 ORDER BY file_id;

Both files grew by 8 MB, so the gap stayed the same. The setting keeps files growing together. It can’t make unequal files equal, so give every file the same starting size. The undo is AUTOGROW_SINGLE_FILE in the same statement.

Test the Extent Setting

The extent setting is easy to see with a one row table. The next script builds two of them. It turns MIXED_PAGE_ALLOCATION on for the second and back off afterward. The allocation function reports where each page came from.

CREATE TABLE dbo.TinyUniform (ID int NOT NULL);
INSERT INTO dbo.TinyUniform VALUES (1);
ALTER DATABASE GrowthFlagsDemo SET MIXED_PAGE_ALLOCATION ON;
CREATE TABLE dbo.TinyMixed (ID int NOT NULL);
INSERT INTO dbo.TinyMixed VALUES (1);
ALTER DATABASE GrowthFlagsDemo SET MIXED_PAGE_ALLOCATION OFF;
GO
SELECT OBJECT_NAME(object_id) AS TableName, page_type_desc, is_mixed_page_allocation
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE object_id IN (OBJECT_ID(N'dbo.TinyUniform'), OBJECT_ID(N'dbo.TinyMixed')) AND is_allocated = 1
ORDER BY TableName, page_type_desc;

The data page of TinyUniform sits in a uniform extent, and the one of TinyMixed sits in a mixed extent. The index allocation map page, the IAM page, sits in a mixed extent in both tables. Uniform extents are the default for new objects, and that is what trace flag 1118 used to force.

What About the Bank?

On SQL Server 2019 the flags do nothing, so removing them was not the fix. Nothing here shows the flags caused the bank’s slowdown. Removing them was cleanup. The rule is plain: the flags apply to SQL Server 2014 and earlier, and from 2016 they have no effect. Another angle is in Trace Flags 1117 and 1118 No Longer Required since SQL Server 2016.

You could argue that a flag that does nothing costs nothing, so leave it. The flag does cost something. A startup parameter nobody understands confuses the next upgrade and the next audit. It also hides the settings that now do the work.

What to Remember

On SQL Server 2016 and later, ignore trace flags 1117 and 1118. Read AUTOGROW_ALL_FILES and MIXED_PAGE_ALLOCATION instead, and give your data files equal sizes. Run the cleanup script when you finish the demo.

USE master;
GO
IF DB_ID(N'GrowthFlagsDemo') IS NOT NULL
BEGIN
    ALTER DATABASE GrowthFlagsDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE GrowthFlagsDemo;
END;

A trace flag is not a tuning habit, it is a patch that the next version already applied.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.