We noticed that when a few of our Microsoft SQL server jobs run disk I/O goes from a background of 1-5 disk queue length to 100-700 disk queue length. The thing is the jobs only run for 1-2 minutes so its a huge spike in I/O.
We'd much rather have the job run for 10-15 or even 30 minutes and reduce the impact of I/O on the disks. Is there some way to even out the I/O and stretch out the job execution.
I was potentially thinking of trying to break the jobs up into batches with sleep statements but I'm not sure how well that would work. I know there is also resource governor but apparently that only works with CPU and Memory? CPU and Memory don't change when these jobs run so I don't think that would help.
How do other DBAs hand these sorts of jobs to make them less intensive?