ALTER DATABASE SET AUTOGROW_ALL_FILES T-SQL Enhancement in SQL Server 2016

What is the AUTOGROW_ALL_FILES Enhancement?

In high-concurrency SQL Server environments, database filegroup optimization is essential to prevent performance degradation. When this setting is enabled, if any single file in a filegroup triggers an autogrow event, every other file in that same group expands by its specified increment at the exact same time.

This is the primary mechanism for preventing TempDB contention with AUTOGROW_ALL_FILES, as it ensures that your data distribution remains perfectly balanced across all underlying storage volumes.

Difference Between Trace Flag 1117 vs AUTOGROW_ALL_FILES

Historically, SQL Server DBAs relied on Trace Flag 1117 to force all files in a database to grow simultaneously. However, TF 1117 was a “blunt instrument” because it was typically set at the global (instance) level. This meant that if one small user database needed to grow, every file in every database on that instance was forced to expand, often leading to unnecessary disk space consumption and storage management headaches.

In SQL Server 2016, the introduction of the AUTOGROW_ALL_FILES property provides a much-needed granular approach to database filegroup optimization. You can now enable this behavior for specific, high-performance filegroups (like TempDB) while leaving others on the default single-file growth setting. This transition allows for better SQL Server 2016 disk I/O performance tuning without the side effects of global configuration.

This is a major win for SQL Server 2016 disk I/O performance tuning, as you can now apply unified growth to TempDB or critical VLDB (Very Large Database) data files without impacting smaller, less critical user databases.

Summary of Why You Should Upgrade to AUTOGROW_ALL_FILES

• Eliminate Hotspots: Stops one disk from doing all the work.
• Reduce Latch Contention: Minimizes PFS page contention in TempDB.
• Granular Control: No more global trace flags; apply it only where needed.
• Balanced Growth: Ensures predictable storage capacity planning.

How to Enable AUTOGROW_ALL_FILES in SQL Server 2016 (Syntax)

To implement this enhancement and start preventing TempDB I/O hotspots 2016, you must utilize the updated ALTER DATABASE command. Unlike previous versions of SQL Server that required global trace flags, this T-SQL enhancement targets the filegroup container itself. This allows for granular control, ensuring that unified file growth is only applied to the specific filegroups that require high-performance storage throughput.

T-SQL Syntax Example

Use the following script to enable the AUTOGROW_ALL_FILES property. In this example, we are targeting the PRIMARY filegroup, which is common for databases where data is spread across multiple files to optimize SQL Server 2016 disk I/O performance tuning.

-- Enabling unified growth for the PRIMARY filegroup
-- This ensures all files in the group grow at the same time
ALTER DATABASE [YourDatabaseName] 
MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
GO

-- Verifying the setting for your database filegroups
-- The 'is_autogrow_all_files' column should now return 1 (True)
SELECT name, is_autogrow_all_files 
FROM sys.filegroups;
GO

Why TempDB Requires AUTOGROW_ALL_FILES for Performance

In SQL Server, the Proportional Fill algorithm determines data distribution based on the percentage of free space in each file. Without the AUTOGROW_ALL_FILES enhancement, a single file growth event creates an “I/O skew,” forcing the engine to hammer one file while others sit idle.

Enabling this feature ensures all files in a filegroup stay the same size, allowing SQL Server to continue writing across all files in parallel, maximizing your storage throughput and preventing the allocation bottlenecks that lead to server-wide stalls.

Performance Impact: The “Why” for Database Administrators

For senior DBAs, the move to SQL Server 2016 proportional fill enhancement isn’t just about storage management—it is a critical requirement for SQL Server 2016 disk I/O performance tuning.

To understand the stakes, consider a high-concurrency environment like TempDB configured with 8 data files. Under the legacy default behavior, if one file triggers an autogrow event while the others remain static, the SQL Server proportional fill algorithm logic will immediately “skew” almost all new write traffic to that larger file because it possesses the highest percentage of free space.

Impact on PFS Page Contention and I/O Skew

Instead of an efficient round-robin distribution of data, you end up with a massive bottleneck in storage throughput. This single-file “hammering” creates three distinct performance killers:

• PFS Page Contention: Multiple threads fight for access to the Page Free Space (PFS), GAM, and SGAM allocation maps within that one file.
• I/O Hotspots: Your storage throughput is limited to the speed of a single file’s disk/LUN rather than being distributed across the entire array.
• Latch Contention: You will see a spike in PAGELATCH_UP or PAGELATCH_EX wait types as the engine struggles to manage the lopsided allocation workload.

The Solution: Unified File Growth

By utilizing the ALTER DATABASE MODIFY FILEGROUP ALL_FILES syntax, you enforce unified file growth. When growth is required, all 8 files expand in lockstep, ensuring that the free space ratio remains identical across the entire filegroup.

This strategy is the gold standard for preventing TempDB contention, as it allows the engine to utilize the full bandwidth and parallel processing power of your underlying storage subsystem. Transitioning to this model is the most effective way to replace Trace Flag 1117 in SQL Server 2016, moving from a blunt global setting to a precision database filegroup optimization tool.

Implementation Best Practices

Maintain Uniform Configuration

Before enabling this setting, ensure all files within the filegroup are configured with the exact same initial size and the same growth increment (ideally in MB, not percentage).

Verifying Configuration via sys.filegroups

Always use the sys.filegroups catalog view after execution. The column is_autogrow_all_files is the source of truth for your database filegroup optimization efforts.

• Query to Verify:

SELECT name, is_autogrow_all_files 
FROM sys.filegroups;

Target TempDB First

Since TempDB is the most common victim of allocation bottlenecks, applying this syntax to your TempDB filegroups should be your first priority during a SQL Server 2016 migration.

