>

SEHummerH二VE奥迪Q7大话存储结构,文件的空中利用

- 编辑:www.bifa688.com -

SEHummerH二VE奥迪Q7大话存储结构,文件的空中利用

 

数据库占用的存储空间,从高层次来看,可以查看数据库文件(数据文件,日志文件)占用的存储空间,从较细的粒度上来看,分为数据表,索引,分区占用的存储空间。监控数据库对象占用的硬盘空间,包括已分配,未分配,和未使用的空间占比,能够有效地管控存储空间,合理利用,避免部分文件空间不足,而其他文件浪费空间的问题。

 

一,数据库文件占用的存储空间

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

1,查看数据库的各个文件占用的存储空间

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

图片 1图片 2

 

select db.name as database_name,
    db.is_auto_shrink_on,
    db.recovery_model_desc,
    mf.file_id,
    mf.type_desc,
    mf.name as logic_file_name,
    mf.size*8/1024/1024 as size_gb,
    mf.physical_name,
    --mf.max_size,
    mf.growth,
    mf.is_percent_growth,
    mf.state_desc
from sys.databases db 
inner join sys.master_files mf 
    on db.database_id=mf.database_id
where mf.size*8/1024/1024>1  -- GB
order by size_gb desc

View Code


2,查看数据文件中已分配,未分配和混合区的空间

 

视图:sys.dm_db_file_space_usage 以Page为单位,实际上,Page的计数都是在区(Extent)级别上统计的,数据文件不够GAM(Global Allocation Map)和SGAM(Shared Global Allocation Map)。

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

select db_name(mf.database_id) as db_name
    ,su.file_id
    ,su.filegroup_id
    ,mf.name as file_logic_name
    ,su.total_page_count*8/1024/1024 as total_gb
    ,su.allocated_extent_page_count*8/1024/1024 as allocated_gb
    ,su.unallocated_extent_page_count*8/1024/1024 as unallocated_gb
    ,su.mixed_extent_page_count*8/1024 as mixed_mb
from sys.dm_db_file_space_usage su
inner join sys.master_files mf
    on su.database_id=mf.database_id
        and su.file_id=mf.file_id

 

还有一个DBCC命令返回相同的数据,该命令以区(Extent)为单位,统计数据库的文件上已分配的,未分配的区的数量:

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

 dbcc showfilestats

 

该命令从系统page:GAM 和 SGAM 上读取Extent的分配信息


3,使用查看当前数据库的空间使用量


exec sys.sp_spaceused

 

图片 3

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 FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAdbpage_3.ndf' ,
11            SIZE = 51200KB ,
12            FILEGROWTH = 10240KB
13          ) TO FILEGROUP [testfg]
14 GO
15  
16 ALTER DATABASE [dbpage]
17 ADD FILE (
18            NAME = N'dbpage_4',
19            FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAdbpage_4.ndf' ,
20            SIZE = 51200KB ,
21            FILEGROWTH = 10240KB
22          ) TO FILEGROUP [testfg]
23 GO
24  
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 方法一:重建聚集索引,先删除聚集索引,然后再建立新的聚集索引指定到文件组,如上一个SQL
34 方法二:利用表分区,先建立 中间表格,中间表添加分区方案,分区建立在 指定的文件组上,然后再 需要迁移到表格上执行 swith partion,然后重命名表格,最后删除旧表,中间表格的分区脚步这里不涉及
35  
36 ALTER TABLE tbtest SWITCH PARTITION 1 TO tbtest_new PARTITION 1 
37 GO
38  
39 EXEC sp_rename 'tbtest','tbtest_old'
40 EXEC sp_rename 'tbtest_new','tbtest'
41 GO
42  
43 DROP TABLE tbtest_old
44 GO

 

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

1 select
2  
3       fg.name fgname,o.name tbname ,index_id,rows,au.type_desc,au.container_id,au.total_pages,au.used_pages,au.data_pages
4 from sys.partitions p
5 join sys.allocation_units au on p.partition_id=au.container_id
6 join sys.filegroups fg on fg.data_space_id=au.data_space_id
7 join sys.objects o on p.object_id=o.object_id
8 where o.type='u'  and p.object_id=object_id('orders')

    图片 4

图片 5

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

 

 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.name
10 )
11 select
12       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 a
16 group by fg_name

    图片 6

  • **database_size数据文件和日志文件的大小;database_size 会比 reserved   unallocated space的加和大,这是因为 reserved 和 unallocated_space 仅仅是数据文件的大小;**
  • unallocated space :数据文件中的未分配空间虽然占用文件的硬盘空间,但是,没有分配,不能被其他数据库对象使用;
  • reserved:数据文件中的保留空间,是已经分配的硬盘空间,能够被其他数据库对象使用;
  • data:数据占用的硬盘空间;
  • index_size:索引占用的硬盘空间;
  • unused :已经分配,但是未被使用的硬盘空间;

1.2 增长选项

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

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

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

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

    图片 7

4, 统计SQL Server 实例中所有数据库的日志文件的硬盘空间使用

1.3 即时初始化

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

 

    什么是初始化呢?

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

 

    什么是即时初始化呢?

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

    

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

  1. 创建数据库
  2. 向现有数据库中添加文件
  3. 增大现有文件的大小、包括自动增长操作(不含日志文件的自动增长)
  4. 还原数据库或文件组

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

 

    如何开启即时初始化?

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

    查看SQL SERVER引擎的登录用户,如下:

    图片 8

 

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

    图片 9

 

    添加后,需要重启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 GO
10  
11 --创建测试库
12 CREATE DATABASE [xinysu]
13  CONTAINMENT = NONE
14  ON  PRIMARY
15 ( NAME = N'xinysu',
16   FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAxinysu.mdf' ,
17   SIZE = 104857600KB , FILEGROWTH = 204800KB
18 )
19  LOG ON
20 ( NAME = N'xinysu_log',
21   FILENAME = N'D:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVER2012MSSQLDATAxinysu_log.ldf' ,
22   SIZE = 524288KB , FILEGROWTH = 102400KB
23 )
24 GO
25  
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 xinysu
31 GO
32  
33 DBCC TRACEOFF(3004,3605,-1)
34 GO

   

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

dbcc sqlperf(logspace) 返回的结果总是准确的,语句的执行不会增加系统负担

2 DB收缩

dbcc sqlperf(logspace)

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 NOTRUNCATE
11 文件末尾的可用空间不会返回给操作系统,文件的物理大小也不会更改。 因此,指定 NOTRUNCATE 时,文件看起来未收缩。
12 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。
13  
14 TRUNCATEONLY
15 将文件末尾的所有可用空间释放给操作系统,但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。
16 target_size如果使用 TRUNCATEONLY 指定将被忽略。
17 TRUNCATEONLY 选项不会移动日志中的信息,但会删除日志文件末尾的失效 VLF。
18  
19 WITH NO_INFOMSGS
20 取消显示所有信息性消息。
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,收缩到100Mb
31 --重新分配超过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 NOTRUNCATE
11 通过将已分配的区从文件末尾移动到文件前面的未分配区来压缩数据文件中的数据。 target_percent是可选的。
12 文件末尾的可用空间不会返回给操作系统,文件的物理大小也不会更改。 因此,指定 NOTRUNCATE 时,数据库看起来未收缩。
13 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。
14  
15 TRUNCATEONLY
16 将文件末尾的所有可用空间释放给操作系统,但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。 target_percent如果使用 TRUNCATEONLY 指定将被忽略。
17 TRUNCATEONLY 将影响日志文件。 若要仅截断数据文件,请使用 DBCC SHRINKFILE。
18  
19 WITH NO_INFOMSGS
20 取消严重级别从 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

GO

 

sp_spaceused

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

 

dbcc shrinkdatabase ('dbpage',50)

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

 

sp_spaceused

 

图片 10.png)

图片 11.png)

 图片 12

图片 13

图片 14

图片 15.png)

     收缩是指,回收数据库未使用的空间,如果数据文件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]

GO

ALTER DATABASE [databasename] SET AUTO_SHRINK ON WITH NO_WAIT

GO

 

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

图片 16

