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.
[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.
[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.