As many are doing, I have to delete in batch in order to be as optimized as possible. So, I delete batches of 2000 records at a time. This is done every 15 minutes and I delete about 50,000 records on each occurence.

This has been working well for two years. We never had one single issue. Over the weekend, on our UAT environment, users on the Web site had dead lock issues because of the delete command executed from a robot. That happened for three days in a row. One occurence would have been a coincidence but not three days.

I have been reading somewhere one person mentioned that the condition to rely on to delete the records would be better to be on a non-clustered index. For now, I have the primary key and that is a clustered index. Can someone comment on that point as to know if it would be preferrable to create one additional field in that big Hit table just for the field to use when comes time to delete?