2.2 原理

    执行的时候,对数据库的每一个文件逐个进行压缩,从文件的末尾开始压缩。数据库引擎将按照 target_percent ,预估出每个文件可以压缩的空间,把文件末尾可压缩的空间上面的区,移动到前面不压缩的空闲区上。

    比如 一个数据库 xinysu,有数据文件及日志文件各一个,其中数据文件有20Mb,实际使用12Mb,如果设置 target_percent 为40%,那么意味这压缩后的 数据文件的 理想大小 = 8Mb/( 1-0.4 ) = 13.3 Mb ,则理想的压缩结果 为 13.3 Mb 。则数据文件末尾的 20Mb-13.3Mb = 6.7Mb 空间上面有实际存储数据的 区,则会移动到 前面13.3Mb空间的空闲区上。

    对于日志文件说来,数据库引擎会尝试收缩每个日志文件到目标大小,但是这里会有个前提,如果虚拟日志中的所有逻辑日志没有超过指定的目标大小,那么文件会正常截断,收缩到指定的目标大小,如果逻辑日志的大小大于指定的大小,那么数据库引擎将会尽可能多的收缩空间的空间,但是收缩情况不如理想状态。

 

    执行收缩前,可以通过sp_spaceused或者 sys.database_files来查看空闲空间,再根据空闲空间来收缩。

    

SELECT

      name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB  

FROM sys.database_files;

 

    也可以通过 dbcc showconfig来查看表格的空间分布情况

 

dbcc showcontig(tbtest)

 

/*

DBCC SHOWCONTIG 正在扫描 'tbtest' 表...

表: 'tbtest' (290100074);索引 ID: 1,数据库 ID: 10

已执行 TABLE 级别的扫描。

- 扫描页数................................: 336367

- 扫描区数..............................: 42175

- 区切换次数..............................: 203346

- 每个区的平均页数........................: 8.0

- 扫描密度 [最佳计数:实际计数].......: 20.68% [42046:203347]

- 逻辑扫描碎片 ..................: 59.42%

- 区扫描碎片 ..................: 68.06%

- 每页的平均可用字节数.....................: 2485.3

- 平均页密度(满).....................: 69.29%

DBCC 执行完毕。如果 DBCC 输出了错误信息,请与系统管理员联系。

*/

 

 

    某些情况下,会遇到,明明数据库文件是有空闲空间的,但是这些空闲的区,分散在每一个区中,而不是完整的有多个空闲的区,那么这个情况下,则无法有效的收缩数据库,因为 DBCC SHRINKFILE做的操作是 基于区操作的,它会把使用过的区前移,但是,它不会合并区合并页,如果数据库中,大多数区都只是使用少量数据页,那么收缩的效果也不会明显的。

     

    收缩对于数据库来说,是一个耗费IO资源以及会增加碎片的操作,不宜过于频繁执行DB 收缩。

    收缩可能会出现几个问题:

  • 文件并没有变小
    • 是否执行的命令含有 NOTRUNCATE
    • 是否指定的大小比实际数据的大小还要小
    • 数据文件没有空闲的区
    • 日志文件中的LSN无法截断,详情查看本系列第6篇
  • 执行时间非常久

    • 某些 基于版本控制隔离级别 的事务 堵塞了 收缩操作,这里会在 errorlog中有记录,可以查看
      • 如果是这个原因堵塞,可以选择停止收缩操作或者停止事务操作或者等待
    • 回收的空间特别大,并且回收的空间上有大量的数据页面需要重分配到前面的空闲数据页面上

二,查看数据库中,各个数据表或索引所占用的硬盘空间

3 空间计算方法和区别

    日志文件的空间统计,可以使用DBCC SQLPERF(LOGSPACE)指令,可以看到整个实例所有数据库的日志使用百分比及日志大小。

    图片 17.png)

     图片 18

    那么,数据文件呢?

    数据库计算数据文件空间使用情况,有好几种方式,这些方式在统计的时候,注意区分是基于区统计的还是基于页面统计的以及执行代价。

1,查看数据库所有数据库表或索引所占用的硬盘空间

