TheSQLGuru (6/25/2013)

---

mayurkb (6/25/2013)

---

TheSQLGuru (6/25/2013)

---

I would have nonclustered indexes on the requisite fields required to find the aged records (some date field on all parent tables I would presume). Child tables would need to have indexes on FKs to parents. Then I would do a batching delete on each set of table(s), doing maybe a few thousand rows at a time with a rowlock hint. All within a transaction per table/table set. Iterate each set until rowcount < batch number. Put a waitfor delay in between each batch to give server some breathing room. Maybe watch for tlog size and perhaps fire off a tlog backup every so often based on that.

I have done this type of thing at many clients over the years.

Thanks Kevin for the reply.

1. Index-wise I'm good.

2. I do not have batching right now, I can add that. Do you recommend doing round trips from the .net app the database for each batch or just call a stored proc with very long time out and let it handle everything.

3. I see why you are proposing rowlock hint, but is it ok to have few thousand rowlocks over pagelocks or index locks?

A) I wouldn't do this from .NET (unless I am on SQLExpress - and then I would likely set it up as a SCHTASKS job). I would likely use a sproc to do all this. That isn't to say you can't do it with pure .NET making individual calls though. I am REALLY, REALLY good with the TSQL "hammer", so a "nail" like this is right in my wheelhouse. YMMV! 😎

B) I prefer to hint ROWLOCKs here to give concurrency a potential boost. They are just a hint though, and SQL Server is free to ignore the request anyway. 🙂