查询数据（日志）空间的使用情况

傷城~ 2022-06-16 12:07 220阅读 0赞

dbcc showfilestats
    命令会以Extent为单位，统计当前数据库下所有数据文件里有多少个Extent，其中有多少个被使用过了。一个Extent是64K。乘一下，就能得到每个文件的使用情况
    
    dbcc sqlperf(logspace)
    它返回SQL里所有数据库的日志文件当前使用量
    
    如果管理者只需要看数据文件的整体使用情况，dbcc showfilestats是比较好的选择。
    如果要看每个对象的空间使用情况，可以使用动态管理视图sys.dm_db_partition_stats。
    如果还想了解每个page，每个extent的使用情况、碎片程度，那Dbcc showcontig是比较好的选择。
    DBCC SHOWCONTIG
    select * from sys.dm_db_index_physical_stats
    --
    /*
    输出结果的第一列(name)是每个表的名字.
    SQL Server在使用数据页的时候，为了提高速度，会先把一些页面一次预留”reserve”给表格，然后真正有数据插入的时候，再使用。
    所以这里有两列，Reserved_page_count和Used_page_count。两列的结果相差一般不会很多。所以粗略来讲，Reserved_page_count*8K，就是这张表格占用的空间大小。
    DataPages是这张表数据本身占有的空间。因此，（Used_page_count – DataPages）就是索引所占有的空间。索引的个数越多，需要的空间也会越多。
    RowCounts，是现在这个表里有多少行数据。
    还有一个方法可以精确地统计出某张表格的空间使用量，那就是DBCC SHOWCONTIG（或者直接查询系统管理视图sys.dm_db_index_physical_stats）。避免在SQLSERVER高峰
    时期使用。否则影响性能
    
    */
    CREATE PROCEDURE PR_WHO_USE_MORE_SPACE
    AS
    SET NOCOUNT ON
    BEGIN
    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
     END
    go
    
    
    select * from sys.dm_os_buffer_descriptors---缓存池中数据页的分布即谁吃掉了 BUFFER POOL
    
     
    
     
    
    --当前数据库的哪个表或者索引占用Pool缓冲空间最多? 
    
    SELECT count(*)*8 AS cached_pages_kb
        ,obj.name ,obj.index_id,b.type_desc,b.name
    FROM sys.dm_os_buffer_descriptors AS bd 
        INNER JOIN 
        (
            SELECT object_name(object_id) AS name 
                ,index_id ,allocation_unit_id,object_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p 
                    ON au.container_id = p.hobt_id 
                        AND (au.type = 1 OR au.type = 3)
            UNION ALL
            SELECT object_name(object_id) AS name   
                ,index_id, allocation_unit_id,object_id
            FROM sys.allocation_units AS au
                INNER JOIN sys.partitions AS p 
                    ON au.container_id = p.partition_id 
                        AND au.type = 2
        ) AS obj 
            ON bd.allocation_unit_id = obj.allocation_unit_id
            LEFT JOIN sys.indexes b on b.object_id = obj.object_id AND b.index_id = obj.index_id
     
    WHERE database_id = db_id()
    GROUP BY obj.name, obj.index_id ,b.name,b.type_desc
    ORDER BY cached_pages_kb DESC;