3.1 基于区统计

    DBCC SHOWFILESTATS 。

 

    该指令基于区统计数据文件的使用情况,从GAM和SGAM页面读取对区的分配信息,计算整个数据文件中有多少区被分配。所以只需要读取数据文件中的GAM和SGAM页面内容既可以统计,方便快速,不会增加系统的额外负担。

图片 19.png)

    图片 20

    注意,这里显示的是使用的区情况,所以,如果需要转换成kb,一个区8个数据页面,一个数据页面有8kb,文件名为 dbpage_3的文件大小为 800*8*8kb=51200kb=50Mb。

;with cte_space as 
(
    select 
        ps.object_id
        ,sum(case when ps.index_id<2 then ps.row_count else 0 end) as row_count
        ,sum(ps.reserved_page_count)*8/1024/1024 as reserved_gb
        ,sum(ps.used_page_count)*8/1024/1024 as used_gb
        ,sum(case when ps.index_id<2
                    then ps.in_row_data_page_count ps.lob_used_page_count ps.row_overflow_used_page_count
                  else 0 end
            )*8/1024/1024 as data_used_gb
    from sys.dm_db_partition_stats ps with(nolock)
    inner join sys.tables t  with(nolock)
        on ps.object_id=t.object_id
    group by ps.object_id
)
select object_schema_name(s.object_id) '.' object_name(s.object_id) as table_name
    ,s.row_count
    ,s.reserved_gb
    ,s.used_gb
    ,s.data_used_gb
    ,s.used_gb-s.data_used_gb as index_used_gb
    ,s.reserved_gb-s.used_gb as unused_gb
from cte_space s
where s.reserved_gb>1  -- more than 512MB
order by used_gb desc
    ,index_used_gb desc

3.2 基于页面统计

2,以索引为单位,查看数据表上各个索引占用的硬盘空间

3.2.1 sp_spaceused

    exec sp_spaceused [objectname, updateusage]。

 

    有两个可选参数,可以不选择也可以任意选择1-2个,objectname 默认为空, 如果不指定objectname,则是统计当前数据库的页面使用情况,如果指定,则是只统计某一个对象;updateusage 默认为 FALSE,如果指定 updateusage=TRUE,则在执行前,对当前数据库或者指定的 object 执行 DBCC UPDATEUSAGE with no_infomsgs,执行结束 UPDATEUSAGE后,再执行 sp_spaceused 。

 

    DBCC UPDATEUSAGE with no_infomsgs 会对数据库做什么操作呢?

    它会针对表或索引中的每个分区更正行、已用页、保留页、叶级页和数据页的计数, 如果系统表中没有错误,则 DBCC UPDATEUSAGE 不返回数据。 如果发现错误,并对其进行了更正,同时返回系统表中更新的行和列。由此可见,对于整个数据库或者某个大表执行 UPDATEUSAGE ,由于需要完整统计表格的页面使用情况,所以会耗费一定量的IO资源,对性能有一定影响。

    所以,一般情况下,执行sp_spaceused来统计空间使用情况时,不建议设置 updateusage=TRUE,除非怀疑 sp_spaceused的输出结果有误。

 

    sp_spaceused是如何来统计数据文件使用情况呢?

    查看 系统存储过程 sp_spaceused 的SQL代码,可以看到无论是基于库统计还是基于表格统计,都是通过这三个 sys.dm_db_partition_stats,sys.internal_tables ,sys.partitions 动态管理视图来查看的。而这几张视图的数据并非是实时更新,所以,有时候,在对表格做索引删除或新增或者大量删除表格数据后,执行sp_spaceused可能不是很准确,这个时候,就需要设置选项 updateusage=TRUE。

 

    测试结果如下:

    图片 21

 

    其实,通过原理及测试情况,发现,sp_spaceused其实就是一个简易版的查询放到了存储过程中使用,但是由于其参数的限制,要不是分析整个数据库,要不是只能够分析某一个表格,无法批量分析,这个多多少少有些不方便,如果需要分析多个表格,则可以通过DMV视图来实现,详见下一小节。

图片 22图片 23

