SQL SERVER大话存储结构（6）_数据库数据文件

发布时间：2017-06-23 15:32:24.657 作者： IT网络文摘 (该文来自笔记，点击查看原文)

数据库文件有两大类：数据文件跟日志文件，每一个数据库至少各有一个数据文件或者日志文件，数据文件用来存储数据，日志文件用来存储数据库的事务修改情况，可用于恢复数据库使用。

这里分为两篇博文讲解，本文来说说数据文件。

如果转载，请注明博文来源： www.cnblogs.com/xinysu/ ，版权归博客园苏家小萝卜所有。望各位支持！

本系列上一篇博文链接：SQL SERVER大话存储结构（5）_SQL SERVER 事务日志解析

回到顶部(go to top)

1 创建数据文件时，在考虑什么

1.1 数据文件与文件组

数据文件有两类，一类是主数据文件，一类是辅助数据文件。

每一个数据库都有一个主数据文件数据文件用来存储数据，扩展名是 mdf。

一个数据库可以有0到多个的辅助数据文件，扩展名是 ndf。

文件组这个概念，可能大多数人只有在涉及表分区的时候有了解过。

顾名思义，文件组，就是给数据文件分为多个组，方便分配磁盘IO资源以及运维管理。每个数据库至少有一个文件组，含数据库主数据文件的组称之为主文件组，一般不指定文件组名创建表格或者索引，则会默认把数据文件放在主文件组中，因为默认主文件组就是默认文件组，当然，也可以通过ALTER 语句来修改默认文件组为其他文件组，这样，创建数据文件但不指定文件组时，则存放到设置的默认文件组中。

这里有个注意事项：数据库中的大多数操作都是仅针对于文件组操作，比如创建索引或者创建表格。

那么，什么情况下需要单文件，什么情况下有需要多个辅助数据文件呢？

建立表格及索引时，只能指定到某一个文件组，不能够指定到这个文件组的某个文件
同一个文件组内的数据文件，起到一个平摊分布数据的作用，如果是位于不同的驱动器，则有利于提高并发IO，如果是位于同一个驱动器，则有利于后期的运维管理；
当使用表分区的时候，每一个分区会使用到一个辅助数据文件（可以同一个驱动器，也可以不同）
大库的灵活运维管理，其实呢，如果在同一个驱动器上建立多个数据文件，对IO性能并没有任何改善，但是，却为后期的管理提供了方便性，尤其是大库管理，比如线上数据库损坏，需要还原出来一个新的数据文件，或者是测试环境的搭建等等，很多时候会遇到剩余的磁盘空间并不足以来存放这个大库，但是如果是多个数据文件，那么就可以分开指定驱动器存储，减少磁盘大小的要求。

那么，什么情况下，会使用到多个文件组呢？

使用表分区
当磁盘IO资源出现瓶颈的情况下，可以考虑迁移部分热表到其他文件组的文件上（不同驱动器），分散IO；
当磁盘空间不足但是想把文件中的冷表（类似与记录登录日志）的表格，迁移到其他驱动器上，可以考虑使用文件组；
历史数据和热数据分开，历史归档数据损坏，不影响热数据；
大库的灵活运维管理，可以使用文件组来备份数据库的一部分，比如某些特定的表格放在辅助数据文件上，出事故后，还原的时候，可以对数据库进行部分还原，主文件组还原结束，即可提供服务，但在其他文件组上的对象暂时不能使用，等到其他文件组也还原结束，其存储的数据才能提供服务。

