我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。
是否有一种简单的方法来确定每个表占用的磁盘空间?
我继承了一个相当大的SQL Server数据库。考虑到它包含的数据,它似乎比我预期的要占用更多的空间。
是否有一种简单的方法来确定每个表占用的磁盘空间?
当前回答
如果需要精确计算SSMS中“表财产-存储”页面上的相同数字,则需要使用与SSMS中相同的方法进行计数(适用于sql server 2005及更高版本……也适用于具有LOB字段的表,因为仅计算“used_pages”不足以显示准确的索引大小):
;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.TableName,
cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc
其他回答
sp_spaceused可以获取表、索引视图或整个数据库使用的磁盘空间的信息。
例如:
USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO
这将报告ContactInfo表的磁盘使用情况信息。
要同时对所有表使用此选项:
USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO
您还可以从SQL Server的右键单击“标准报告”功能中获取磁盘使用情况。要获取此报告,请从对象资源管理器中的服务器对象导航,向下移动到数据库对象,然后右键单击任何数据库。从出现的菜单中,选择“报告”,然后选择“标准报告”,再选择“磁盘分区使用情况:[DatabaseName]”。
当处理多个分区和/或筛选索引时,Marc_s的答案给出了错误的结果。它也不区分数据和索引的大小,这通常是非常相关的。一些建议的修复方法并不能解决核心问题,或者根本就是错误的。
以下查询解决了所有这些问题。
SELECT
[object_id] = t.[object_id]
,[schema_name] = s.[name]
,[table_name] = t.[name]
,[index_name] = CASE WHEN i.[type] in (0,1,5) THEN null ELSE i.[name] END -- 0=Heap; 1=Clustered; 5=Clustered Columnstore
,[object_type] = CASE WHEN i.[type] in (0,1,5) THEN 'TABLE' ELSE 'INDEX' END
,[index_type] = i.[type_desc]
,[partition_count] = p.partition_count
,[row_count] = p.[rows]
,[data_compression] = CASE WHEN p.data_compression_cnt > 1 THEN 'Mixed'
ELSE ( SELECT DISTINCT p.data_compression_desc
FROM sys.partitions p
WHERE i.[object_id] = p.[object_id] AND i.index_id = p.index_id
)
END
,[total_space_MB] = cast(round(( au.total_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[used_space_MB] = cast(round(( au.used_pages * (8/1024.00)), 2) AS DECIMAL(36,2))
,[unused_space_MB] = cast(round(((au.total_pages - au.used_pages) * (8/1024.00)), 2) AS DECIMAL(36,2))
FROM sys.schemas s
JOIN sys.tables t ON s.schema_id = t.schema_id
JOIN sys.indexes i ON t.object_id = i.object_id
JOIN (
SELECT [object_id], index_id, partition_count=count(*), [rows]=sum([rows]), data_compression_cnt=count(distinct [data_compression])
FROM sys.partitions
GROUP BY [object_id], [index_id]
) p ON i.[object_id] = p.[object_id] AND i.[index_id] = p.[index_id]
JOIN (
SELECT p.[object_id], p.[index_id], total_pages = sum(a.total_pages), used_pages = sum(a.used_pages), data_pages=sum(a.data_pages)
FROM sys.partitions p
JOIN sys.allocation_units a ON p.[partition_id] = a.[container_id]
GROUP BY p.[object_id], p.[index_id]
) au ON i.[object_id] = au.[object_id] AND i.[index_id] = au.[index_id]
WHERE t.is_ms_shipped = 0 -- Not a system table
exec sp_spaceused N'dbo.MyTable'
对于所有表,请使用。。(根据保罗的评论补充)
exec sp_MSForEachTable 'exec sp_spaceused [?]'
CREATE TABLE #tmp_table_info
(
id int identity(1,1),
tblname varchar(200)
);
CREATE TABLE #SpaceUsed
(
TableName sysname
,NumRows BIGINT
,ReservedSpace VARCHAR(50)
,DataSpace VARCHAR(50)
,IndexSize VARCHAR(50)
,UnusedSpace VARCHAR(50)
)
insert into #tmp_table_info
select s.name+'.'+t.name
from sys.tables t
inner join sys.schemas s on t.schema_id = s.schema_id
where t.type = 'U';
declare @min int =1,@max int = 0
select @max = count(*)
from #tmp_table_info
while(@min<=@max)
begin
declare @tablename varchar(200)
select @tablename=tblname
from #tmp_table_info
where id =@min
DECLARE @str VARCHAR(500)
SET @str = 'sp_spaceused '''+@tablename+''''
INSERT INTO #SpaceUsed
EXEC (@str)
set @min =@min + 1
end;
select @@SERVERNAME as servername,DB_NAME() as DatabaseName,CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpace_MB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpace_MB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpace_MB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpace_MB from #SpaceUsed
drop table #tmp_table_info
drop table #SpaceUsed
下面是一个示例查询,用于获取按大小降序排列的大于1GB的表。
USE YourDB
GO
DECLARE @Mult float = 8
SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs
; WITH CTE AS
(
SELECT
i.object_id,
Rows = MAX(p.rows),
TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),
UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)
FROM
sys.indexes i
JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
i.object_id > 255
GROUP BY
i.object_id
HAVING
SUM(a.total_pages) * @Mult > 1
)
SELECT
SchemaName = s.name,
TableName = t.name,
c.TotalSpaceGB,
c.UsedSpaceGB,
UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,
[RowCount] = c.Rows
FROM
CTE c
JOIN
sys.tables t ON t.object_id = c.object_id
JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
c.TotalSpaceGB DESC