3

I want to run a background job in SQL Agent which will cleanup a lot of data and will run for multiple days.

Basically, 400 million records will be deleted from one single table. The delete runs in a loop, so always batches of around 100.000 records are deleted.

During the delete process, nothing else will be modified in this table.

Is it possible to lower the CPU priority for such a job, so that it does not slow down the system?

3
  • If you use Enterprise Edition, you could configure and enable Resource Governor for such tasks. Dec 9 '20 at 9:18
  • Welcome to DBA.SE. We'd require more information to be able to provide you with a suitable answer. Currently your question is very generic. Will you be performing UPDATE statements? Or DELETEs? What kind of job? Pure DML statements or SSIS packages? How big is the amount of data that will be cleaned up? Muliple tables? (Batch processing could be an option) Consider adding more information to your question by hitting the edit link. Good luck.
    – John K. N.
    Dec 9 '20 at 9:26
  • @Johnakahot2use Thanks, I have edited the question!
    – askolotl
    Dec 9 '20 at 10:04
7

As mentioned in the comments above, the resource governor is the built-in method for managing this, assuming you have an edition which enables this feature. Though unless your DELETE operations perform some complex logic to determine what to delete, changing CPU priority is unlikely to help at all because IO is will be the main bottleneck in such a case and it'll be barely bothering the CPU at all, so look at that MAX_IOPS_PER_VOLUME option when creating the resource pool.

If you don't have enterprise edition so do not have access to the resource governor, you could add sleep states into you loop using WAITFOR DELAY. You would want to work in smaller batches if using this more hacky manual approach, pick a batch size that will complete in a small amount of time. If you work for ~0.1 seconds and `WAITFOR DELAY '00:00:00.1' the task will be running at approximately half resource (very approximately, unless you get clever and have the task monitor itself and adjust batch size and/or sleep delay dynamically).

If what you are deleting represents almost all the data in the table, you might find it quicker to copy what you want to keep to another table, truncate the current table, and copy the kept data back in, though obviously this could get more complex if you have referential integrity to maintain between the remaining data and the rest of the database throughout the process, or the table is subject to any of the other restrictions for TRUNCATE TABLE. If you are deleting the whole table content, again assuming your the is suitable or can be temporarily made so, then just TRUNCATE TABLE.

2
  • 2
    I recently used WAITFOR DELAY in a similar manner. Wrapped the DELETE statement in a BEGIN/COMMIT block, and the WAITFOR DELAY just after it. All that was in a WHILE loop. Worked quite well.
    – RonJohn
    Dec 9 '20 at 18:24
  • 1
    okay, so a "thank you" gets deleted here? Well, so again, thank you! I am now also doing the WAITFOR approach. The truncate approach is also a great hint!
    – askolotl
    Dec 10 '20 at 9:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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