Expand All Database Files Simultaneously Using SQL Server 2016 AUTOGROW_ALL

Problem

SQL Server database auto-growth is a process in which the SQL Server Engine expands the size of the database file when that file runs out of space. The size expansion amount is based on the database files auto-growth settings that are configured at the database level. These database file auto-growth settings include growing the file by a specific size, percentage of the current file size or no growth performed at all. The best practice for these options is growing the files with a fixed amount to have better control on the disk drive consumption process.

Auto-growth is an expensive process, where SQL Server will hold up the database processing during the auto-growth process, slowing the database response time for the incoming commands runs against that database. This process is performed on each database file separately, resulting in database files with different sizes. Is there a way to enforce all database files to be expanded at the same time?

Solution

Generally speaking with a large database, you may need to create more than one data file in the same filegroup. When the data is written to that database, a round robin insert process will be performed across these files, in which a piece of data will be written to each file until the data is fully written to the files. SQL Server writes data to the database files depending on the amount of free space in each data file, using the Proportional Fill algorithm. If the data file runs out of space, the auto-growth event will be triggered to expand the file size. In this way, one file will be larger than the other one, which results in an unbalanced data distribution across these files. To ensure an even distribution of the data across the database files, the database files should have the same size.

Using Trace Flag 1117 in SQL Server version earlier to SQL Server 2016, all database files will expand at the same time when one of these files runs out of space, taking into consideration that the auto-growth is configured on the database. However, this trace flag is a global level trace flag, which means that it will be applied to all databases hosted on the SQL instance, having the auto-growth enabled on the databases can potentially consume the disk drive space faster.

SQL Server 2016 introduces a new feature in which you can control the auto-growth process of the database files within the same filegroup at the database level, to be at singe file at a time or all database files at the same time. This occurs if the auto-growth is configured on that database, using one of the new ALTER DATABASE options AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES.

To understand this new feature practically, we will create two new databases; each database has three data files and one log file with 4096KB as an initial size for all the database files, and auto-growth is enabled on all the database files. The script that is used to create the first database is the following:

 CREATE DATABASE [AutoGrowthTest]
