How to store database files, files, and file groups in SQL Server

Preface: Last time I wrote an article about indexes, several insite emails from yuanyou asked me how to store indexes and tables on different hard disks. I think it should be easier to write an article to describe files and file groups.

Introduction

In SQL SERVER, the storage method of the database on the hard disk is no different from that of the common files in Windows, just a few files. SQL SERVER manages files by managing logical file groups. understanding the concepts of files and file groups is the most basic knowledge for better database configuration.

 

Understanding files and file groups

In SQL SERVER, the logical object of the file group is used to manage the files that store data.

First, let's look at a picture:

The logical database we see is composed of one or more file groups.

The file group manages files on the disk, and the file stores the actual data of SQL SERVER.

 

Why File Group Management

From the user's perspective, there are only three types of data objects to be stored in the file group for the created object: tables, indexes, and large objects (LOB)

You can use file groups to isolate users and files, so that you can create tables and indexes for file groups, rather than files on the actual disk. When a file is moved or modified, the table and index created by the user are created on the file group and do not depend on the specific file. This greatly enhances manageability.

Another point is that using file groups to manage files can make different files in the same file group distributed in different hard disks, greatly improving IO performance.

SQL server automatically allocates new space based on the initial size and growth of each file. Assume that file A in the same file group is twice the size of file B, if A new data occupies three pages, two pages are allocated to file A proportionally, and one Page is allocated to file B.

 

File category

• Primary file: this file is mandatory and can only have one. This file stores additional information such as the location of other files. The extension is. mdf.
• Secondary files: any number of files can be created for different purposes. The extension is. ndf.
• Log File: stores logs. The extension is. ldf.

After SQL SERVER 2008, file stream data files and full-text index files are added.

The above file name extensions can be modified at will, but I recommend that you use the default extension.

You can use the following statement to view the files in the database:

Note that if a table contains multiple physical files, the data page of the table is organized as N (N is a specific file) B trees. instead of a B-tree object.

 

Create and use a file group

Creating a file or file group can be done either in SSMS or using T-SQL statements. For a database, you can add files and file groups when creating a database, or add files and file groups to an existing database. these methods are similar. next let's take a look at how to add files and file groups to an existing database through SSMS.

First, create a file group:

After the file group is created, you can add files to the existing file group:

Now we can add the created table or index to the new file group through the statement:

 

Advantages and disadvantages of using multiple files

Generally, a small database does not need to create multiple files to distribute data. However, as data grows, the disadvantages of using a single file become apparent.

First, using multiple files to distribute data to multiple hard disks can greatly improve IO performance.

Second, for databases with a few files, backup and recovery will be much easier. I have met a GB database, but I don't have such a large storage device on hand...

 

However, in the world of databases, each benefit is often accompanied by a disadvantage:

Obviously, using multiple files requires more disk space. This is because each file has its own B-tree organization mode and its own growth space. Of course, there is also a set of its own fragments-.-But in most cases, the disadvantages of occupying more disk space is far less than the benefits of Multi-file.

 

Summary

This article briefly describes the concepts of files and file groups in SQL SERVER, and describes how to configure files and file groups. Organize different file groups according to the business to distribute different files, so as to improve the performance. It is very helpful for you to receive a few calls in the middle of the night :-)