Post reply

High fragmentation of indexes

  • February 28, 2013 at 9:57 pm

    #290460

    Hi all,

    I have a table Order with primary key ID column is uniqueidentifier data type on server B. Nightly, a synchronization job runs to synchronize orders from server A to server B. After that, I check the fragmentation of clustered index I see that the value is always so high.

    Does uniqueidentifier data type cause the high frag because SQL insert data into Order table with random mode?

    I also set FILLFACTOR = 80%

    Thanks,

  • February 28, 2013 at 10:59 pm

    #1592248

    Dung Dinh (2/28/2013)

    Hi all,

    I have a table Order with primary key ID column is uniqueidentifier data type on server B. Nightly, a synchronization job runs to synchronize orders from server A to server B. After that, I check the fragmentation of clustered index I see that the value is always so high.

    Does uniqueidentifier data type cause the high frag because SQL insert data into Order table with random mode?

    I also set FILLFACTOR = 80%

    Thanks,

    That is most likely your problem. This is a known issue with using the uniqueidentifier in any type of index, especially the clustered index. Fill factor will only go so far to help you. The best option is to move away from using uniqueidentifier or to stay extremely vigilant about maintaining your indexes.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 28, 2013 at 11:40 pm

    #1592254

    opc.three (2/28/2013)

    Dung Dinh (2/28/2013)

    Hi all,

    I have a table Order with primary key ID column is uniqueidentifier data type on server B. Nightly, a synchronization job runs to synchronize orders from server A to server B. After that, I check the fragmentation of clustered index I see that the value is always so high.

    Does uniqueidentifier data type cause the high frag because SQL insert data into Order table with random mode?

    I also set FILLFACTOR = 80%

    Thanks,

    That is most likely your problem. This is a known issue with using the uniqueidentifier in any type of index, especially the clustered index. Fill factor will only go so far to help you. The best option is to move away from using uniqueidentifier or to stay extremely vigilant about maintaining your indexes.

    As your point,I should rebuild or re-organize indexes on this table after this job is done nightly.

  • March 1, 2013 at 12:19 am

    #1592264

    I would say yes you have to rebuild your index after sync dome with server.

    Updating table which is having cluster index may face this problem batter to rebuild index once sync with server

  • March 1, 2013 at 5:33 am

    #1592322

    Yes, the uniqueidentifier key is the most likely your problem. If you could make changes to your tables so that it gets newsequentialid() values, then you'll obtain a very low fragmentation.

    Igor Micev,My blog: www.igormicev.com

  • March 1, 2013 at 7:09 am

    #1592368

    Dung Dinh (2/28/2013)

    As your point,I should rebuild or re-organize indexes on this table after this job is done nightly.

    Yes. As often as you have a maintenance-window to keep those indexes at a low fragmentation level I would schedule it.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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