3.2.2 sys.dm_db_partition_status

    动态视图的查询,其实跟sp_spaceused不加updateusage选项的原理是一样的,不过这个查询可以动态调整,方便一些。

     查询SQL如下,根据所需动态调整即可。

 

 1 select
 2       o.name,
 3       sum(case when (p.index_id<2) then row_count end) rows,
 4       sum(p.reserved_page_count)*8 reseved_kb,
 5          sum(p.reserved_page_count-p.used_page_count)*8 unused_kb,
 6          sum(p.used_page_count)*8 used_kb,       
 7          sum(case when (p.index_id<2) then (p.in_row_data_page_count p.lob_used_page_count p.row_overflow_used_page_count)
 8                   else p.lob_used_page_count p.row_overflow_used_page_count end
 9              )*8 data_kb,
10          sum(p.used_page_count-(case when (p.index_id<2) then (p.in_row_data_page_count p.lob_used_page_count p.row_overflow_used_page_count)
11                                      else p.lob_used_page_count p.row_overflow_used_page_count end)
12           )*8 index_kb
13 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id=o.object_id
14 where o.type='u'
15       and o.name in ('orders','tba','tb_clu_no_unique')
16 group by o.name
17 order by o.name

图片 24

;with cte_space as 
(
    select 
        ps.object_id
        ,ps.index_id
        ,sum(ps.row_count) as row_count
        ,sum(ps.reserved_page_count)*8/1024 as reserved_mb
        ,sum(ps.used_page_count)*8/1024 as used_mb
        ,sum(ps.in_row_data_page_count ps.lob_used_page_count ps.row_overflow_used_page_count)*8/1024 as data_used_mb
        ,sum(ps.in_row_data_page_count)*8/1024 as in_row_data_used_mb
        ,sum(ps.lob_used_page_count ps.row_overflow_used_page_count)*8/1024 as max_data_used_mb
    from sys.dm_db_partition_stats ps
    inner join sys.tables t 
        on ps.object_id=t.object_id
    group by ps.object_id
        ,ps.index_id
)
select object_schema_name(s.object_id) '.' object_name(s.object_id) as table_name
    ,i.index_id
    ,i.name as index_name
    ,s.row_count
    ,s.reserved_mb
    ,s.used_mb
    ,s.data_used_mb
    ,s.in_row_data_used_mb
    ,s.max_data_used_mb
    ,s.used_mb-s.data_used_mb as index_used_mb
    ,s.reserved_mb-s.used_mb as unsed_mb
from cte_space s
inner join sys.indexes i
    on s.object_id=i.object_id
        and s.index_id=i.index_id
where s.reserved_mb>512  -- more than 512MB
    --and i.index_id>1
order by unsed_mb desc
    ,index_used_mb desc

3.2.3 DBCC SHOWCONTIG

    这个指令在检查数据库碎片的时候,经常使用到。 用于显示指定的表或视图的数据和索引的碎片信息。这个指令在不指定FAST的情况下,可以说是最精确的统计方式了,细致到某个表格用了多少页,页面上的数据使用情况,碎片率如何,每次统计的时候,都会扫描这个表格的涉及到数据页,扫描过程会对逐个页面加锁然后释放,故在业务高峰期的时候,慎用,一方面是IO资源占用,另一方面是锁影响。

 

DBCC SHOWCONTIG

[ (

    { table_name | table_id | view_name | view_id }

    [ , index_name | index_id ]

) ]

    [ WITH

        {

         [ , [ ALL_INDEXES ] ]

         [ , [ TABLERESULTS ] ]

         [ , [ FAST ] ]

         [ , [ ALL_LEVELS ] ]

         [ NO_INFOMSGS ]

         }

    ]

 

--语法说明

table_name,table_id,view_name,view_id

可以指定object_name检查,或者object_id来查看;如果不指定任意对象,则说明是检查当前整个数据库的所有表格

 

index_name,index_id

指定对象后,指定某个索引进行分析;如果不指定,则是按照聚集索引分析,没有聚集索引则是全表分析;

 

ALL_INDEXES

分析扫描所有索引,包含非聚集索引

 

TABLERESULTS

返回结果按照表格显示

 

FAST

指定是否要对索引执行快速扫描和输出最少信息。 快速扫描不读取索引的叶级或数据级页。

 

ALL_LEVELS

