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.