Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from serverfault.com Feb 9 '12 at 8:59

This question came from our site for system and network administrators.

4  
Can you check the physical table layout? see rusanu.com/2011/10/20/sql-server-table-columns-under-the-hood –  Remus Rusanu Feb 9 '12 at 17:23

3 Answers 3

up vote 5 down vote accepted

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

share|improve this answer
    
was closest answer to what I ended doing (I actually created a clustered index: there was no reason why this table was a heap, and a missing index was needed in a range search) –  Mitch Wheat Feb 12 '12 at 3:11

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 :)

share|improve this answer
    
Thanks, but I already knew the sizes...THe built-in reports seem pretty good. I'd be surprised if they weren't using something similiar... –  Mitch Wheat Feb 9 '12 at 9:12

I'm fairly sure this is due to a pathologically fragmented HEAP

Especially because the HEAP has nothing to do with it. Ouch.

Maybe a lot got deleted and the pages are not yet reclaimed for free space, because of missing reorganization or empty pages just not having been reclaimed (which is a background process).

I would reorganize the clustered index - that normally cleans that out quite fast. Also copy into another table and see how large it is then.

share|improve this answer
    
@Mtich I'm confused about source vs. cause? –  ErikE Feb 12 '12 at 23:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.