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.

  • Forgive me for asking this, but you didn't happen to create the index in descending order did you? – dwjv Nov 20 '15 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 Nov 20 '15 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 Nov 20 '15 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 Nov 20 '15 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 Nov 20 '15 at 21:31

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

  • Nice point, but we recently had no shrinking. – Mohfath Nov 20 '15 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. – Martin Smith Nov 20 '15 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 Nov 25 '15 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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