I have a table that has 4 million records. It has a clustered index on a date column (record creation date). It has 5 tables reference this table, all have FK indexes.

The machine has no down time. I had a program that clean up records that older than 31 days. It create a connection, Delete TOP 1000 rows, close the connection, and repeat until all old records are removed.

The delete has been very slow, it is deleting about 1000 rows per 10 sec. Ideally I want to do 1000 row per a second.

I notice that during the delete, it is performing a lot of page lock on the index.

I am wondering if there are faster way to delete data without causing timeout.

My idea is that would it be better if I do a table lock, perform the delete, wait for it for a sec so that it doesn't timeout other transaction, then perform the delete again. My guess is that if I do a table lock, it should reduce the number of row locks or page locks, which may speed up the delete.

Any suggestions on this issue I have would help.

Please note that the harddrive or database isn't fragmented, and it is a RAID 10 machine.

[Update] Thanks for asking for the performance execution plan. It looks like the live environment is different than my development environment. It is doing index scan rather than index seek. I think I have to investigate more about why it would do a index scan. Estimated Execution Plan

[Update 2] Here is the index that we have for some of those tables. Our index naming convention is [TableName]_[ColumnName], sorry we didn't use MSSQL naming standard. In addition, it turns out that client has a 96% fragmented index (VehicleLocationTP_VehicleLocationKey), that definitely is one of the problems. It may be a reason why SQL2005 would use index scan, rather than index seek. enter image description here

[Update 3] I finally able to test the delete query on their testing server, instead on my own computer. They are running SQL 2005 Standard, verse SQL 2008 R2 Express on my machine. The indexes where about 95% fragmented, and rebuilding the index has improved the delete from 25-50%. It is hard to do performance test when the their SQL Server is constantly running. However, checking the actual execution plan, it is the same as the estimated one. So you are right that fragmentation doesn't affect the execution plan. My guess is that it could be the number of rows in the table. Maybe if the table is small, it would uses index scan, rather than index seek.

In addition, this article give me a bit more insight on why it is a index scan Index Scan vs Index Seek

When the execution plan showing index scan, it really scanning the entire table. It calls it a index scan because VehicleLocationAPC is a cluster indexed table. This remove a bit of confusion. It means that index weren't use, it was doing an entire table scan.

Another thing to realize is that the content of the data in VehicleLocationAPC. VehicleLocationKey are almost unique all the time. Our application generation one VehicleLocationAPC row per VehicileLocation row. My guess is that because of this, SQL Server would rather scan the entire table, instead of using the index... but I could be wrong, because I would have thought that the index are sorted in a b-tree, which should be faster to scan the key, rather than doing a table scan.

My focus turns to VehicleLocationTP, this is the table that is causing 63% of the estimated time, and this table is huge.

  • row/page versus table locking isn't the problem. Waiting for an exclusive table lock is likely to slow your delete down even more. My best guess is your slow deletion performance is due to indexes or foreign keys. Dec 9, 2011 at 1:25
  • Can you generate a query plan for the delete statement and show it to us?
    – datagod
    Dec 9, 2011 at 2:22
  • 1
    Any triggers on any of the 6 tables? And FKs are removed by cascades?
    – gbn
    Dec 9, 2011 at 6:29
  • Thank you for including the query plan. Can you show the indexes on one of the tables with the FK?
    – datagod
    Dec 12, 2011 at 4:32
  • RE: Your edit SQL Server does not take account of fragmentation in generating a plan. It uses a scan and a merge join as it believes that will be cheaper than a nested loop join with 1,000 seeks. Dec 12, 2011 at 20:56

2 Answers 2


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.
    DELETE TOP (1000) FROM YourTable
    WHERE Column < getdate()-31

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.

  • Thanks for the idea and info. We are planning to update the specification so that for large client they will require to use Enterprise Edition (i.e. we need to support partitioning). Unfortunately currently I can't use SQL Agent because we need to ensure data is ETL over the data warehouse first before we are comfortable to perform delete. In addition there will be debate on Application vs SQL Agent before we use more database build in functions.
    – dsum
    Dec 9, 2011 at 20:11
  • You could create a SQL Agent job but not schedule it. Then just start the job with your application. Connecting to the database takes time which will slow your delete down. You could take the code I posted above and put it into your app and run that. It'll take forever, so as long as you don't declare a transaction and you change the run timeout of the .NET objects it'll just run.
    – mrdenny
    Dec 9, 2011 at 20:43

From the query plan, it appears that you have FK's pointing at the table you are deleting, but you don't have supporting indexes on those tables. For example, you VehicleLocationAPC table (hard to see the name) is undergoing an index scan to see if the VehicleLocation record you are deleting is referred to by any records in the VehicleLocationAPC table.

If you had a single index on the FK column, this would change to a seek. My instinct tell me that you do have an index on that column, but it is part of a compound index and is not the left most column.

  • The question states "It has 5 tables reference this table, all have FK indexes.". This is borne out by the plan as all the scans feed into merge joins without needing intermediate sort operations. So there must be indexes on all tables with leading key column of VehicleLocationId (or whatever) Dec 10, 2011 at 19:13
  • @Martin, what you say is indeed interesting, but I still believe if there was an index on the FK (a proper index) the scans would change to seeks. The index is being scanned because SQL knows the values are in there somewhere, but it has to scan the whole index in order to find them.
    – datagod
    Dec 12, 2011 at 4:33
  • Not sure what you mean by a "proper index" here? Dec 12, 2011 at 20:58
  • It is hard to describe, without actually seeing the existing indexes. If there was, for example, a single non-clustered index on the FK column, there would be no scan. A seek would be performed. I suspect that the FK column is part of a compound index, and not the leftmost part either.
    – datagod
    Dec 12, 2011 at 21:53
  • Your suspicion is definitely not correct (at least the second part of it). If it wasn't the leftmost part the index would not be correctly sorted to be of any use in a merge join. Dec 12, 2011 at 21:56

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.