How to remove data and index fragmentation in a table

  • The picture is showing the results that I used sp_spaceused for OldTable and NewTable, I used "select into NewTable from OldTable", and created the same indexes on newtable with the OldTable, as per the data of the picture, the OldTable taking more space than the NewTable, so seems that the OldTable has many many fragmentation space, now How can I remove the fragmentation space? seems that the data has very big data fragmentation, how to remove it ? many thanks!

     

    Attachments:
    You must be logged in to view attached files.
  • Have you tried rebuilding the indexes on the old table - with the same fill factor as the new table?  I would review each index and see what the fill factor has been set to on the old table.

    The other concern is whether this table has a clustered index or not.  If it doesn't have a clustered index and there are expansive updates and/or deletes from the table, the previous allocated will not be released.  If the table does not have a clustered index and you cannot add one - try running an ALTER TABLE ... REBUILD.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • thank you Jeffrey Williams, the table has clustered index,  but  when I used "alter table oldtable rebuild" , the table released a lot space, many thanks!

    when I did this operation, there is another question, when I ran "select * from sys.sysindexes" to check which tables have more rows,

    it rans very very slow,but I ran it on another server, it runs very faster, how could I can fix the problem except for restarting SQL server service? thanks!

  • 892717952 wrote:

    thank you Jeffrey Williams, the table has clustered index,  but  when I used "alter table oldtable rebuild" , the table released a lot space, many thanks!

    when I did this operation, there is another question, when I ran "select * from sys.sysindexes" to check which tables have more rows,

    it rans very very slow,but I ran it on another server, it runs very faster, how could I can fix the problem except for restarting SQL server service? thanks!

    There are a thousand reasons why this might be true.  How many rows in each table?  How does the hardware compare?  What is the workload on the server?  How many rows in the table?  Etc, etc, etc.

    This is a bit like asking why the Blue car gets better gas mileage than the Red car.  Without knowing make, model, engine size, transmission and drive configuration, and driving habits, it's impossible to tell.

    --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)
    Intro to Tally Tables and Functions

  • Dear Jeff Moden,  thank you for you kind help!   what you said is quite right.

    if I ran it on the same server, sometimes it works fine, but sometimes not ,  because it is system's object. it is my confusion? thanks

  • Jeff Moden wrote:

    This is a bit like asking why the Blue car gets better gas mileage than the Red car. 

    Red cars are faster.

  • 892717952 wrote:

    Dear Jeff Moden,  thank you for you kind help!   what you said is quite right.

    if I ran it on the same server, sometimes it works fine, but sometimes not ,  because it is system's object. it is my confusion? thanks

    It doesn't matter if it's a system object or not.  It just proves that "It Depends" is frequently the correct answer and that "Always look eye" is the correct action to take.

    --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)
    Intro to Tally Tables and Functions

  • Getting back to the original post... you do know what sys.dm_db_index_physical_stats() is for, correct?

     

    --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)
    Intro to Tally Tables and Functions

  • This was removed by the editor as SPAM

Viewing 9 posts - 1 through 9 (of 9 total)

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