如何新增文件或者文件组呢？如何迁移表格数据到新的文件组呢？

 1 --案例 1 ：给数据库 dbpage新增 文件组 testfg，并在这个文件组内建立辅助数据文件 dbpage_3，dbpage_4 2 USE [master] 3 GO 4 ALTER DATABASE [dbpage] ADD FILEGROUP [testfg] 5 GO 6   7 ALTER DATABASE [dbpage] 8 ADD FILE ( 9            NAME = N'dbpage_3',10            FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\dbpage_3.ndf' ,11            SIZE = 51200KB ,12            FILEGROWTH = 10240KB13          ) TO FILEGROUP [testfg]14 GO15  16 ALTER DATABASE [dbpage]17 ADD FILE (18            NAME = N'dbpage_4',19            FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\dbpage_4.ndf' ,20            SIZE = 51200KB ,21            FILEGROWTH = 10240KB22          ) TO FILEGROUP [testfg]23 GO24  25 --案例 2 ：指定文件组创建表格26 CREATE TABLE tbtest(id int not null,name varchar(10) not null) on [testfg]27  28 --案例 3 ：迁移表到其他文件组29 --表无聚集索引，通过建立聚集索引，把整个表格迁移到 指定文件组30 alter table tbtest add constraint pk_tbtest primary key (id) on [testfg]31  32 --表有聚集索引33 方法一：重建聚集索引，先删除聚集索引，然后再建立新的聚集索引指定到文件组，如上一个SQL34 方法二：利用表分区，先建立 中间表格，中间表添加分区方案，分区建立在 指定的文件组上，然后再 需要迁移到表格上执行 swith partion，然后重命名表格，最后删除旧表，中间表格的分区脚步这里不涉及35  36 ALTER TABLE tbtest SWITCH PARTITION 1 TO tbtest_new PARTITION 1 37 GO38  39 EXEC sp_rename 'tbtest','tbtest_old'40 EXEC sp_rename 'tbtest_new','tbtest'41 GO42  43 DROP TABLE tbtest_old44 GO

检查某个表格在文件组的分布

1 select2  3       fg.name fgname,o.name tbname ,index_id,rows,au.type_desc,au.container_id,au.total_pages,au.used_pages,au.data_pages4 from sys.partitions p5 join sys.allocation_units au on p.partition_id=au.container_id6 join sys.filegroups fg on fg.data_space_id=au.data_space_id7 join sys.objects o on p.object_id=o.object_id8 where o.type='u'  and p.object_id=object_id('orders')

seo优化培训,网络推广培训,网络营销培训,SEM培训,网络优化,在线营销培训

检查每个文件组一共有多少个表格

 1 with data as( 2       select 3             fg.name fg_name, o.name tbname 4       from sys.partitions p 5          join sys.allocation_units au on p.partition_id=au.container_id 6          join sys.objects o on p.object_id=o.object_id 7          join sys.filegroups fg on fg.data_space_id=au.data_space_id 8          where o.type='u' 9       group by o.name,fg.name10 )11 select12       a.fg_name,13          count(*) tbcount,14          tbnames=stuff((select ','+b.tbname from data b where a.fg_name=b.fg_name order by tbname for xml path('')),1,1,'')15 from data a16 group by fg_name

seo优化培训,网络推广培训,网络营销培训,SEM培训,网络优化,在线营销培训

1.2 增长选项

设置数据库文件的似乎，需要判断是否启用自动增长，如果启用，是采用百分比增长还是按指定大小增长，是否设置文件最大大小。

首先，在线上业务，建议是：不设置文件的最大大小，避免某些业务数据异常增长导致空间不足，当然，这个设置的前提是，做了磁盘剩余空间监控及报警。

既然不设置最大文件大小，那么就需要设置自动增长，但是，注意，建议在数据库最开始的时候，就设置足够大的空间，避免频繁自动增长，每次自动增长都会在增长期间影响到数据库的IO性能，从而影响数据库的使用，所以建议在最开始的时候，设置足够大的空间，如果后面发现文件自动增长比较频繁，可以找一个业务低峰期，再扩大数据文件，设置文件增长大小，建议不要设置为百分比，避免数据库太大，按百分比，一次增长太大，导致增长影响时间加长，监建议设置为指定大小，可以在200M左右，实际可根据磁盘性能及增长情况来调整。

增长的调整，可以通过指定选项FILEGROWTH ，设置百分比 FILEGROWTH = 10%或者设置指定大小 FILEGROWTH = 204800KB，或者通过界面操作。

seo优化培训,网络推广培训,网络营销培训,SEM培训,网络优化,在线营销培训

1.3 即时初始化

说起文件增长，这里要提一个至关重要的 windows 系统参数配置：即时初始化（Instant File Initialization）。

什么是初始化呢？

当服务需要申请存储空间来使用时，操作系统需要用零来填充空间，填充结束则完成初始化操作，但是，如果申请的空间比较大时，会耗费非常久的时间。

什么是即时初始化呢？

即时文件初始化功能将回收使用的磁盘空间，而无需使用零填充空间，直接跳过了零填充的过程，新数据写入文件时会覆盖磁盘内容。如果SQL SERVER服务登录用户开启了即时初始化，那么就可以瞬间完成对数据文件的初始化，注意，日志文件不能立即初始化。

SQL SERVER 中哪些操作可以即时初始化？

创建数据库
向现有数据库中添加文件
增大现有文件的大小、包括自动增长操作（不含日志文件的自动增长）
还原数据库或文件组