仅为保持向后兼容性而保留

 

NO_INFOMSGS

取消严重级别从 0 到 10 的所有信息性消息。

 

    测试聚集索引表格tbindex,含2个非聚集索引。测试情况如下:

 

DBCC SHOWCONTIG (tbindex)

 

DBCC SHOWCONTIG (tbindex,ix_number_name)

 

DBCC SHOWCONTIG (tbindex)  WITH TABLERESULTS,ALL_INDEXES

 

DBCC SHOWCONTIG (tbindex)  WITH TABLERESULTS,ALL_INDEXES,FAST

 

图片 25.png)

 图片 26

    总体来说,如果是基于整个数据文件来看空间使用情况,DBCC SHOWFILESTATS是首选;如果需要动态查询表格的空间使用情况,可以使用DMV sys.dm_db_partition_status ;如果需要非常全面的分析表格的空间情况、碎片情况,则是用DBCC SHOWCONTIG。各个指令使用时,需明确其性能影响及准确性。

    总结空间计算的指令,说明如下:

图片 27.png)

    图片 28

 

 

参考文档:

《SQL SERVER 2012实施与管理实战指南》第8章

 

View Code

3,在当前DB中,查看某一个数据表的空间使用信息

该存储过程用于查看当前数据库数据表的空间使用,返回的结果并不精确,默认情况下,该存储过程底层使用系统视图 sys.allocation_units 和 sys.partitions 获取数据表的所占用空间的“近似”信息。当索引被删除、索引被重建、或者大表被删除(drop,truncate)时,数据库引擎会延迟Page的释放,延迟删除导致不会立即释放已分配的空间,在这种情况下,该存储过程不会立即返回精确的空间使用信息。

exec sys.sp_spaceused 'dbo.dt_study'

图片 29

  • rows::数据表的总行数;
  • reserved:数据文件中已分配的空间;
  • data:数据文件中,基础表占用的空间;
  • index_size:数据文件中,索引占用的空间;
  • unused:数据文件中,已分配,但是为被数据库对象(基础表和索引)使用的空间;

三,查看服务器各个逻辑盘符剩余的硬盘空间

Exec master.sys.xp_fixeddrives

图片 30

四,压缩文件

在SQL Server中,使用 DBCC ShrinkFile命令压缩数据库文件(数据文件和日志文件),或直接把数据库文件清空。除非硬盘空间不足,不要轻易收缩数据库的文件,这会打乱索引的物理顺序,大幅增加的索引外部碎片,影响查询性能。

在执行DBCC ShrinkFile命令,收缩数据文件的时候,数据库引擎首先把文件尾部的区(Extent)移动到文件的开头,然后释放文件末尾的空闲空间,归还给操作系统。在移动Page时,数据库引擎会扫描数据文件并对正在读取的页面加锁,对数据库的性能会有所影响。但是收缩操作不是一个独占行为,其他用户仍然可以对数据库进行读写操作。在进程中的任意一个时间点停止文件收缩操作,任何已经完成的工作都将保留。

收缩文件以区为单位,它会把文件末尾已分配的区前移,把未分配的区从文件末尾移除。该命令不会把一个区里面的空闲页面(empty page)移除,也不会合并区以释放空闲页面,如果数据库中有很多只使用了一两个页面的区,DBCC SHRINKFILE的效果会不明显。

DBCC SHRINKFILE ( { file_name | file_id }, EMPTYFILE )
DBCC SHRINKFILE ( { file_name | file_id }, target_size , { NOTRUNCATE | TRUNCATEONLY } )

1,参数说明

target_size :是整数类型,单位是MB,数据库引擎尝试把文件收缩到指定的大小(Size),但不会收缩到小于数据实际存储必需的空间。只有和参数NOTRUNCATE搭配使用,才起作用。

EMPTYFILE :把数据从指定的文件迁移到同一个文件组中的其他文件中,也就是说,把当前文件清空,把数据转存到其他文件中,数据的迁移只能在同一个文件组的不同文件之间进行。数据文件被清空之后,数据库引擎不会把数据存储到空文件中,可以使用 ALTER DATABASE 把文件从数据库中移除。

NOTRUNCATE:只用于数据文件,对日志文件不起作用;该参数用于把已分配(Allocated)的区(Extent)从数据文件的末尾移动到数据文件开头的未分配(Unallocated)的空间中,文件末尾被释放的空间不会返回给操作系统,仍然存在于文件中,处于未分配状态,这意味着数据库文件占用的存储空间不变。和target_size参数一起使用,用于指定文件收缩的大小。由于区(Extent)的移动是IO密集型操作,会影响数据库的IO性能。

TRUNCATEONLY:把文件末尾的所有空闲空间都释放,返回给操作系统,该参数不会执行任何的Page移动,也就是说,该参数收缩数据库文件,而忽略target_size参数,收缩的硬盘空间以区(Extent)为单位。

2,收缩文件示例

想要收缩数据库文件,释放硬盘空间,需要分两步:先移动,后释放

Step1,将文件末尾已分配的区(extent)向前移动,移动到文件前端未被分配的区中,移动的区被标记为未分配(Unallocated)

dbcc shrinkfile('filename',0,notruncate)

Step2,将文件末尾的空闲空间(以区为单位)释放,归还给操作

dbcc shrinkfile('filename',target_size_MB,truncateonly)

五,IO请求的等待和挂起

数据库引擎记录对数据文件和日志文件的IO操作,缓存到函数:sys.dm_io_virtual_file_stats,对于数据文件,数据的物理读操作更为重要;对于日志文件,数据的读写操作都重要:

  • io_stall_read_ms:等待读操作的时间
  • io_stall_write_ms:等待写操作的时间

如果硬盘繁忙,数据库引擎发送的IO请求,可能会被IO子系统挂起(pending),数据库引擎把pending的IO请求缓存到视图:sys.dm_io_pending_io_requests,

  • io_pending:指定是否有IO请求挂起或完成

1,查看数据库文件的IO和等待IO完成的时间

图片 31图片 32

select db_name(vfs.database_id) as db_name,
    --vfs.file_id,
    mf.name as file_name,
    mf.type_desc as file_type,
    vfs.sample_ms/1000/60/60 as sample_h,
    vfs.io_stall_read_ms/vfs.num_of_reads as avg_stall_read_ms,
    vfs.io_stall_write_ms/vfs.num_of_writes as avg_stall_write_ms,

    vfs.num_of_reads as physical_reads,
    vfs.num_of_bytes_read/vfs.num_of_reads/1024 as avg_read_kb,
    vfs.num_of_writes as physical_writes,
    vfs.num_of_bytes_written/vfs.num_of_writes/1024 as avg_written_kb,
    cast(vfs.size_on_disk_bytes/1024/1024/1024.0 as decimal(10,2)) as disk_size_gb,
    --cast(mf.size/1024*8/1024.0 as decimal(10,2)) as file_size_gb,
    vfs.file_handle
from sys.master_files mf 
cross apply sys.dm_io_virtual_file_stats(mf.database_id,mf.file_id) as vfs
where mf.database_id=db_id()  --current db
order by avg_stall_read_ms desc ,avg_stall_write_ms desc

View Code

2,查看pending的IO请求

图片 33图片 34

select db_name(vfs.database_id) as db_name,
    --vfs.file_id,
    mf.name as file_name,
    pr.io_type,
    sum(pr.io_pending_ms_ticks) as io_pending_ms,
    pr.io_pending
from sys.dm_io_virtual_file_stats(null,null) vfs
inner join sys.dm_io_pending_io_requests as pr
    on vfs.file_handle=pr.io_handle
inner join sys.master_files mf
    on vfs.database_id=mf.database_id
        and vfs.file_id=mf.file_id
group by vfs.database_id,
    mf.file_id,
    mf.name,
    pr.io_type,
    pr.io_pending
order by vfs.database_id,
    mf.name

View Code

 

参考文档:

sp_spaceused.aspx)

sys.dm_db_file_space_usage (Transact-SQL).aspx)

sys.dm_db_partition_stats (Transact-SQL).aspx)

本文由88bifa必发唯一官网发布,转载请注明来源:SEHummerH二VE奥迪Q7大话存储结构,文件的空中利用