Post reply

heap with a lot of unused Space keep growing!

  • February 7, 2013 at 9:09 am

    #269877

    Hi folks,

    I've got an Instance of SQL2K08 with 22 user databases and about 1430 tables.

    One of the user databases has a heap table ( heap schema ) with a lot unused space.

    sp_spaceused applied on the table( dbcc updateusage also applied)

    name rows reserved data index_size unused

    Orders377603 24076024 KB3009760 KB56 KB21066208 KB

    This table only receives Inserts. There aren't UPDATES/DELETES and there aren't forwarding_records.

    Other metrics : 117 min_record_size_in_bytes / 313 max_record_size_in_bytes / avg_record_size_in_bytes 227.044

    1 .I really don't understand why there is so much unused space just for a single table. ( To Archive 22GB of data there had to be a big amount of data loaded and deleted and the developer swears that there wasn't any deletes on that table.

    2º I tested some inserts, and after +- 400 rows the Trace detected an Data File Auto Growth . If there is 20GB of unused space why the file keeps growing ?

    3º The developer has another table(Clustered) with the same data and it's only 120MB and this Heap is spending almost 3GB.

    Anyone can help me figuring this out ?

    Thank you all.

  • February 7, 2013 at 9:13 am

    #1585508

    Can you create a clustered index on the table. The tables seems to suffer of fragmentation.

    Greetz
    Query Shepherd

  • February 7, 2013 at 9:16 am

    #1585513

    Hi there,

    I know that if I create a Clustered Index, the problem is solved, but I really want to understand the reason of this amout of data wasted.

    Avg_fragmentation_in_percent

    1.66831921358261

    Fragmentation_count

    376183

    avg_fragment_size_in_pages

    1.00009835638506

    avg_space_used_in_percent

    2.81550531257721 ( WHAT ? )

    Why does i have a % used page space so low?

  • February 7, 2013 at 9:17 am

    #1585514

    a heap table never releases the space that were used by rows that existed, and then were deleted (unless a full table schema lock was applied, like when deleting ALL rows or truncate table)

    i know you said it only received inserts, but some process might have deleted at some point.

    so if this table has any sort of deletes applied to it, then the ionly way to recover the space from those deleted rows is to rebuild the table (insert into newHeap) or adding a clustered index, which fixes the space issue.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 7, 2013 at 9:18 am

    #1585517

    Heap tables don't reclaim space when records are deleted. To reclaim space you can add a clustered index to the table. Your table really should have one any way. Be sure to pick one that makes sense for the table. Two criteria some would tell you is 1) it should uniquely identify the row, 2) it should be an ever increasing value.

    I'm sure others will provide you with more information.

  • February 7, 2013 at 9:25 am

    #1585522

    That was the first thing i thought!

    I can't trust on what the developer is saying me. 1 year already had passed and he can't be sure if anything(or even himself) deleted some data.

    22GB is a lot of information and even if someone deleted data(and if the data it's spending about 120MB on the Clustered table) I can't figure out how many deletes would be necessary to archive 20GB of unused space.

    Are you sure that if someone deletes data, this is marked as unused space and will never be re-used?

    @Lynn Pettis It's not my table. I would always create a Clustered Index on it. I'm just trying to understand the SQL Server behavior of adding more space when there is so much unused space on it to be used .

    Thanks

  • January 29, 2015 at 4:37 am

    #1773935

    You can rebuild the heap and the nonclustered indexes, and update statistics, by creating and then dropping the clustered index, using the ALTER TABLE ... REBUILD or by changing the data compression. (when using the REBUILD on a heap, not all the options are available to you).

    You need to determine when this is necessary by using sys.dm_db_index_physical_stats to check the level of fragmentation. I prefer ALTER TABLE ... REBUILD but creating and dropping the clustered index works as well.

    I hope that helps

    Best wishes,
    Phil Factor

  • January 29, 2015 at 8:43 am

    #1774037

    kudz (2/7/2013)

    3º The developer has another table(Clustered) with the same data and it's only 120MB and this Heap is spending almost 3GB.

    Hold the phone! Why would there be two objects (clustered table and a heap) that contain "the same data" to begin with???

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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