Is it possible to lower the CPU priority for a job? 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?
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.