Trending SQL Server Articles and Tips

Conclusion: Optimizing Your SQL Server 2016 Infrastructure

Implementing the ALTER DATABASE SET AUTOGROW_ALL_FILES enhancement is a fundamental step in modernizing your database filegroup optimization strategy. By moving away from the “blunt instrument” of Trace Flag 1117 and embracing unified file growth, you ensure that your proportional fill algorithm remains balanced. This not only results in preventing TempDB contention but also significantly boosts overall storage throughput by eliminating I/O hotspots.

As you continue your SQL Server 2016 disk I/O performance tuning journey, the next step is to look at how the engine handles data storage efficiency at the row and page level.

Next Step: Learn how to reduce your storage footprint and improve memory performance in our next guide: Part 8: Using COMPRESS and DECOMPRESS Functions in SQL Server 2016.

Frequently Asked Questions (FAQs) on AUTOGROW_ALL_FILES

1. Why should I use AUTOGROW_ALL_FILES in SQL Server 2016?

The primary reason to use AUTOGROW_ALL_FILES is to protect the efficiency of the proportional fill algorithm. In a multi-file filegroup, SQL Server writes data to files based on their free space. If only one file grows, it becomes the “target” for almost all writes, creating a disk I/O hotspot. Enabling this feature ensures all files in a filegroup stay the same size, distributing I/O throughput evenly and preventing performance bottlenecks.

2. Is AUTOGROW_SINGLE_FILE the default setting for new databases?

Yes, AUTOGROW_SINGLE_FILE is the default behavior for user-created databases to prevent unnecessary disk space consumption. However, starting with the SQL Server 2016 installation wizard, TempDB is often configured with “all files” growth by default. For production databases with high-concurrency workloads, DBAs should manually evaluate if switching to the “all files” growth enhancement is necessary for secondary data filegroups.

3. Do I still need Trace Flag 1117 in SQL Server 2016 or later?

No. Trace Flag 1117 is effectively deprecated for SQL Server 2016 and higher. The legacy trace flag was a “blunt instrument” that forced every file in every database on the instance to grow simultaneously. The new T-SQL enhancement allows for granular control, enabling you to apply the “grow all files” logic to a specific database or even a specific filegroup without affecting the entire instance.

4. Can I set different growth increments for files in the same filegroup?

Technically, SQL Server allows different growth settings, but it is a performance anti-pattern. When using AUTOGROW_ALL_FILES, if files have different increments (e.g., one in MB and one in %), they will eventually fall out of sync. To maintain a balanced proportional fill, always configure identical growth increments and initial sizes for every file within a targeted filegroup.

5. How does AUTOGROW_ALL_FILES impact TempDB contention?

One of the best use cases for this enhancement is TempDB optimization. By ensuring all TempDB data files grow in unison, you prevent one file from becoming larger than others. This uniform sizing helps reduce PFS, GAM, and SGAM page contention, ensuring that internal temporary objects are distributed across all threads and files equally, which is vital for high-volume T-SQL processing.

6. Does this enhancement affect the Transaction Log (LDF) files?

While the syntax applies to the filegroup, it is important to note that SQL Server does not support multiple log files for parallel processing. Even if you have multiple LDF files, they are used sequentially. Therefore, while you can set this on a filegroup, the most significant performance gains are seen in Data Filegroups (MDF/NDF) where parallel I/O is actually utilized by the engine.

7. What is the impact on VLF (Virtual Log File) fragmentation?

If you have AUTOGROW_ALL_FILES enabled and your growth increments are set too small (e.g., 1MB), every growth event creates new VLFs across all files. This can lead to VLF fragmentation, which slows down database recovery and log backups. For the best results, use a fixed, significant growth increment (like 256MB or 1024MB) to keep the number of VLFs manageable while keeping files synchronized.

8. Can I enable AUTOGROW_ALL_FILES on the Primary filegroup?

Absolutely. You can execute ALTER DATABASE [DBName] MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES. This is highly recommended for databases that rely heavily on the primary filegroup for data storage, as it prevents the “Primary File” from becoming a bottleneck while secondary NDF files remain underutilized.

9. Is there any storage risk associated with growing all files at once?

The main risk is rapid disk exhaustion. Because every file in the group grows simultaneously, a single 1GB growth event in a 4-file group will instantly consume 4GB of disk space. DBAs must ensure that the underlying storage LUN has sufficient overhead to handle multi-file growth bursts to avoid the database going into a “Standby/Offline” state due to lack of space.

10. Does Instant File Initialization (IFI) affect AUTOGROW_ALL_FILES?

Yes, and it is highly recommended. When AUTOGROW_ALL_FILES is enabled, a single growth event triggers multiple file expansions. Without Instant File Initialization, SQL Server must “zero out” the new space for every file, which can cause significant T-SQL execution timeouts during growth bursts. Enabling IFI ensures that the space is allocated nearly instantly, allowing unified file growth to occur without stalling your production workload.

SQL Server 2016 T-SQL Enhancement Series

• COMPRESS and DECOMPRESS Functions
• STRING_SPLIT and STRING_ESCAPE Functions
• FORMATMESSAGE Statement
• SERVERPROPERTY Function
• TRUNCATE TABLE WITH PARTITIONS
• DROP IF EXISTS
• ALTER TABLE WITH (ONLINE = ON | OFF)
• MAXDOP for DBCC CHECKDB, DBCC CHECKTABLE and DBCC CHECKFILEGROUP
• ALTER DATABASE SET AUTOGROW_SINGLE_FILE
• ALTER DATABASE SET AUTOGROW_ALL_FILES