Empty table taking a lot of space

Question

Empty table taking a lot of space

David Betteridge

Hall of Fame

Points: 3264
More actions
May 11, 2009 at 2:24 am

#204130

I have a table (dbo.ImportCustomer) which is populated and cleared down as a part of an import job. However this process has been timing out a lot recently. Looking at the top tables by disk usage, this table now appears at the top.
This table has no indexes (clustered or otherwise)
--If I select count(*) from the table then no rows are reported, however it takes about a minute to return.
select count(*) from dbo.ImportCustomer
0
DBCC SHOWCONTIG ('dbo.ImportCustomer')
DBCC SHOWCONTIG scanning 'ImportCustomer' table...
Table: 'ImportCustomer' (201832577); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 71372
- Extents Scanned..............................: 13667
- Extent Switches..............................: 13666
- Avg. Pages per Extent........................: 5.2
- Scan Density [Best Count:Actual Count].......: 65.28% [8922:13667]
- Extent Scan Fragmentation ...................: 62.14%
- Avg. Bytes Free per Page.....................: 8058.3
- Avg. Page Density (full).....................: 0.44%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Does this seem correct?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Satish Nagaraja SSCrazy Points: 2632 More actions · Answer 1

Going by the DBCC output you mention in you post you have no indexes on the table and is usually referred to as heap. This output can be ignored for fargementation check since index id = 0 which is a heap.

can you check by running system procedure sp_spaceused '

' and post the results here ?

even if the results show zero rows in above output there would be minimum space reserved based if there had been values stored earlier and then deleted but if it is new table then it would show zero for all columns

-:-)

Cheer Satish 🙂

David Betteridge Hall of Fame Points: 3264 More actions · Answer 2

sp_spaceused 'ImportCustomer'

Gives....

name=ImportCustomer

rows=0

reserved=874776 KB

data=571048KB

index_size=16KB

unused=303712KB

thanks

David

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

Is this on 2000? If so, emptied pages in heaps don't necessarily get deallocated (freed).

Try one of: truncate table, creating a clustered index (and maybe keeping it around or dropping it again).

Not sure why techbabu said to ignore the DBCC output - it's clearly telling you how many pages there are and that they're basically empty.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

David Betteridge Hall of Fame Points: 3264 More actions · Answer 4

I did a truncate table and that sorted it - thanks.

The database used to be on a SQLServer 2000 machine, so I guess that the issue was left over from then. I shall keep an eye on it and see what happens.

Thanks again,

David

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 5

I'm not sure if this is happening with you but the following article http://blog.sqlrx.com/wp-content/forwarded-records-article.doc may give you some insight on some hidden performance killers when working with heap tables.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]