Deleting large number of rows from SQL Server - in an efficient and non-locking manner

I am writing a procedure to delete all rows from a few tables over n days old.

A dead simple query is easy to write

DELETE FROM [myTable] 
WHERE [Created] < GETDATE()-30

One problem is there is no index on the date field - I could add one, but I was working around it by doing something like:

SELECT @var = MAX([ID]) FROM myTable WHERE Created < GETDATE()-30; 
DELETE FROM myTable WHERE ID < @var

Does that seem like an acceptable method?

The problem is the table is huge, and this query will be deleting likely hundreds of thousands of rows every run.

Running it on a (slightly slow) test server it is taking an hour or so, and killing the table from other processes trying to read/write to it.

I don't so much mind it taking a while to run (though quicker is better) - but I can not have it locking the table for an hour while it is running, as there are constant read/writes going on (mainly writes).

My DB knowledge is quite basic, as I'm a coder not a dba.

Can someone give me a decent method for performing this task - in the most efficient way possible.

7
задан marc_s 6 May 2011 в 04:55
поделиться