How database files are stored in SQL Server, files, and Filegroups _mssql

Brief introduction

In SQL Server, the way a database is stored on a hard disk is no different from how normal files are stored in Windows, just a few files. SQL Server manages files by managing the logical filegroups. Understanding the concepts of files and filegroups is the most basic knowledge for a better configuration database.

Understanding Files and Filegroups

In SQL Server, the file that holds the data is managed through the logical object of the filegroup.

First look at a picture:

The logical databases we see consist of one or more filegroups

The file group manages the files on the disk, and the files contain the actual data for SQL Server.

Why files are managed through filegroups

For the user's perspective, there are only three data objects that need to be specified for the Stored filegroup: Table, index, and large object (LOB)

Use filegroups to isolate users and files so that users can set up tables and indexes for filegroups, rather than files on the actual disk. When files are moved or modified, manageability is greatly enhanced by the fact that the user-established tables and indexes are based on filegroups and do not rely on specific files.

Also, using filegroups to manage files allows different files within the same filegroup to be distributed across 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 and a new data occupies three pages (page), the proportional 2 pages are assigned to file A and 1 pages are assigned to file B.

Classification of documents

• First file: This file is necessary and can only be one. This file places additional information such as the location of other files. The extension is. mdf
• Secondary files: You can build as many as you want to store for different purposes. ndf extension
• Log file: Store log with the extension. ldf

After SQL SERVER 2008, there are also new file stream data files and full-text indexing files.

Several of these file name extensions can be modified at will, but I recommend using the default extension.

We can view the files in the database by using the following statement:

It is also important to note that if a table exists in more than one physical file, the table's data page is organized as n (n is a specific number of files) a B-tree. Instead of an object as a B-tree.

Create and use Filegroups

Creating files or filegroups can be done either in SSMs or by using T-SQL statements. For a database, you can add files and filegroups at the time you create them, or you can append files and filegroups to an existing database. These methods are similar. Here's a look at adding files and filegroups to an existing database by SSMS.

To create a filegroup first:

When a filegroup is created, you can add files to an existing filegroup:

Here we can add the created table or index to the new filegroup by using the statement:

Advantages and disadvantages of using multiple files

Typically, a small database does not need to create multiple files to distribute data. But as data grows, the drawbacks of using a single file begin to appear.

First: Using multiple files to distribute data to multiple hard disks can greatly improve IO performance.

Second: Multiple files for a slightly more data database, backup and recovery will be much easier. I've come across a 150G database, but I don't have this big 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 set of B-tree organization, and its own growth space. Of course, there is also a set of pieces of their own-.-but in most cases, the disadvantages of multiple-footprint disk space are far less than the benefits of multiple files.

Summarize

This article briefly describes the concepts of files and filegroups in SQL Server, and describes how files and filegroups are configured in the article. According to the business organization of different file groups to distribute different files, so that the performance of the promotion, for you in the middle of the night to take a few calls to help is a big drop:-)