Design5：SQL server 文件组和文件

柔情只为你懂 2022-06-17 11:29 161阅读 0赞

1，文件组和文件的作用

Sql Server的数据存储在文件中，文件是实际存储数据的物理实体，文件组是逻辑对象，Sql server通过文件组来管理文件。

![191459339826696.jpg][]

一个DataBase有一个或多个FileGroup，PrimaryFileGroup是必需的，用户可以根据需要添加FileGroup。每一个FileGroup管理一个或多个文件，其中PrimaryFileGroup管理一个master data file，扩展名是.mdf，这个file是系统默认生成的；扩展名是.ndf的文件是用户根据需要添加的。

文件组管理着磁盘上的文件，文件中存放着SQL SERVER database的实际数据。

2，示例db

2.1示例db的FileGroup如下

Primary 是一个FileGroup，是个默认的文件组。如果在create table，create index中没有指定FileGroup，将使用Default FileGroup管理的文件来存储实际的数据。

![191655400765316.jpg][]

2，每一个FileGroup都有相应的File，File是实际存储数据的物理实体。

![191700372169436.jpg][]

File有两种类型，一种是存储数据的rows data，一种是存储日志的log。可以从path中找到磁盘上物理file。

File中存储的数据经过sql server的处理，存储的是sql server能够识别的格式化数据。

3，使用文件组管理文件的好处

在实际的开发过程中，user只需要关注逻辑对象FileGroup，而不用关心数据的物理存储，即使物理存储的file变更，也不影响应用程序。如果同一个FileGroup内的文件分布在不同的磁盘上，在读取数据时，Sql Server能同时从不同的磁盘上读取数据，极大的提高IO性能，这种实现方式是分区表实现的基础，分区表能够将不同的数据子集存储在不同的磁盘上。

Tsql 查看database的filegroup和file

select * 
    FROM sys.database_files
    
    select *
    from sys.filegroups

![191728320608562.jpg][]

4，使用文件组的情况

4.1 在创建表的时候，使用on子句指定FileGroup，那么该表的数据将存储在该FileGroup管理的File中。

![复制代码][copycode.gif]

CREATE TABLE [dbo].[student](
        [stuid] [int] NOT NULL,
        [stuname] [varchar](100) NOT NULL,
        [professionid] [int] NULL,PRIMARY KEY CLUSTERED 
        (
            [stuid] ASC
        )
    ) ON [PRIMARY]

![复制代码][copycode.gif]

4.2 在创建索引的时候，使用on子句指定FileGroup，那么该表的索引结构将存储在该FileGroup管理的File中。

CREATE NONCLUSTERED INDEX [idx_student_stuname] 
    ON [dbo].[student]( [stuname] ASC ) 
    ON [PRIMARY]

5, Data file 容量增长导致的问题

假如一个data file已经满了，当一个insert命令执行时，会导致data file增长，如果filegrowth设置的过大，会导致sql server消耗很长的时间来实现文件增长，这样，即使插入一条数据，也要等待很长时间。在data file grow时，该文件是不能访问的。data file grow会导致文件碎片。

数据文件增长是非常耗费系统资源和影响性能，如果设置SQL Server 自动增长，可能会导致系统性能不够稳定，所以，应该预测可能的空间使用需求，并提前做好规划。尽量避免空间用尽而使得SQL Server不得不自动增长的现象发生。同时也要确保每一次自动增长都能够在可接受的时间内完成，及时满足客户端应用的需求。

The growth might take so long that the client application's timeout value is exceeded, which means the insert query fails.

Fast file initialization

You can initialize sql server 2012 data files(but not log files) instantly, allowing for fast execution of the file creation and growth. Instant file initialization adds space to the data file without filling the newly added space with zeros. Instead, the actual disk content is overwritten only as new data is written to the files.

[191459339826696.jpg]: /images/20220617/174603388e5149ab946da0c4d4c8ee92.png
[191655400765316.jpg]: /images/20220617/cbaff52cf82744b4aa99b8464cb1aaf7.png
[191700372169436.jpg]: /images/20220617/d7659117d3ab4561bb6c2046f3ab00cd.png
[191728320608562.jpg]: /images/20220617/c162729466ac4ce59684de75de37bf94.png
[copycode.gif]: /images/20220617/a4020f3349464757ba861d0b1b982f61.png