0

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?

  • 2
    Practically speaking, this seems like an odd thing to be concerned about. Disk Queue Length, especially on a SAN, isn't a very meaningful metric. If you're dead set on doing this, and you're on Enterprise Edition, you could use Resource Governor. – Erik Darling Jul 21 '18 at 15:45
  • 3
    If the batch processes have parallel plans, you could specify a MAXDOP 1 query hint to mitigate both disk and CPU at the expense of queries running longer. – Dan Guzman Jul 21 '18 at 16:14
  • 2
    Resource Governor directly controls IO in SQL Server 2014 and later. See docs.microsoft.com/en-us/sql/t-sql/statements/… – David Browne - Microsoft Jul 21 '18 at 17:52
  • @sp_BlitzErik - at periods of peak I/O we're seeing our website go down (presumably because SQL server is thrashing the disks) for jobs that just aren't time sensitive. CPU and Memory consumption remain normal during these periods and these jobs are quite simple - they're deleting old records so there's not much to optimize short of running them more slowly. I'll take a look at resource governor - I hadn't realized it was possible to use that for disk i/o. – Brad Jul 21 '18 at 20:35
  • 2
    That's quite a lot to presume. You may want to look at deleting in batches instead. – Erik Darling Jul 21 '18 at 20:40
1

"these jobs are quite simple - they're deleting old records so there's not much to optimize short of running them more slowly."

SELECT 0 -- rowcount is 1 
WHILE (@@ROWCOUNT > 0)
BEGIN
    WAITFOR DELAY '00:00:01'
    DELETE TOP (5000) FROM yourTable  ...
END

tune value 5000 and '00:00:01' depend your hardware

  • Batch deletes can be surprisingly tricky to optimize. – vonPryz Jul 24 '18 at 9:21
  • 1
    in this case optimize only : low IO – Igor Jul 24 '18 at 10:50

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.