Sql-server – Different table size on sp_spaceused and standard report “Disk Usage by Top Tables”

reportingsql serversql-server-2008sql-server-2008-r2tablespaces

I ran sp_spaceused and Disk Usage by Top Tables standard report for a table.

The results for sp_spaceused are:

name rows reserved data index_size unused
SomeTable <1301755> <7691344 KB> <3931672 KB> <3673840 KB> <85832 KB>

However Disk Usage by Top Tables report shows: 

Table Name  # Records   Reserved (KB)       Data (KB)   Indexes (KB)    Unused (KB)     
SomeTable   1.301.755   4.340.216       3.931.672   324.776 83.768

Record count is the same but there is a big gap between the two in terms of space used. sp_spaceused shows 7.691.344 KB as reserved while the report shows 4.340.216 KB. Which one is correct?

Best Answer

Frankly, I wouldn't use either. You can find your biggest tables immediately - with more flexibility - and not worry about where @updateusage has been set.

CREATE PROCEDURE dbo.TopTables
  @NumberOfObjects INT = 100,
  @MinimumSizeInMB INT = 10
AS
BEGIN
  SET NOCOUNT ON;

  SELECT TOP (@NumberOfObjects)
    [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name),
    index_count = COUNT(i.index_id),
    size_in_MB = SUM(p.reserved_page_count)*8/1024.0
  FROM sys.schemas AS s
  INNER JOIN sys.objects AS t
  ON s.[schema_id] = t.[schema_id]
  INNER JOIN sys.indexes AS i
  ON t.[object_id] = i.[object_id]
  INNER JOIN sys.dm_db_partition_stats AS p
  ON t.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE t.is_ms_shipped = 0
  GROUP BY s.name, t.name
  HAVING SUM(p.reserved_page_count)*8/1024.0 >= @MinimumSizeInMB
  ORDER BY size_in_MB DESC;
END
GO
Related Question