Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
When the query takes a long time to complete, do you know why? Do you have blocking locks or other waits going on that cause the job to run long? –  Mike Fal Feb 4 '13 at 21:18
    
As far as I have investigated, there are no blocking locks or waits. The table contains a [image] datatype column - I was thinking If because of that column and too many records the deletes are slow. –  Ashish Gupta Feb 4 '13 at 21:26
1  
How did you investigate that there are no "blocking lock or waits"? –  Aaron Bertrand Feb 4 '13 at 21:45
1  
"Could anyone think of a better option?" -- table partitioning, if you're using an edition that supports it. –  Jon Seigel Feb 4 '13 at 22:55
    
Why not truncate table MyTable ? That need no time... –  Ice Feb 6 '13 at 16:05

2 Answers 2

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).

share|improve this answer
    
CreatedOn is not indexed at all and I have a clustured index on the "ID" column. –  Ashish Gupta Feb 4 '13 at 22:17
    
Well there you go, your DELETE has to operate against the whole table, and this will block inserts. Chunking it out will help, but making that the clustered index will help a lot more. Why do you need a clustered index on the ID column? –  Aaron Bertrand Feb 4 '13 at 22:22
    
I understand that uniqueidentifier being random is not an ideal choice for clustered index. However, what about frequent selects by id? I was heading that way (changing clustered Id to non-clustered and then making CreatedOn clustered). But then thought of performing more rigorous tests before that. Your thoughts? –  Ashish Gupta Feb 6 '13 at 16:46
1  
Why do you think a clustered index is better than a non-clustered index for selects by individual id? As long as the index is covering the columns you need in the query (e.g. don't use SELECT *), and only those columns, then a seek on that non-clustered index - since it should be narrower - should actually be more efficient. You'll have to do testing, of course, but if you look around you'll see a mountain of evidence against using traditional NEWID() guids as a clustered index... –  Aaron Bertrand Feb 6 '13 at 16:50

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.

share|improve this answer
    
ID is a uniqueidentifier column - so can't use max on that. –  Ashish Gupta Feb 5 '13 at 4:26
4  
Then is ID clearly the wrong clustered key. –  Remus Rusanu Feb 5 '13 at 6:36
    
I understand that uniqueidentifier being random is not an ideal choice for clustered index. However, what about frequent selects by id? I was heading that way (changing clustered Id to non-clustered and then making CreatedOn clustered). But then thought of performing more rigorous tests before that. Your thoughts? –  Ashish Gupta Feb 6 '13 at 16:45
    
I would do exactly that: move ID into a non-clustered key, make CreateOn the clustered one. Frequent selects by ID will suffer (two lookups, one ont he NC index then one on the clustered one) but that is doubling two cheap operations. test. –  Remus Rusanu Feb 6 '13 at 17:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.