More information about Files and Filegroups in the SQL Server(ANO9)

#FileGroups?in the SQL Server, tips and tricks in the MS SQL Server

In the previous article(ANO8) I explained datafiles and FileGroups, in this article I want to describe a number of useful system and user-defined stored procedures and system views.

First of all, let's create a database and add some filegroups and datafiles to it.

If you have any questions about scripts please leave a comment and ask your question.

USE master
GO
IF DB_ID('FGDB')>0
BEGIN
	ALTER DATABASE FGDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE FGDB
END
GO

CREATE DATABASE FGDB?
	ON? PRIMARY
	(
		NAME=FGDB,FILENAME='E:\Dump\FGDB.mdf'
	),
	FILEGROUP FG1
	(
		NAME=FGDB_Data1,FILENAME='E:\Dump\FGDB_Data1.ndf',SIZE=1MB,FILEGROWTH=1MB
	),
	(
		NAME=FGDB_Data2,FILENAME='E:\Dump\FGDB_Data2.ndf',SIZE=1MB,FILEGROWTH=1MB
	),
		FILEGROUP FG2
	(
		NAME=FGDB_Data3,FILENAME='E:\Dump\FGDB_Data3.ndf',SIZE=1MB,FILEGROWTH=1MB
	),
	(
		NAME=FGDB_Data4,FILENAME='E:\Dump\FGDB_Data4.ndf',SIZE=1MB,FILEGROWTH=1MB
	)
	LOG ON
	(
		NAME=FGDB_log,FILENAME='E:\Dump\FGDB_log.ldf'
	)
GO
USE FGDB
GO        

Notice: I set FILEGROWTH for each datafile, and I will explain this attribute in the following steps

SP_HELPFILE

this stored procedure returns the physical names and attributes such as filegroup, size,… of files associated with the current database.?

SP_HELPFILE        

sys.database_file

this is a system view that is similar to SP_HELPFILE but returns more attributes about the current database.

--1:
SELECT * FROM sys.database_files
GO
--2:
SELECT name, size/128.0 FileSizeInMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0?
? ?AS EmptySpaceInMB
FROM sys.database_files;
GO        

SP_HELPFILEGROUP

Returns the names and attributes of filegroups associated with the current database.?

SP_HELPFILEGROUP        

SYS.filegroups

Contains a row for each data space that is a filegroup.

SELECT * FROM SYS.filegroups        

Pay attention to the is_autogrow_all_files, this is a very important attribute in a large database, and I want to check it in detail.

First of all, let's explain what file growth is and how it works in SQL Server databases, and why we need to understand is_autogrow_all_files:

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 of 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 running against that database.?This process is performed on each database file separately, resulting in database files with different sizes.?

How we can control database file growth and enforce all database files to be expanded at the same time?

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.

To Enable Trace flag 1117 go to SQL server configuration manager, Select SQL server services, and on the right panel, double click on SQL server(if you installed namely instance select that), go to startup parameters, and add -T1117 in the Specify a startup parameter textbox

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 a single 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.

Check AUTOGROW_ALL_FILES to FGDB database before altering it

SELECT?
	name,is_autogrow_all_files?
FROM SYS.filegroups
GO        

Now enable AUTOGROW_ALL_FILES for FG2

ALTER DATABASE FGDB MODIFY FILEGROUP FG2 AUTOGROW_ALL_FILES
GO
SELECT
	name,is_autogrow_all_files?
FROM SYS.filegroups
GO?        

Adding a table into FG1 in which is_autogrow_all_files?is not configured, and adding 1000 records in it.

DROP TABLE IF EXISTS Employee
GO
CREATE TABLE Employee
(
	ID INT IDENTITY PRIMARY KEY,
	FullName CHAR(8000)
) ON FG1
GO

--SP_HELP Employee
GO
SET NOCOUNT ON
GO
INSERT INTO Employee (FullName) VALUES ('TestData')
GO 1000
DBCC SHOWFILESTATS
GO?        

As you see the growth in FGDB_Data1 and FGDB_Data2 is different

Adding a table into FG2 in which is_autogrow_all_files?is configured, and adding 1000 records in it.

ALTER DATABASE FGDB MODIFY FILEGROUP FG2 AUTOGROW_ALL_FILEs
GO


DROP TABLE IF EXISTS Employee2?
GO
CREATE TABLE Employee2
(
	ID INT IDENTITY PRIMARY KEY,
	FullName CHAR(8000)
) ON FG2
GO

--SP_HELP Employee2
GO
INSERT INTO Employee2 (FullName) VALUES ('TestData')
GO 1000
DBCC SHOWFILESTATS
GO
        

Because we enabled is_autogrow_all_files?for FG2 the growth in FGDB_Data3 and FGDB_Data4 is the same.

Check used space

SELECT
	DB_NAME() AS [DatabaseName],
	 Name, file_id,?
	 physical_name,
	(size * 8.0/1024) as Size,
	((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
From sys.database_files
GO?        

Best practices for autogrow

1. For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.
2. You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.
3. Autoshrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); They must not be left unmanaged.
4. Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb you can use for testing is to set your autogrow setting to about one-eight the size of the file.
5. Turn on the?\<MAXSIZE>?setting for each file to prevent any one file from growing to a point where it uses up all available disk space.
6. Keep the size of your transactions as small as possible to prevent unplanned file growth.

Template Database

USE master
GO
IF DB_ID('FGDB')>0
BEGIN
	ALTER DATABASE FGDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
	DROP DATABASE FGDB
END
GO


CREATE DATABASE FGDB?
	ON? PRIMARY
	(
		NAME=FGDB,FILENAME='E:\Dump\FGDB.mdf'
	),
	FILEGROUP FG1
	(
		NAME=FGDB_Data1,FILENAME='E:\Dump\FGDB_Data1.ndf',SIZE=1GB,MAXSIZE = 10GB ,FILEGROWTH=1GB
	),
	(
		NAME=FGDB_Data2,FILENAME='E:\Dump\FGDB_Data2.ndf',SIZE=1GB,MAXSIZE = 10GB ,FILEGROWTH=1GB
	),
		FILEGROUP FG2
	(
		NAME=FGDB_Data3,FILENAME='E:\Dump\FGDB_Data3.ndf',SIZE=1GB,MAXSIZE = 10GB ,FILEGROWTH=1GB
	),
	(
		NAME=FGDB_Data4,FILENAME='E:\Dump\FGDB_Data4.ndf',SIZE=1GB,MAXSIZE = 10GB ,FILEGROWTH=1GB
	)
	LOG ON
	(
		NAME=FGDB_log,FILENAME='E:\Dump\FGDB_log.ldf'
	)
GO