Sql-server – SQL Server Table Size Discrepancy

size;sql serversql-server-2005

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.

Best Answer

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...

Related Question