4

I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a record inserted out of sequence. Nor do we update or delete rows.

Since most of our queries are based on the time a call was initiated, I created my main clustered index on this column (DATETIME data-type). However, after a brief time the index becomes fragmented and after just one week it has over 70 percent of fragmentation so we need to rebuild it. I don't understand why this keeps happening since we insert sequentially, and don't update or delete rows.

Any suggestions to reduce or remove the fragmentation will be much appreciated.

I'm using a SQL Server 2014 Enterprise version on an old Xenon server with Windows Server 2012.

8
  • Forgive me for asking this, but you didn't happen to create the index in descending order did you?
    – dwjv
    Commented Nov 20, 2015 at 16:03
  • 70% fragmentation is a week is a lot. I know you say you never delete or update rows but check again. Are you even maybe doing an insert with an immediate update? Until you figure out what is going on I would put the index at less then 100% fill and maybe not have it clustered.
    – paparazzo
    Commented Nov 20, 2015 at 16:20
  • Thanks for the replies and information you shared, dear @dwjv , your pointed matter was very nice and may be something that I might have missed, but ichecked and it IS in ascending order, thanks though. Frisbee , I must reassure, so I have set a profiler on the table, I will tell the results later, and for the fill factor point, yes. I have set it to 75 yesterday and it reduced the fragmentation but we still have it.
    – Mohfath
    Commented Nov 20, 2015 at 16:22
  • 1
    @Frisbee, I was considering the scenario that some records one hour ahead are inserted, then every normal record will suddenly become a late arrival, leading to page splits and fragmentation.
    – Cyrus
    Commented Nov 20, 2015 at 21:22
  • 1
    @Cyrus If the last week was totally fragmented by hours it is still just the last week. If the table is the last year that is only 2% of the table. It takes a lot to fragment a whole table 70%.
    – paparazzo
    Commented Nov 20, 2015 at 21:31

1 Answer 1

7

Fragmentation can still occur on a table with an ever increasing key even if that key is never itself subject to updates.

  • If the file group is shared with other objects allocations can be interleaved causing fragmentation.
  • Updates that increase the size of rows can cause page splits.
  • Deletes can leave pages nearly empty and cause internal fragmentation.

However you say deletes and updates are rare or non existent. So to reach 70% fragmentation within a week I would assume that the cause must be a shrink database task. See Why you should not shrink your data files

3
  • Nice point, but we recently had no shrinking.
    – Mohfath
    Commented Nov 20, 2015 at 16:26
  • 3
    @Mohfath how have you determined that? To get 70% fragmentation there must be some activity touching old pages and you have also ruled out the other possibilities. Commented Nov 20, 2015 at 16:59
  • 1
    I WANT TO THANK EVERYONE WHO SHARED THEIR KNOWLEDGE ON THIS QUESTION, My problem solved here. It seems that you must always give a little free space (fill factor) to any index. Or you WILL face index fragmentation. This time I rebuilt index with 80 percent fill factor and I got 0 fragmentation after a week. I'm sorry for the delay everyone, I needed to make sure about it before posting the result of the test
    – Mohfath
    Commented Nov 25, 2015 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.