June 26, 2012 at 4:22 pm
I have a database table with 1460 records, 3 columns.
It's a heap with no indexes at all.
I'm just curious why is it 9GB in size?
Is there a way to find out or fix this or should not worry about it?
Thanks
June 26, 2012 at 4:31 pm
Is there a lot of update/insert/delete activity on this table? Very good possibility that space is not being reused and more space is allocated when it thinks it is needed.
June 26, 2012 at 5:16 pm
Or you could have two int and an xml or varchar(max) or varbinary(max) or text or image column. Each of those LOB columns can store up to 2GB of data in a row. Might be worth looking at the table schema.
June 26, 2012 at 11:47 pm
Probably because it's a heap.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 28, 2012 at 7:59 am
GilaMonster (6/26/2012)
Probably because it's a heap.
That would be my vote too. I have seen that at quite a number of clients over the years!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 28, 2012 at 9:53 am
Also, do a
DBCC UPDATEUSAGE
on the table. Heaps seem to report ridiculously incorrect page totals too often for comfort. I've had SQL telling me that a single heap was larger than the entire db allocation!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 28, 2012 at 12:45 pm
Did you try this?
select * from sys.dm_db_index_physical_stats(DB_ID(),object_id('tablename'),null,null,'DETAILED')
go
It can provide you the forward row count etc..
To fix this you can run the alter table <tablename> rebuild
it should fix the issue if it is because of lots of deletion or forwarded row count and the pages were not deallocated at the time of deletion.
But if it is because you have some nvarchar(max) or xml column etc... Then It might actually using that much space.
Can you post the ddl statement for the table?
GulliMeel
http://gullimeelsqlsybase.wordpress.com/2012/06/28/finding-top-n-worst-performaing-queries/
http://gullimeelsqlsybase.wordpress.com/2012/06/26/improved-merge-join-algorithm-in-sql-server-2008/
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply