SQL Server Table Size Discrepancy

Question

Hopefully this has a simple answer and I've missed something obvious. I have a table where the number of rows * the row size is much smaller then the actual data space used by the table.

If I run the standard report "Disk Usage by Top Tables", the numbers I get are:

# Records   Reserved (KB)      Data (KB)      Indexes (KB)      Unused (KB) 
33,245        32,962,192       31,070,264       144               1,891,784

which (unless I'm missing something) implies each row is taking up just under 1MB!

The table schema (which I didn't design) is:

CREATE TABLE [MySchema].[MyTable]
(
    [Code]     [varchar](4) NOT NULL,
    [SomeID]   [smallint] NOT NULL,
    [SomeID1]  [smallint] NOT NULL,
    [Updated]  [datetime] NOT NULL,
    [SomeId2]  [int] NULL,
    [SomeId3]  [int] NULL,
    [Somekey]  [real] NULL,
    [desc1]    [char](12) NULL,
    [colA]     [real] NULL,
    [someid4]  [char](20) NULL,
    [starttime] [real] NULL,
    [endtime]  [real] NULL,
    [duration] [real] NULL,
    [reason]   [real] NULL,
    [status]   [real] NULL,
    [category] [real] NULL,
    [comment]  [char](30) NULL,
    [ColB]     [real] NULL,
    [ColC]     [real] NULL
)

It has no indexes or keys.

A bit of research lead me to the idea that perhaps in the past the table had variable length columns that had been deleted, so I ran DBCC CLEANTABLE with no change.

SQL Server 2005 x64 Service Pack 3 (as far as I can work out from the following version string:

[Microsoft SQL Server 2005 - 9.00.4053.00 (X64) May 26 2009 14:13:01 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)]

Update: I'm fairly sure this is due to a pathologically fragmented HEAP .... I rebuilt by applying a clustered index (an index is required, according to missing index reports), and the size discrepancy has gone.

Answer 1

When you delete from heaps, allocated space may not be unallocated unless you use a table lock. See "Deleting Rows from a Heap" from DELETE on MSDN

This is a separate problem to fragmentation (which happens of course)

Just in case, have you tried this to update usage info?

EXEC sp_spaceused 'MySchema.MyTable', 'true'

With no indexes or keys, you can't defragment it normally.
You can add/drop a clustered index, or do something like this

SELECT * INTO [MySchema].[MyTableNew] 
FROM [MySchema].[MyTable]
-- ORDER BY something

-- if you like
EXEC sp_spaceused 'MySchema.MyTableNew', 'true'

DROP TABLE [MySchema].[MyTable];

EXEC sp_rename 'MySchema.MyTableNew', 'MyTable';

oops: just seen updates, my Internets has been broken on and off...

Answer 2

I use this script:

CREATE TABLE #Temp
 (  [Table_Name] varchar(50),
    Row_Count int,
    Table_Size varchar(50),
    Data_Space_Used varchar(50),
    Index_Space_Used varchar(50),
    Unused_Space varchar(50) )
INSERT INTO #temp exec sp_msforeachtable 'sp_spaceused "?"'

SELECT Table_Name AS [Table], Row_Count AS [Row Count],
        dbo.strip_kb(Table_Size) / 1024 AS [Table size],
        dbo.strip_kb(Data_Space_Used) / 1024 AS [Data Space],
        dbo.strip_kb(Index_Space_Used) / 1024 AS [Index Space],
        dbo.strip_kb(Unused_Space) / 1024 AS [Unused Space],
        CASE Row_Count WHEN 0 THEN 0 ELSE dbo.strip_kb(Data_Space_Used) * 1024 / Row_Count END AS [Avg Row Size]
    FROM #temp order by Row_Count DESC

DROP TABLE #temp

It gives you the average row size per table, as well as the data you showed above, but I tend to trust its output a bit more than the built-in reports :)