File groups and files for SQL Server

1. Default condition

After you create a database, you typically have two files with a suffix of. mdf (primary file) and. ldf (log files) by default with only one filegroup

2. Add filegroups, file groups are used to place files (users can add file suffixes by default to. ndf files)

3. Add Files

4. Why use Filegroups

For the user's perspective, there are only three data objects specified for the stored filegroup for the Created object: Table, index, and large object (LOB)

Using filegroups allows you to isolate users and files so that users can set up tables and indexes on filegroups rather than files on actual disks. When files are moved or modified, the user-created tables and indexes are built on filegroups and do not rely on specific files, which greatly enhances manageability.

Also, using filegroups to manage files can allow different files within the same filegroup to be distributed across different hard disks, greatly improving IO performance.

SQL Server automatically assigns newly added space based on the initial size and growth of each file setting , assuming that file a in the same filegroup is set to twice times the size of file B, a new data occupies three pages (page), 2 pages are allocated proportionally to file a, and 1 pages are assigned to File B. (must files and files in the same filegroup)

5. Why file (. ndf)

Can relieve disk pressure, spread table data across different disk locations, and improve IO performance, especially on servers with disk arrays

6. Pros and cons

At the same time, because each file has its own set of B-tree organization and its own growth space, so the use of multiple files need to occupy more disk space.

7. Example

--current database, querying filegroups, files
SELECT DF. [NAME],DF.PHYSICAL_NAME,DF. [Size],
Df. [Growth],f.[name] as [Filegroup],f.[is_default]
From Sys.database_files DF
Left JOIN sys.filegroups F
On df.data_space_id = f.data_space_id

--current database, data file occupancy and remaining space
SELECT db_name () as DbName,
Name as FileName,
size/128.0 as CURRENTSIZEMB,
Size/128.0-cast (Fileproperty (name, ' spaceused ') as INT)/128.0 as Freespacemb
From Sys.database_files;

Before data is added (space remaining)

---add data:

--Test data
DECLARE @num int=0
While @num <200000
BEGIN
INSERT into Tempa VALUES (222)
SET @[email protected]+1
END

After---added:

It can be seen that the data is scattered into two files under the same filegroup, the file growth space is: all the file space under the current filegroup is full to increase the amount of file space currently required to be written