I have a table that contains more than 18 million records. I have a process that clears data out of that table everyday. Index fragmentation was low.

This table has high transaction throughput. Every second it stores about 3 to 5 new records, so we know that clearing old records for this table needs to be fast.

The delete statement is like this:

Delete top 1000 
From MyTable 
Where CreationDate < 'Some Date'

Ideally, we keep running it until no more rows can be deleted.

For the first 6 million records the delete process goes well, but as time passes the delete starts slowing down until it impacts other application that access the same table. In addition, many foreign key indexes become fragmented.

My questions are:

  1. Does deleting a lot of rows cause fragmentation on the foreign key indexes? (that is, indexes the dependent tables)
  2. Does the deletion become slow because of the fragmented foreign key indexes? (slower reference data lookup)
  3. Is there a balanced strategy that can keep the performance of both the deletes and the foreign key indexes high?

I'm on SQL Server 2005 Standard edition.

[Update] I have included more information here The actual table name is called "VehicleLocation" Key columns:

  • VehicleLocationKey (PK, char(36), not null)
  • AgencyVehicleKey (FK, char(36), not null)
  • AssignmentKey (FK, char(36, null)
  • EmployeeKey (FK, char(36), null)


  • VehicleLocation_AssignmentKey (Non-Unique, Non-Clustered)
  • VehicleLocation_CreationDate (Non-Unique, Non-Clustered)
  • VehicleLocation_MessageGenerationDate (Non-Unique, Clustered)
  • VehicleLocation_pk (Unique, Non-Clustered)

Object Dependencies for VehicleLocation (~ 10.5 mil rows)

  • VehicleLocationAPC (~76000 rows)
  • VehicleLocationFare (0 rows)
  • VehicleLocationGF ( 0 rows)
  • VehicleLocationInpt (0 rows)
  • VehicleLocationOBD (~ 15000 rows)
  • VehicleLocationTP (~8.3 million rows)

All the above tables has indexes on their primary key and on VehicleLocationKey (FK) table.

In addition, we use GUID as primary key (bad idea, but it is legacy). On top of that I see that VehicleLocationTP index has a fragmentation of 96%, which is very high.

  • 2
    What edition of SQL Server are you on? You might want to consider partitioning if this is a daily process. Also not sure I understand your FK issue. Is there an on delete cascade from MyTable to dependant tables? Commented Jan 11, 2012 at 20:19
  • What's the PK on MyTable? Ideally, you should be doing something more like this: DELETE FROM MyTable mt WHERE mt.PrimaryKeyID IN ( SELECT TOP 1000 PrimaryKeyID FROM MyTable WHERE CreationDate < 'Some Date' ORDER BY CreationDate DESC).
    – Brandon
    Commented Jan 11, 2012 at 20:29
  • @MartinSmith, I would love to use partition, but we don't have the resource and expertise in the company to know how to use the Enterprise version partitioning feature.
    – dsum
    Commented Jan 11, 2012 at 22:15
  • @Brandon, why would that make a difference? Commented Jan 12, 2012 at 13:52
  • @MongusPong, I was originally thinking that the CreationDate would somewhat track onto the clustered index and that by forcing a somewhat chronological order deletion, it would be more performant. The GUID clustered index shoots that idea in the foot though, so my comment above is not terribly valid in this instance.
    – Brandon
    Commented Jan 12, 2012 at 23:46

1 Answer 1


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.

  1. Do you have an index on CreationDate
  2. Do you have delete cascades?
  3. 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
  • 1. Yes - we ahve index on CrationDate 2. No - no delete cascades or any cascades 3. Yes - We open a connection, run the Delete, then close the connection. This process repeat itself. This is to prevent table lock. I don't really know much about statistic update. I thought TRUNCATE TABLE delete everything, can I put where clause on it? Last year we actually was thinking of using Synonyms for partitioning for another project, but we dropped this idea because of other design issue.
    – dsum
    Commented Jan 12, 2012 at 17:28

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.