Efficient way to delete records every 10 mins

Problem at hand

Need to delete some few thousand records every 10 minutes from a SQL Server database table.This is part of cleanup for older records.

Solutions under consideration

1. There's .Net Service running for some other functionality. Same service can be used wi开发者_如何转开发th a timer to execute SQL delete command on db.
2. SQL server job
3. Trigger

Key consideration for providing solution

1. Ours is a web product which gets deployed at different client locations. we want minimal operational overhead as resources doing deployment are very limited technical skill and we also want to make sure that there's less to none configuration requirement for our Product.
2. Performance is very important, as it on live transactional database.

---

This sounds like exactly the sort of work that a SQL Server job was intended to provide; database maintenance.

A scheduled job can execute a basic T-SQL statement that will delete the records you don't want any more, on whatever schedule you want it to run on. The job creation can be scripted to be part of your standard deployment scripts, which should negate the deployment costs.

Additionally, by utilizing an established part of SQL Server, you capitalize on the knowledge of other database administrators that will understand SQL jobs and be able to manage them.

---

I would not use a trigger...and stick with SQL Server DTS or SSIS. Obviously you will need some kind of identifier so I would use a timestamp column with an index...if that's not required just fire off a TRUNCATE once nightly.

---

The efficiency of the delete comes from indexes, has nothing to do how the timer is triggered. It is very important that the 'old' records be easily identifiable by a range scan. If the DELETE has to scan the whole table to find these 'old' records, it will block all other activity. Usually in such cases the table is clustered by the datetime value first, and unique primary keys are delegated to a non-clustered index, if needed.

Now how to pop the timer, you really have three alternatives:

• SQL Agent job
• Conversation Timers
• Application timer

SQL Agent job is the best option for 10 minute intervals. Only drawback is that it does not work on SQL Express deployments. If that is a concern, then conversation timers and activated procedures are a viable alternative.

Last option has the disadvantage that the application must be running for the timer to trigger deletion. If this is not a concern (ie. if the application is not running, it doesn't matter that the records are not deleted) then is OK. Note that ASP.Net applications are very bad host for such timers, because of the way IIS and ASP may choose to recycle and put to sleep app pools.