ON  PRIMARY
( NAME = N'AutoGrowth_1', FILENAME = N'D:\Data\AutoGrowth_1.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
( NAME = N'AutoGrowth_2', FILENAME = N'D:\Data\AutoGrowth_2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
( NAME = N'AutoGrowth_3', FILENAME = N'D:\Data\AutoGrowth_3.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'AutoGrowth_1_log', FILENAME = N'D:\Data\AutoGrowth_1_log.ldf' , SIZE = 4096KB , FILEGROWTH = 10% )
GO

The script to create the second database will be same as the previous one except for the ALTER DATABASE statement that is used to enable the AUTOGROW_ALL_FILES feature on the PRIMARY filegroup of that database. The script is as follows:

 CREATE DATABASE [AutoGrowthTest_WithAUTOGROW_All]
ON  PRIMARY
( NAME = N'AutoGrowthAGAll_1', FILENAME = N'D:\Data\AutoGrowthAGAll_1.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
( NAME = N'AutoGrowthAGAll_2', FILENAME = N'D:\Data\AutoGrowthAGAll_2.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
( NAME = N'AutoGrowthAGAll_3', FILENAME = N'D:\Data\AutoGrowthAGAll_3.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'AutoGrowthAGAll_1_log', FILENAME = N'D:\Data\AutoGrowthAGAll_1_log.ldf' , SIZE = 4096KB , FILEGROWTH = 10% )
GO
ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All]
MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES;
GO

We can now query the sys.database_files and sys.filegroups system objects to check the database files properties:

 USE AutoGrowthTest
GO
SELECT
    DB_NAME() DatabaseName,
    DBF.name AS FileName,
    FileG.name as FileGroupName,
    FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
    ON DBF.data_space_id = FileG.data_space_id
GO
USE AutoGrowthTest_WithAUTOGROW_All
GO
SELECT
    DB_NAME() DatabaseName,
    DBF.name AS FileName,
    FileG.name as FileGroupName,
    FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
    ON DBF.data_space_id = FileG.data_space_id

The result will show us the is_autogrow_all_files property which specifies if the database files within the same filegroup will grow together or is not.

is_autogrow_all_files property that specifies if the database files within the same filegroup will be grown together or is not enabled as is the case with the second database

Now, we will create a simple table that contains employee information in the two test databases as follows:

 USE AutoGrowthTest
GO
CREATE TABLE Employees
( EmpID int IDENTITY(1,1),
  EmpName NVARCHAR(500)
)
GO
USE AutoGrowthTest_WithAUTOGROW_All
GO
CREATE TABLE Employees
( EmpID int IDENTITY(1,1),
  EmpName NVARCHAR(500)
)

Then we will insert 260,000 records into these two tables to have a test scenario:

 INSERT INTO AutoGrowthTest.dbo.Employees VALUES ('Mohammad Yaseen')
GO 260000
INSERT INTO AutoGrowthTest_WithAUTOGROW_All.dbo.Employees VALUES ('Mohammad Yaseen')
GO 260000

We can then check the first database files sizes after inserting the data using the sp_helpfile system stored procedure:

 USE AutoGrowthTest
GO
EXEC Sp_helpfile

You can see that the database files were expanded separately due to an unbalanced round-robin writing process, this results in different sizes for these files as shown below:

database files were expanded separately due to unbalanced round-robin writing process, this results in different sizes for these files

We can check the second database files sizes, after inserting the data using the sp_helpfile system stored procedure:

 USE AutoGrowthTest_WithAUTOGROW_All
GO
EXEC Sp_helpfile

You can see that the database files were expanded at the same time, resulting in the same size with even data distribution across the files as shown below:

database files were expanded at the same time, results in the same size with even data distribution across these files

Let us perform another test. This time we will create a new filegroup on the AutoGrowthTest_WithAUTOGROW_All database and add two new data files under that filegroup, with an initial size of 6144KB:

 USE [master]
GO
ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All] ADD FILEGROUP [TestFG]
GO
ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All] 
ADD FILE ( NAME = N'AutoGrowthAGAll_5', FILENAME = N'D:\Data\AutoGrowthAGAll_5.ndf' , 
SIZE = 6144KB , FILEGROWTH = 1024KB ) TO FILEGROUP [TestFG]
GO
ALTER DATABASE [AutoGrowthTest_WithAUTOGROW_All]
ADD FILE ( NAME = N'AutoGrowthAGAll_6', FILENAME = N'D:\Data\AutoGrowthAGAll_6.ndf' , 
SIZE = 6144KB , FILEGROWTH = 1024KB ) TO FILEGROUP [TestFG]
GO

We can query the sys.database_files and sys.filegroups system objects again to check the new filegroup:

 USE AutoGrowthTest_WithAUTOGROW_All
GO
SELECT
    DB_NAME() DatabaseName,
    DBF.name AS FileName,
    FileG.name as FileGroupName,
    FileG.is_autogrow_all_files AutoGrowthEnable
FROM sys.database_files AS DBF
JOIN sys.filegroups AS FileG
    ON DBF.data_space_id = FileG.data_space_id

The two new data files under the TestFG filegroup are shown below and we can see the is_autogrow_all_files attribute is not enabled by default for these files:

The two new data files under the TestFG filegroup will be shown in the below result, with the is_autogrow_all_files attribute not enabled by default on that data files

We will create a new table called Employee_FG2 and put into the TestFG filegroup:

 USE AutoGrowthTest_WithAUTOGROW_All
GO
CREATE TABLE Employees_FG2
( EmpID int IDENTITY(1,1),
  EmpName NVARCHAR(500)
) ON TestFG

We can check the database files sizes before inserting the data using the sp_helpfile system stored procedure:

 USE AutoGrowthTest_WithAUTOGROW_All
GO
EXEC Sp_helpfile

As shown below, all database files have a 6144KB size with 1024KB fixed auto-growth amount:

All database files have 6144KB size with 1024KB fixed auto-growth amount

Then we will insert a different data amount to the old and new tables. We will insert 260,000 records to the first table and 260 records to the second table:

 INSERT INTO AutoGrowthTest_WithAUTOGROW_All.dbo.Employees VALUES ('Mohammad Yaseen')
GO 260000
INSERT INTO AutoGrowthTest_WithAUTOGROW_All.dbo.Employees_FG2 VALUES ('Mohammad Yaseen')
GO 260

Now we will check the database files sizes again after inserting the data using sp_helpfile:

 USE AutoGrowthTest_WithAUTOGROW_All
GO
EXEC Sp_helpfile

We can see below that the database data files 1, 2 and 3 in the PRIMARY filegroup are expanded to the same size and the database data files 4 and 5 in the TestFG filegroup are still the same size since we didn’t insert much data into the table on the TestFG filegroup, therefore an auto-growth was not necessary. This shows that the files within different filegroups are expanded as needed and the is_autogrow_all_files feature works at the filegroup level for the database:

database data files 1, 2 and 3 under the PRIMARY filegroup are expanded to the same size, and the database data files 4 and 5 under the TestFG filegroup are expanded to the same size

Next Steps

Resources

Community

Subscribe

Expand All Database Files Simultaneously Using SQL Server 2016 AUTOGROW_ALL_FILES

Resources

Community

Subscribe