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;