4种查看数据空间使用情况的方法

客官°小女子只卖身不卖艺 2022-06-14 06:12 222阅读 0赞

--4种 查看数据空间使用情况
    1、exec SP_Spaceused
    2、
    dbcc showfilestats--以Extent为单位，统计当前数据库下所有数据文件里有多少个Extent，其中有多少个被使用过了。一个Extent是64K。乘一下，就能得到每个文件的使用情况
    dbcc sqlperf(logspace)--返回SQL里所有数据库的日志文件当前使用量
    3、--查看哪个表占用的数据空间最大
    SELECT o.name ,
         SUM (p.reserved_page_count) as reserved_page_count,
         SUM (p.used_page_count) as used_page_count,
         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)
                                        ELSE p.lob_used_page_count + p.row_overflow_used_page_count
                        END
                        ) as DataPages,
         SUM (
                        CASE
                                        WHEN (p.index_id < 2) THEN row_count
                                        ELSE 0
                        END
                        ) as rowCounts
    FROM sys.dm_db_partition_stats p inner join sys.objects o
    on p.object_id = o.object_id
    group by o.name
    
    /*
    输出结果的第一列是每个表的名字。SQL Server在使用数据页的时候，为了提高速度，会先把一些页面一次预留”reserve”给表格，然后真正有数据插入的时候，再使用。
    所以这里有两列，Reserved_page_count和Used_page_count。两列的结果相差一般不会很多。所以粗略来讲，Reserved_page_count*8K，就是这张表格占用的空间大小。
    DataPages是这张表数据本身占有的空间。因此，（Used_page_count – DataPages）就是索引所占有的空间。索引的个数越多，需要的空间也会越多。
    RowCounts，是现在这个表里有多少行数据。
    */
    
    或者
    
    SELECT name, data_space_id 文件组id, size/128 [文件大小(兆)], 
        FILEPROPERTY(name, 'SpaceUsed')/128 [已用空间(兆)],
        size/128 - FILEPROPERTY(name, 'SpaceUsed')/128 [未用空间(兆)],
        FILEPROPERTY(name, 'SpaceUsed')*100.0/size [使用率(%)],
        max_size/128 [最大值(兆)], 
        case is_percent_growth when 0 then cast(growth/128 as nvarchar) + '兆' else cast(growth as nvarchar) + '%' end 增长值,
        physical_name 物理路径
    FROM sys.database_files a  ORDER BY a.[name]
    
    
    4、
    DBCC SHOWCONTIG('table_name')--可以查看每个page，每个extent的使用情况、碎片程度
    或者
    sys.dm_db_index_physical_stats
    
    --会影响性能，在非高峰时期使用 建议
    /*是最精确的方法，可以告诉我们某张表（或索引）用了多少页面，多少区，甚至页面上的平均数据量。从这些值可以算出一张表格占用了多少空间*/
    
    select * from sys.dm_os_buffer_descriptors---缓存池中数据页的分布即谁吃掉了 BUFFER POOL