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.