Post reply

How to do Table defragmentation

  • March 26, 2009 at 4:42 am

    #207636

    Hi Experts,

    Any one can suggest how to do Table Defragmentation(not index defrag).

    Few tables are grown upto 5 gb in our database. We need to do some defragmentation on that tables to improve performance.(server disks are configured with raid 5)

    Or any utilities to do this task.

    Thanks,

    KK.

  • March 26, 2009 at 5:12 am

    #966522

    Do these tables have clustered indexes?

    ------------------------------------------------------------------------
    Bite-sized fiction (with added teeth) [/url]

  • March 26, 2009 at 5:16 am

    #966524

    Yeah, what mazzz said. If you defrag the clustered index on that table it reorganizes the data in the table because the data in the table folows the order of the clustered index.

    Otherwise, copying all of the data from one table to another, with the correct sort, will give you a new, defragged table, which you can rename to be the table in question.

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • March 26, 2009 at 9:58 am

    #966796

    Hi Chris,

    Thanks for the update.

    My table having indexes, normalization and all other statistics are good.

    But we are facing a latency when we are using this table in joins. This Table having 70 million records and joins with another heavy table(5 million records).

    We need to over come this issue.

    Can you suggest any other alternative or utilities.

    Thanks,

    KK

  • March 26, 2009 at 10:17 am

    #966821

    koteswara kishore K. (3/26/2009)

    Hi Chris,

    Thanks for the update.

    My table having indexes, normalization and all other statistics are good.

    But we are facing a latency when we are using this table in joins. This Table having 70 million records and joins with another heavy table(5 million records).

    We need to over come this issue.

    Can you suggest any other alternative or utilities.

    Thanks,

    KK

    If you have a clustered index on a table then it's the actual data. Data is part of a clustered index. Did you check the fragmentation levels of this table? Could you post it please? and have you got proper indexes designed on the table? and can you post the query which you are using to join this table?

  • March 26, 2009 at 1:59 pm

    #967029

    How wide is the table? Can you post the scripts for the tables and indexes involved?

    What are you keying on in your join?

    How many records does the query that is slow return?

    Chris

    Learning something new on every visit to SSC. Hoping to pass it on to someone else.

  • March 27, 2009 at 6:12 am

    #967355

    I agree with the replies. The data maybe out of order and need rebuilt in order but the order is by a clustering index. If you have no clustering index on the table the data will be stored in any random fashion. If you have a clustering index and rebuild it it will order all 70 million rows that way. Keep in mind if you rebuild a clustered index it will automatically rebuild ALL of the indexes on that table because the clustering index is how the physical data is stored in that order. If your 70 million row table has 10 indexes this could take some time just keep that in mind.

  • March 29, 2009 at 9:59 pm

    #968136

    Krishna (3/26/2009)

    koteswara kishore K. (3/26/2009)

    My table having indexes, normalization and all other statistics are good.

    But we are facing a latency when we are using this table in joins. This Table having 70 million records and joins with another heavy table(5 million records).

    We need to over come this issue.

    Can you suggest any other alternative or utilities.

    Is the performance main region for you to planning for the re-index..? if yes, I will say stop first check the table fragmentation level (by executing DBCC SHOWCONTIG) and if the logical scan fragmentation is above 50% then only go for rebuilding the indexes. Performance of the query can be improved by looking into the query plan and index definition etc..may be your query is not utilizing the most of your indexes or your index definition is not upto the mark.

    In a nutshell I will recommend do the basic check wisely and then for any operation.

    -Forum Etiquette: How to post Performance Problems[/url]

    -Forum Etiquette: How to post data/code to get the best help [/url]

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

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