就拿建立数据库来说，不设置即时初始化文时，创建一个100G的数据库需要接近6min，但是开启了即时初始化后，仅需要3秒。更好的应用是在自动增长这块，能有效减少自动增长的时间，从而大大减少自动增长的影响时间。

如何开启即时初始化？

查看SQLSERVER引擎的登录用户->给该用户添加 ' 执行卷维护服务 ' -> 重启SQLSERVER服务。

查看SQL SERVER引擎的登录用户，如下：

seo优化培训,网络推广培训,网络营销培训,SEM培训,网络优化,在线营销培训

打开 `管理工具`，点击 `本地安全策略` ，按下图找到 `执行卷维护任务` ，双击后选择添加 SQL server 服务的登录用户，然后点击 `应用` 即可。

seo优化培训,网络推广培训,网络营销培训,SEM培训,网络优化,在线营销培训

添加后，需要重启SQL SERVER服务，使其加载该权限。所以，建议在数据库一开始安装的时候，就配置好该权限，或者在数据库宕机或者维护期间，做该操作。

如何检查是否开启即时初始化？

检查创建DB的时候，是否直接跳过零填充的过程。

 1 /* 2 以全局方式打开跟踪标记 3004 和 3605。 3 3004：查看SQL Server对日志文件进行填零初始化的过程 4 3605：要求DBCC的输出放到SQL server ERROR LOG 5   -1：以全局方式打开指定的跟踪标记。 6 */ 7   8 DBCC TRACEON(3004,3605,-1) 9 GO10  11 --创建测试库12 CREATE DATABASE [xinysu]13  CONTAINMENT = NONE14  ON  PRIMARY15 ( NAME = N'xinysu',16   FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\xinysu.mdf' ,17   SIZE = 104857600KB , FILEGROWTH = 204800KB18 )19  LOG ON20 ( NAME = N'xinysu_log',21   FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\xinysu_log.ldf' ,22   SIZE = 524288KB , FILEGROWTH = 102400KB23 )24 GO25  26 --查看错误日志27 Exec xp_readerrorlog 0,1,Null,Null,'2017-05-29 10:28:00','2017-05-29 10:30:00'28  29 --删除测试库30 DROP DATABASE xinysu31 GO32  33 DBCC TRACEOFF(3004,3605,-1)34 GO

可以看到，创建数据库xinysu，数据文件100G，日志文件512Mb，都是直接跳过零填充的过程，速度非常快。

回到顶部(go to top)

2 DB收缩

2.1 指令及设置

执行收缩的指令有两种：shrinkfile跟shrinkdatabase。一个是指定某个文件进行压缩，一个是指定某个数据库，对数据库下的所有文件进行压缩。

Shrinkfile的指令如下：

 1 DBCC SHRINKFILE  ( { file_name | file_id } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]   }  )  [ WITH NO_INFOMSGS ] 2   3 /* 4 target_size 5 用兆字节表示的文件大小（用整数表示）。 如果未指定，则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。 默认大小为创建文件时指定的大小。如果target_size指定，DBCC SHRINKFILE 尝试将文件收缩到指定的大小。 将要释放的文件部分中的已使用页重新定位到保留的文件部分中的可用空间。 
 6   7 EMPTYFILE 8 将所有数据从指定的文件都迁移到其他文件相同的文件组。 换而言之，清空文件将迁移数据，从指定的文件到同一个文件组中的其他文件。 清空文件可确保你没有新数据将添加到文件。可以通过删除该文件ALTER DATABASE语句。 9  10 NOTRUNCATE11 文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。 因此，指定 NOTRUNCATE 时，文件看起来未收缩。12 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。13  14 TRUNCATEONLY15 将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。16 target_size如果使用 TRUNCATEONLY 指定将被忽略。17 TRUNCATEONLY 选项不会移动日志中的信息，但会删除日志文件末尾的失效 VLF。18  19 WITH NO_INFOMSGS20 取消显示所有信息性消息。21 */22  23 --举例说明24 DBCC SHRINKFILE ( dbpage_data, 100 )25  26 DBCC SHRINKFILE ( dbpage_data, EMPTYFILE)27 --清空 dbpage_data 数据文件上面的所有内容28  29 DBCC SHRINKFILE ( dbpage_data, 100 ,NOTRUNCATE)30 --收缩数据库 datapage的数据文件，文件名师 dbpage_data，收缩到100Mb31 --重新分配超过100Mb的数据行到前面100Mb未分配的区，保留空闲空间32  33 DBCC SHRINKFILE ( dbpage_data, TRUNCATEONLY)34 --收缩数据库 datapage的数据文件，文件名是 dbpage_data，文件末尾未使用的空间释放给操作系统，不会重新分配数据行到未分配的区35

