SQL Server – Datetime Clustered Index Keeps Getting Fragmented

clustered-indexdatetimefragmentationsql serversql server 2014

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.

Best Answer

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

Related Solutions

Sql-server – Should I lock the table during delete

Changing the locking to table locks will just make the deletes run even slower as the delete won't be able to run until the lock can be taken on the table which means that all other threads need to be finished or blocked. If you have foreign keys with delete cascade enabled that will probably take a lot of the time.

You might want to change it to a SQL Agent job so instead of running your app which connects and disconnects, you just run a loop deleting data until you are done.

SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (1000) FROM YourTable
    WHERE Column < getdate()-31
END

If this doesn't work you could look into table partitioning which would allow you to switch the data to another table very quickly, then truncate the data from the new table. This however does require Enterprise Edition.

Sql-server – Index fragmentation increase as more row being deleted

This isn't fragmentation.

Fragmentation is generated of course, but deletes will simply create "islands" of remaining pages, which is less evil then GUID/clustered key INSERT fragmentation.

If you're PK is an IDENTITY, then CreationDate should roughly track this so you're actually deleting chunks of contiguous rows anyway.

Do you have an index on CreationDate
Do you have delete cascades?
Is the TOP 1000 in a single transaction?

For point 3, doing a loop inside a transaction is pointless: is this it?

At some point, a statistics update may be needed if you delete enough rows but I don't think it's that.

Other options:

why not use TRUNCATE TABLE, wrapped in a stored procedure with EXECUTE AS OWNER
use SYNONYMs for poor man's partitioning

Best Answer

Related Solutions

Sql-server – Should I lock the table during delete

Sql-server – Index fragmentation increase as more row being deleted

Related Question