Post reply

Table with high unused space

  • November 17, 2023 at 8:46 am

    #4329393

    I have a table where over 50% has unused space

     

    How can I reclaim this space back? Index maintenance?

  • November 17, 2023 at 9:06 am

    #4329428

    You should be asking "why has this table 50% unused space" ?

    • Is the free space on the data file(s) or in the log file ?
    • Indexes can be set to only fill its pages up to a given % at rebuild time.  (leaving free space to avoid page splits)
    • due to page splits ( 50/50 ) of your data system can be normal behaviour ( and should be managed )
    • (mass) delete of rows ( "to keep the db size and performance under control")
    • Delete of rows in heap tables with off row data ( text/immage/binary/varchar(max) ) may leave pages in use that are empty

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 17, 2023 at 1:47 pm

    #4329555

    Could the way data is inserted into the table increase the unused space?

  • November 17, 2023 at 2:36 pm

    #4329592

    yes, if it is causing page splits

    ( check clustered index, sort according to clustered index of possible )

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 19, 2023 at 9:40 pm

    #4330705

    If the clustered index has an ever increasing key and the inserts are following that key, you won't get page splits.  If, however, the inserts are followed by "ExpAnsive" Updates, you're guaranteed to get page splits and fragmentation and low page densities.

    The key to us helping here is that we'd need to see the CREATE TABLE statement, including all of the indexes involved and then an example of what the inserts look like (being sure to NOT post any PII or other sensitive information).

    Also, doing index maintenance to recover the disk space will only recover the disk space... it will NOT help you prevent the fragmentation all by itself.  You've got to figure out WHY it is fragmenting.  The info I asked for above can help a lot there.

    I'll also state that "Index" maintenance will do absolutely nothing if this "table" is actually a "HEAP" (a table with no clustered index).  You  would have to do an ALTER TABLE REBUILD instead.  The problem there is that it will also rebuild all non-clustered indexes on the HEAP because the RIDs (row IDs) of every row in the HEAP are included in every non-clustered index.  Unless you have FK's pointing at the table, it would be better to first disable all the NCIs (non-clustered indexes), rebuild the HEAP, and then REBUILD all the NCI's to re-enable them.

     

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

  • November 19, 2023 at 10:21 pm

    #4330709

    Have you tried rebuilding the clustered index?

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

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