SQL Server 文件的空间使用

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

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

为了性能,通常为数据库创建多个数据文件,每个文件占用的硬盘空间可能不同。

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

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)。

select db_name(mf.database_id) as db_name
    ,su.file_id
    ,su.filegroup_id
    ,mf.name as file_logic_name
    ,mf.physical_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)为单位,统计数据库的文件上已分配的,未分配的区的数量:

 dbcc showfilestats

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

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

exec sys.sp_spaceused

数据库的空间利用信息:

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

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

数据库中各个表所占用的空间大小是不同的,有些表占用的空间虽大,但是没有真正用于存储数据,而仅仅是处于保留状态,以备不时之需。

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

;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

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

;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
View Code

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

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

exec sys.sp_spaceused 'dbo.dt_study'

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

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

Exec master.sys.xp_fixeddrives

四,压缩文件

在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)

3,清空文件

清空文件,是指把当前文件中的数据转移到同一个FileGroup的其他文件中去,使得当前文件不包含任何数据。

dbcc shrinkfile('filename',emptyfile)

也可以使用emptyfile把数据均匀分布到不同的文件上去。

 

 

参考文档:

sp_spaceused

sys.dm_db_file_space_usage (Transact-SQL)

posted @ 2017-06-28 10:45  悦光阴  阅读(2041)  评论(4编辑  收藏  举报