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

 
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;