Shrinkdatabase指令使用如下：

 1 DBCC SHRINKDATABASE ( database_name | database_id | 0  [ , target_percent ]  [ , { NOTRUNCATE | TRUNCATEONLY  } ] ) [ WITH NO_INFOMSGS ]   2   3 /* 4 database_name | database_id | 0  
 5 要收缩的数据库的名称或 ID。 如果指定 0，则使用当前数据库。 6   7 target_percent 8 数据库收缩后的数据库文件中所需的剩余可用空间百分比。 9  10 NOTRUNCATE11 通过将已分配的区从文件末尾移动到文件前面的未分配区来压缩数据文件中的数据。 target_percent是可选的。12 文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。 因此，指定 NOTRUNCATE 时，数据库看起来未收缩。13 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。14  15 TRUNCATEONLY16 将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。 target_percent如果使用 TRUNCATEONLY 指定将被忽略。17 TRUNCATEONLY 将影响日志文件。 若要仅截断数据文件，请使用 DBCC SHRINKFILE。18  19 WITH NO_INFOMSGS20 取消严重级别从 0 到 10 的所有信息性消息。21 */22  23 --举例说明24 DBCC SHRINKDATABASE (dbpage, 20)25 --对数据库dbpage执行收缩处理，其中收缩后空闲空间占整个数据库大小的 20%26 --等同于先执行 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE) ，再执行DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)27  28 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE)29 --对数据库dbpage执行收缩处理，其中收缩后空闲空间占整个数据库大小的 20%30 --数据文件，分配文件末尾的区到文件前面未分配的区，压缩空间不会返回给操作系统，文件大小不变31  32 DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)33 --对数据库dbpage执行收缩处理，但是收缩的空间不一定是 20%34 --收缩的空间是文件末尾的可用空间，也就是 target_percent 在这里指定了也没有用35 --日志文件跟数据文件，释放文件末尾的可用空间给系统文件，但是文件内不执行任何数据页移动

测试数据库 dbpage，先查看数据库的空间分布情况，再收缩数据库，使得收缩后的数据，剩余的空余空间占整个数据库的50%。

use dbpage

sp_spaceused

--数据库总大小 58M，其中未使用的空间有 40Mb

dbcc shrinkdatabase ('dbpage',50)

--执行收缩后，返回数据文件占用2512个数据页，实际使用1248个数据页，预估可以再收缩1248个数据页；日志文件占用288个数据页。

sp_spaceused

seo优化培训,网络推广培训,网络营销培训,SEM培训,网络优化,在线营销培训

收缩是指，回收数据库未使用的空间，如果数据文件20M，但是实际大小只有10M，那么DBCC SHRINKFILE 指定target_size=15M，则是把最后5Mb上面实际存储的数据内容重新分配到前面15Mb中未分配的区中，注意，DBCC SHRINKFILE 不会将文件收缩到小于存储文件中的数据所需要的大小。例如，数据文件实际使用 7 MB ，但占用 10 MB ，执行 DBCC SHRINKFILE 语句target_size的 6 时，将文件收缩到仅 7 MB，不是 6 MB。对于dbcc shrinkdatabase，也是跟以上的注意事项一样。

设置有自动收缩跟手动收缩两种。

自动收缩，可在数据库的`属性` 设置，把自动收缩设置为true，也可以执行命令如下：

USE [master]

ALTER DATABASE [databasename] SET AUTO_SHRINK ON WITH NO_WAIT

自动收缩，其运行结果等同于执行 dbcc shrinkdatabase(dbname,25)，及在数据库中保留25%的自由空间，其他剩余空间回收，每30分钟检查一次来收缩数据。

2.2 原理

执行的时候，对数据库的每一个文件逐个进行压缩，从文件的末尾开始压缩。数据库引擎将按

http://www.cnblogs.com/xinysu/p/6949867.html

分类导航

SQL SERVER大话存储结构（6）_数据库数据文件

1 创建数据文件时，在考虑什么

1.1 数据文件与文件组

1.2 增长选项

1.3 即时初始化

2 DB收缩

2.1 指令及设置

2.2 原理

延伸阅读

我想了解如何学习