Inserting data to a table which is being cleaned up by SQL job at the same time

Question

Our app uses a table in a database and we have a Sql job set up to run overnight which executes a stored procedure which just deletes data older that from the table.

DELETE FROM
        MyTable
    WHERE
        CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());

Now depending on the data stored on that table (on the previous day), the job completes fast or very long. When It is long, the app throws timeout error inserting into the table. Obviously this is reported by the users using the app overnight.

I have following options :-

a) Change the time of the job so that minimum number of users get affected.

b) Delete certain number of rows at a time rather than delete all of them together.

delete top (10) MyTable where CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());
while @@rowcount > 0
    begin
    delete top (10) MyTable where CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());
    end

Could anyone think of a better option?

Answer 1

If CreatedOn is not the clustered index (and especially if it is not indexed at all), and you are inserting new data sequentially (e.g. new rows have a newer CreatedOn), you should consider making that the clustered index. Then you shouldn't have any contention when you are deleting old data and inserting new data, since it will be on a completely different set of pages. Unless for some reason your inserts or the delete are escalating locks. If that is the case then instead of one big transaction:

DELETE [massive amount of rows]
  WHERE CreatedOn < [somedate];

You should consider breaking that up into chunks, as you suggested; but, just doing that in a loop and not adding any other changes won't have that much of an effect, since that may still be operating as a single transaction (and your problem might be exasperated by transaction log writes, and perhaps poor autogrowth settings for the transaction log). My method is typically as follows (with no "outer" transaction at play) - commit each set in its own transaction, and checkpoint or backup the log in between transactions. This lets user queries get in between your transactions and also reduces the impact on the log. Note that I picked 1000 arbitrarily; that might not be the right number for your scenario, but I'm pretty sure 10 is not.

DECLARE @somedate DATE = DATEADD(DAY, -1, GETUTCDATE());

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple recovery: CHECKPOINT;
  -- otherwise: BACKUP LOG ...;

  BEGIN TRANSACTION;

  DELETE TOP (1000) dbo.MyTable
    WHERE CreatedOn < @SomeDate;
END

But again, this will help your process most if CreatedOn is clustered, or at least indexed (having no context I don't know if your overall workload would be better off with that as clustered, but I do know that this query will work much better if it is).

Answer 2

CreatedOn is not indexed at all and I have a clustured index on the "ID" column

Usually there is a correlation between CreatedOn and ID. With this info you can extend the WHERE clause to have a bounding ID:

  DELETE ...
  WHERE CreatedOn < @....
  AND ID < @max_possible_id_on_created_on;

Discovering the max id for a specific date is left as an exercise.

But with time seres in general (as you table seems to be) having the clustered index on the datetime column is usually the best, since time series are almost always queried on date ranges. The ID can become a non-clustered primary key, if required.