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?

Improve this question
8
  • 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, 2018 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, 2018 at 16:14
  • 2
    Resource Governor directly controls IO in SQL Server 2014 and later. See learn.microsoft.com/en-us/sql/t-sql/statements/…
    – David Browne - Microsoft
    Jul 21, 2018 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, 2018 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, 2018 at 20:40

1 Answer 1

Reset to default
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

Improve this answer
2
  • Batch deletes can be surprisingly tricky to optimize.
    – vonPryz
    Jul 24, 2018 at 9:21
  • 1
    in this case optimize only : low IO
    – Igor
    Jul 24, 2018 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.